[MySQL] Timestamp: formatted?

[Olaf van der Spek]

MySQL timestamp fields hebben in de C en PHP APIs een formaat: YYYY-MM-DD HH:MM:SS (ofzo). Dit vind ik erg onhandig, aangezien je er vaak nog iets mee wil doen.
Heel vaak kom ik in code van anderen dan ook code tegen om dit te parsen en te genereren naar standaard unix timestamps.
Overal from_unixtime() en unix_timestamp() bijzetten vond ik ook geen optie, dus heb ik er maar gewoon int van gemaakt. Hoe gaan jullie hier mee om? En hoe gaat bijvoorbeed PostgeSQL hiermee om?

[Verwijderd]

Mysql heeft prima date en time functions, zie http://dev.mysql.com/doc/...e-and-time-functions.html. Zelfs een UNIX_TIMESTAMP voor als je echt met unix timestamps wil werken. Deze functies gebruiken is echt een stuk makkelijker dan met ints gaan werken

[Olaf van der Spek]

Stel, ik print een timestamp in de output. Moet ik het formatteren dan aan MySQL overlaten?

[mr_derk]

http://dev.mysql.com/doc/...html#function_date-format

kan niet missen lijkt me

[Creepy]

Wat wil je er precies mee doen, en in welke taal? Bijv in java krijg je direct een instantie van de class Date terug, dus kan je er direct mee werken, geen conversie voor nodig.

[RobIII]

Olaf van der Spek schreef op vrijdag 29 januari 2010 @ 15:07:
Stel, ik print een timestamp in de output. Moet ik het formatteren dan aan MySQL overlaten?

Als een rule-of-thumb laat je formatteren aan je view/presentatielaag over. MySQL moet gewoon doen waar 'ie goed in is: data aanleveren/opslaan.

[Olaf van der Spek]

Creepy schreef op vrijdag 29 januari 2010 @ 16:59:
Wat wil je er precies mee doen, en in welke taal? Bijv in java krijg je direct een instantie van de class Date terug, dus kan je er direct mee werken, geen conversie voor nodig.

Bijvoorbeeld een timestamp vergelijken met een ander timestamp, in C of PHP. Of berekenen hoever in het verleden een timestamp is. Dat gaat wat lastig met formatted input.

RobIII schreef op vrijdag 29 januari 2010 @ 17:02:
[...]

Als een rule-of-thumb laat je formatteren aan je view/presentatielaag over. MySQL moet gewoon doen waar 'ie goed in is: data aanleveren/opslaan.

En dat is dus precies de kern van het probleem: MySQL doet al formatting...

[ Voor 85% gewijzigd door Olaf van der Spek op 29-01-2010 17:03 ]

[RobIII]

Olaf van der Spek schreef op vrijdag 29 januari 2010 @ 17:02:
En dat is dus precies de kern van het probleem: MySQL doet al formatting...

Says who?
Ergens druk jij de datum af (of het tooltje waarmee je je query bekijkt zoals PHPMyAdmin of de MySQL query browser of...). Die zal de formattering verzorgen; MySQL niet. MySQL levert een timestamp aan (feitelijk gewoon een bult enen en nullen), geen (al dan niet geformatteerde) string.

quote: http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is 'YYYY-MM-DD HH:MM:SS'.

Nu ben ik verder geen MySQL guru (spaar me ), maar ik mag hopen dat 'ie niet ook daadwerkelijk een timestamp zo opslaat (dus als een bult 'chars': 2009-01-29 17:22:15)

Als ik Creepy mag geloven (en dat doe ik doorgaans wel ) moet je dus gewoon met een timestamp kunnen rekenen; maar dan moet je 'm dus ook behandelen als een timestamp (in Java dus een Date instantie) en niet als een string.

Edit:

quote: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section.

Ga eens na waarom 'ie geen '2064-01-28' zou kunnen opslaan als 'ie het als 'ie het als YYYY-MM-etc... zou opslaan. Ik zie hier de bekende 1970-2038 range wat me doet vermoeden dat een timestamp onderhuids gewoon een 32bits (signed) integer is. En met integers rekenen ben je waarschijnlijk wel bekend mee lijkt me?

Helaas kan ik op de MySQL site zelf niets vinden over de (interne) opslagmethode van een timestamp maar het lijkt me veilig om bovenstaande aan te nemen
Gefund!

quote: http://dev.mysql.com/doc/...storage-requirements.html

TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)

[ Voor 82% gewijzigd door RobIII op 29-01-2010 17:37 ]

[Olaf van der Spek]

RobIII schreef op vrijdag 29 januari 2010 @ 17:14:
Says who?

Me. Intern slaat MySQL de waarde vast op als 32-bit int, maar standaard krijg je via de C en PHP APIs gewoon een formatted string terug. Tenzij je overal unix_timestamp() voor gebruikt.

[RobIII]

Olaf van der Spek schreef op vrijdag 29 januari 2010 @ 18:33:
[...]

Me. Intern slaat MySQL de waarde vast op als 32-bit int, maar standaard krijg je via de C en PHP APIs gewoon een formatted string terug. Tenzij je overal unix_timestamp() voor gebruikt.

Dat zou dan wel héél erg gaar zijn

[Janoz]

Mwah, de reden daarvoor is waarschijnlijk dat er geen fatsoenlijk (standaard) datum type is in die talen.

Om ook nog even terug te komen op de originele vraag: from_unixtime() en unix_timestamp() bijzetten vind ik ook wel een optie.

[RobIII]

Janoz schreef op vrijdag 29 januari 2010 @ 19:01:
Mwah, de reden daarvoor is waarschijnlijk dat er geen fatsoenlijk (standaard) datum type is in die talen.

Ik zou het nog begrijpen voor een "datetime" type dat niet bestaat uit een integer; maar als het toch al intern een 32bits integer is snap ik niet waarom mysql nog met z'n takken eerst de zooi gaat zitten formatten alvorens de zaken aan de client te geven. Laat dat dan aan de client over... Worst case heb je dus een int32 -> string naar client "over de wire" -> client ontvangt string en gaat er weer lekker een int32 van zitten maken... Wat een waste of resources

Goed, het is dus documented behaviour en ik interpreteerde de documentatie dus anders en in mijn onschuldige naïviteit nam ik aan dat de formatting ergens door PHP ofzo voor rekening werd genomen. Valt weer tegen. En dan zal er inderdaad niet veel meer op zitten dan wat er al in de topicstart genoemd wordt; of overal from_unixtime() en unix_timestamp() bijzetten (en daar heeft TS geen zin in) of converteren naar een int. Of er moet nog een andere 'hidden' optie zijn waar ik geen weet van heb natuurlijk

[ Voor 7% gewijzigd door RobIII op 29-01-2010 19:11 ]

[Verwijderd]

Ik heb hier ook weleens mee gestoeid, maar dat is wel even geleden. Ik heb toen vies gedaan door de interne str_to_datetime van MySQL zelf te gebruiken. Volgens mij kwam het wel neer op een patch van de MySQL client library, maar ik weet dat helaas niet meer zeker.

In PHP gebruik ik gewoon strtotime().

[ Voor 7% gewijzigd door Verwijderd op 29-01-2010 19:22 ]

[Olaf van der Spek]

Verwijderd schreef op vrijdag 29 januari 2010 @ 19:21:
Volgens mij kwam het wel neer op een patch van de MySQL client library, maar ik weet dat helaas niet meer zeker.

Dat is voor een public project geen optie en voor een private project eigenlijk ook niet. Ook is er nog een timezone issue als MySQL niet met timezone = gmt draait.
Het probleem met int is vooral dat het in phpMyAdmin niet leesbaar is.

[T-MOB]

Olaf van der Spek schreef op vrijdag 29 januari 2010 @ 19:49:
Het probleem met int is vooral dat het in phpMyAdmin niet leesbaar is.

Het probleem met een int lijkt me vooral dat je geen gebruik kunt maken van de datumfuncties van MySQL. De aankomende 10 verjaardagen uit een set geboortedatums is geen mooie query zonder die functies. Je kunt een en ander natuurlijk afvangen met FROM_UNIXTIME(), maar mooi is anders.
Zelf gebruik ik in PHP altijd een Date object om met datums te werken. Een datum die uit de database wordt omgezet in zo'n object en als het de database ingaat wordt het wat noodzakelijk is om een DATETIME in de database te krijgen. Ik heb eerlijk gezegd nog nooit bedacht dat je daar wat van kan vinden.

[Olaf van der Spek]

T-MOB schreef op zaterdag 30 januari 2010 @ 02:15:
Het probleem met een int lijkt me vooral dat je geen gebruik kunt maken van de datumfuncties van MySQL. De aankomende 10 verjaardagen uit een set geboortedatums is geen mooie query zonder die functies.

De volgende 10 verjaardagen kun je toch gewoon in C of PHP berekenen?

Je kunt een en ander natuurlijk afvangen met FROM_UNIXTIME(), maar mooi is anders.
Zelf gebruik ik in PHP altijd een Date object om met datums te werken. Een datum die uit de database wordt omgezet in zo'n object en als het de database ingaat wordt het wat noodzakelijk is om een DATETIME in de database te krijgen. Ik heb eerlijk gezegd nog nooit bedacht dat je daar wat van kan vinden.

De conversieproblemen worden dan opgevangen door de Date class, maar intern heb je nog steeds hetzelfde probleem.

[Dido]

Olaf van der Spek schreef op zaterdag 30 januari 2010 @ 10:04:
De volgende 10 verjaardagen kun je toch gewoon in C of PHP berekenen?

Alles kan, maar een dergelijke simpele restrictie op je resultset is nou net iets wat ik door de database zou laten doen. Jij haalt liever eerst, zeg, een miljoen redords uit je database, om dan vervolgens in C tien records te selecteren?

[Olaf van der Spek]

Dido schreef op zaterdag 30 januari 2010 @ 10:35:
[...]

Alles kan, maar een dergelijke simpele restrictie op je resultset is nou net iets wat ik door de database zou laten doen. Jij haalt liever eerst, zeg, een miljoen redords uit je database, om dan vervolgens in C tien records te selecteren?

Oh, zo... Voor die ene keer wil ik best een from_unixtime gebruiken.

[RobIII]

Olaf van der Spek schreef op zaterdag 30 januari 2010 @ 11:09:
[...]

Oh, zo... Voor die ene keer wil ik best een from_unixtime gebruiken.

Wat zijn eigenlijk je beweegredenen om géén Datetime ofzo te gebruiken?

[Olaf van der Spek]

RobIII schreef op zaterdag 30 januari 2010 @ 11:51:
Wat zijn eigenlijk je beweegredenen om géén Datetime ofzo te gebruiken?

Dan moet ik elke keer in C en PHP strtotime gebruiken.

[T-MOB]

Er is intern helemaal geen probleem, het converteren zie ik gewoon als hoe het werkt. Ik heb in zowel in mijn database als in mijn code een heldere representatie van een datum. Hoe het tussen die twee wordt uitgewisseld interesseert me geen biet (al veranderd het tusendoor in macaroni and cheese), zolang het maar snel (genoeg) is.
Als uitwisselformaat is de 'YYYY-MM-DD HH:MM:SS' in SQL queries wel behoorlijk standaard - in tegenstelling tot functies om met timestamps te werken. Het postgres-alternatief voor FROM_UNIXTIME(1234) is bijvoorbeeld:

SQL:

SELECT TIMESTAMP 'epoch' + 1234 * INTERVAL '1 second'.

[Cartman!]

Opslaan als INT heb ik ook jaren gedaan maar je mist gewoon direct de datum- en tijdfuncties die enorm handig kunnen zijn. Het probleem met UNIX_TIMESTAMP() mis ik dan ook een beetje, of je dat nu in je query doet of los in je code maakt dan weinig meer uit. Als je DATETIME gebruikt heb je geen beperkingen meer van unix timestamps ook, want hoe wilde jij bijv. een geboortedatum opslaan als INT? Of ga je er dan een min getal van maken?

[Olaf van der Spek]

Cartman! schreef op zondag 31 januari 2010 @ 11:00:
Opslaan als INT heb ik ook jaren gedaan maar je mist gewoon direct de datum- en tijdfuncties die enorm handig kunnen zijn. Het probleem met UNIX_TIMESTAMP() mis ik dan ook een beetje, of je dat nu in je query doet of los in je code maakt dan weinig meer uit.

Als je int gebruikt, hoef je het dus in geen van beide te doen.

Als je DATETIME gebruikt heb je geen beperkingen meer van unix timestamps ook, want hoe wilde jij bijv. een geboortedatum opslaan als INT? Of ga je er dan een min getal van maken?

Dit gaat om timestamps, niet om dates.

[Cartman!]

Je vroeg in je TS hoe wij daar mee omgaan, simpel: ik gebruik alleen maar DATETIME Ik vroeg me alleen af waarom je daar perse niet voor wilt gaan.

[Olaf van der Spek]

Cartman! schreef op maandag 01 februari 2010 @ 10:33:
Je vroeg in je TS hoe wij daar mee omgaan, simpel: ik gebruik alleen maar DATETIME Ik vroeg me alleen af waarom je daar perse niet voor wilt gaan.

Dat waarom heb ik al een paar keer gepost. Ok, jij gebruikt datetime. En verder? Wat doe je als je zo'n datetime in PHP met een ander timestamp wil vergelijken? Of wil weergeven (in een ander formaat)?

[ Voor 4% gewijzigd door Olaf van der Spek op 01-02-2010 15:41 ]

[cariolive23]

Olaf van der Spek schreef op vrijdag 29 januari 2010 @ 14:57:
MySQL timestamp fields hebben in de C en PHP APIs een formaat: YYYY-MM-DD HH:MM:SS (ofzo). Dit vind ik erg onhandig, aangezien je er vaak nog iets mee wil doen.
Heel vaak kom ik in code van anderen dan ook code tegen om dit te parsen en te genereren naar standaard unix timestamps.
Overal from_unixtime() en unix_timestamp() bijzetten vond ik ook geen optie, dus heb ik er maar gewoon int van gemaakt. Hoe gaan jullie hier mee om? En hoe gaat bijvoorbeed PostgeSQL hiermee om?

Een veld van het type TIMESTAMP is in PostgreSQL heel wat anders dan in MySQL. In PostgreSQL krijg je dan het ISO-formaat TIMESTAMP of zelfs TIMESTAMP WITH TIME ZONE en lopen van 4713 BC tot 294276 AD met een nauwkeurigheid van 1 microseconde.

Dit heeft dus niets met een Unix timestamp te maken, wat in MySQL wordt gebruikt, een TIMESTAMP in PostgreSQL lijkt nog het meeste op een DATETIME in MySQL.

Het ophalen van een Unix-timestamp op basis van een TIMESTAMP, kun je doen met EXTRACT en EPOCH:

SQL:

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');

http://www.postgresql.org...c/functions-datetime.html

[Motrax]

Ik zie veel mensen in het formaat: 'YYYY-MM-DD HH:MM:SS' schrijven om een timestamp formaat aan te duiden.

Zover ik weet is het 'YYYY-MM-DD HH:MI:SS', waar dus de laatste twee MM's vervangen is door MI. Anders krijg je dus de maand ook nog eens op de minuten plek. Vrij vervelend...

[Raynman]

Motrax schreef op maandag 01 februari 2010 @ 16:07:
Ik zie veel mensen in het formaat: 'YYYY-MM-DD HH:MM:SS' schrijven om een timestamp formaat aan te duiden.

Zover ik weet is het 'YYYY-MM-DD HH:MI:SS', waar dus de laatste twee MM's vervangen is door MI. Anders krijg je dus de maand ook nog eens op de minuten plek. Vrij vervelend...

Context? Die notatie wordt gebruikt (hier en in (MySQL-)documentatie) om iets aan menselijke lezers duidelijk te maken. Met bijvoorbeeld PHP's date() zou je dan ook vier keer het jaar, twee keer de maand enz. krijgen en MySQL's date_format ziet het gewoon als string (zonder speciale betekenis). Oké, Postgre gebruikt het wel met to_char, maar dat is hier niet echt relevant.

[ Voor 7% gewijzigd door Raynman op 01-02-2010 19:40 ]

[Cartman!]

Olaf van der Spek schreef op maandag 01 februari 2010 @ 15:40:
[...]

Dat waarom heb ik al een paar keer gepost. Ok, jij gebruikt datetime. En verder? Wat doe je als je zo'n datetime in PHP met een ander timestamp wil vergelijken? Of wil weergeven (in een ander formaat)?

Als het enkel representatie is dan heb ik een view helper die de datum explode en in de goede volgorde terugzet of ik gebruik de DATE_FORMAT functie in MySQL om dit direct te doen.

Als ik een vergelijking wil doen dan zet ik dus UNIX_TIMESTAMP() in m'n query neer zodat ik de datum als unix timestamp terugkrijg, maar alleen als ik zeker weet dat dit binnen de grenzen blijft van wat unix timestamp aankan.