Timestamp updaten

[Gast]

Hallo zusammen,

wie muss der SQL-Befehl lauten, um einen Timestamp upzudaten?

Code: Alles auswählen

UPDATE blabla SET blabla = '01.01.2003 18:00' WHERE id = 1

So bekomme ich immer den 01.01.1970, also nicht so ganz richtig. Was mache ich falsch?

Herzlichen Dank und viele Grüße

Sascha

[Pyramide]

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE

http://www.mysql.com/doc/en/DATETIME.html

[Gast]

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE

http://www.mysql.com/doc/en/DATETIME.html

Sehr schön. Und wie sieht jetzt die Syntax aus um einen Timestamp upzudaten?

[Elo]

@xGalaxius

So bekomme ich immer den 01.01.1970, also nicht so ganz richtig.

Doch das Datum ist korrekt.

Sehr schön. Und wie sieht jetzt die Syntax aus um einen Timestamp upzudaten?

Nix verstanden!
Na so wie Du sie oben schon eingefügt hast, allerdings solltest Du auch einen Timestamp zum updaten nehmen.
Jetzt klar.

Elo

[Gast]

Hallo Elo,

danke für deine Antwort.

Nix verstanden!

Richtig erkannt.

Na so wie Du sie oben schon eingefügt hast, allerdings solltest Du auch einen Timestamp zum updaten nehmen.

Code: Alles auswählen

UPDATE blabla SET blabla = time(2003-01-01 18:00:00) WHERE id = 1

Nix mehr weiß, und verzweifel.

Jetzt klar.

Du weißt doch sicherlich wie die korrekte Syntax lautet. Kannst du sie mir nicht niederschreiben?


Liebe Grüße

Sascha

[Elo]

OK, so wie ich es sehe hast Du den Wink mit dem Zaunpfahl verstanden.

Code: Alles auswählen

sql = 'UPDATE blabla SET blabla = ' . mktime( '18', '0', '0', '01', '01', '2003' ) . ' WHERE id = 1';

http://de3.php.net/manual/de/function.mktime.php

Gruß,
Elo

[Gast]

Hallo Elo,

du meinst ich soll ein PHP-Skript ausführen um die Datenbank upzudaten? Ich hätte das jetzt direkt mit SQL in PHP My-Admin gemacht. Aber da spuckt er mir noch Fehler aus ...

Na ja, ich schreib auch gerne ein PHP-Script, Hauptsache ich bekomme die Datumsfelder aktualisiert.

Tausend Dank!

Liebe Grüße

Sascha

[itst]

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE

http://www.mysql.com/doc/en/DATETIME.html

Sehr schön. Und wie sieht jetzt die Syntax aus um einen Timestamp upzudaten?

Nasenbär. Lies Dir mall Pyramides Post durch. Dort werden die Formate, in denen MySql Timestamps annimmt erklärt. Deine Query aus dem ersten Posting ist an sich ja OK, nur das Format des Timestamps stimmt nicht.

[Gast]

Nasenbär. Lies Dir mall Pyramides Post durch. Dort werden die Formate, in denen MySql Timestamps annimmt erklärt. Deine Query aus dem ersten Posting ist an sich ja OK, nur das Format des Timestamps stimmt nicht.

Das hab ich schon probiert ... schluckt er nicht.

Ich glaube ich rechne mir die zu aktualisierende Datümer per Hand aus, und schreibe den Wert dann in die Datenbank. *keine lust mehr hab*

Wann fängt das ganze an? Am 01.01.1970 00:00:00 Uhr?

[Elo]

http://www.mysql.com/doc/de/DATETIME.html

Tipp:
Stichwort: Sekunden.

Elo