[PHP/SQL] Van UTC datum naar timestamp, naar EST datum/tijd

[Verwijderd]

Via een scriptje heb ik een tijdje tweets gedownload via de search api. De json bevat geeft onder andere de datum en tijd waarop de tweet is aangemaakt. Deze tijd is omgerekend naar UTC:

code:

created_at: Fri Feb 18 21:08:38 +0000 2011

Nu heb ik deze data opgeslagen als timestamp mbv PHP's strtotime. Ik denk er pas nu aan dat mijn server ingesteld staat op Europe/Paris (gevonden via date_default_timezone_get()) en heeft opgeslagen:

PHP:

strtotime('Fri Feb 18 21:08:38 +0000 2011') // geeft 1298063318

De eerste vraag waar ik nu niet uitkom is: is die timestamp (1298063318) nu in UTC of Europe/Paris (UTC +1 of +2, ligt aan DST)? Of dwingt die +0000 strtotime om een timestamp te maken in UTC, ongeacht de instelling van mijn server?

Vraag 2: ik heb tweets verzameld van 1 januari tot 1 mei 2011. Betekent dat nu dat sommige timestamps berekend zijn met DST (dus: +2) en anderen zonder (dus: +1)? En zo ja, hoe kan ik dat "op 1 lijn trekken"? Hier wat informatie over deze tijdzone en DST.

Vraag 3: waar het uiteindelijk om gaat is dat ik originele created_at datum van de twitter api, wil omrekenen naar new york lokale tijd. NY ligt in timezone EST, op -5 uur van UTC. Als een tweet dus geleverd is met als created_at datum

code:

created_at: Fri Feb 18 21:08:38 +0000 2011

dat wil ik die omrekenen naar hoe laat het op dat moment in NY was (5 uur vroeger dus):

code:

NY Time: Fri Feb 18 16:08:38 2011

Kortom, ik heb nu de volgende database tabel (met nyse_date en nyse_time nu nog leeg):

code:

id  nyse_date   nyse_time  created_at
-------------------------------------------
1   2011-02-18  16:08:38   1298063318

Je ziet weer staan: de timestamp die ik ook heb ingevoerd. nysq_datum en nyse_time spreken voor zich. Met bovenstaande in acht nemende, is er dan een SQL query mogelijk waarmee ik de datum en tijd kan bepalen die het op het moment van die timestamp (die dus misschien niet in UTC maar door mij als Europe/Paris is opgeslagen)?

Ik denk dat je in principe als volgt de dag en tijd uit de timestamp kunt halen.

SQL:

UPDATE tweets SET nyse_date = DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y-%m-%d'), nyse_time = DATE_FORMAT(FROM_UNIXTIME(created_at), '%H-%i-%s')

Maar nogmaals - die timestamp is (i) misschien niet eens in UTC en (ii) zeker niet in EST tijd. Met andere woorden; wie kan mij vertellen hoe ik die timestamps juist krijg naar NY datum en tijd? Als ik er vanuit kan gaan dat de created_at timestamps in UTC zijn, kan ik daar natuurlijk (5 urr x 60 minuten x 60 seconden) 18.000 seconden vanaf halen om op de utc_timestamp te komen (toch?).

Maar nog een probleem. Stel dat de van twitter gekregen datum "Fri Feb 18 02:08:38 +0000 2011" is. Dan moet er voor die tweet in nyse_date 2011-02-17 (en niet 18!) en in nyse_time 21:08:38 worden opgeslagen. Met andere woorden; welke query houdt er rekening mee dat -5 uur dan ook kan betekenen dat de nyse_date de dag ervoor kan zijn?

P.S. Ik maak het vast moeilijker dan het is, maar zit er nu al zo lang over na te denken (en te zoeken op internet), dat ik volledig door de war ben

[ Voor 12% gewijzigd door Verwijderd op 07-04-2012 22:29 ]

[Creepy]

Zie http://php.net/manual/en/function.time.php

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

Oftewel: als je een timestamp hebt, dan is deze altijd de tjid in seconden sinds 1-1-1970 00:00:00 GMT (!). Ik denk dat dat aardig scheelt in je probleem

[Verwijderd]

@Creepy, thanks. Maar zie ook http://php.net/manual/en/function.strtotime.php

int strtotime ( string $time [, int $now = time() ] )

The function expects to be given a string containing an English date format and will try to parse that format into a Unix timestamp (the number of seconds since January 1 1970 00:00:00 UTC), relative to the timestamp given in now, or the current time if now is not supplied. Each parameter of this function uses the default time zone unless a time zone is specified in that parameter.

Ik raak dus in de war van die vetgedrukte zin, want snap niet wat hier met "each parameter of this function" bedoeld wordt: de +0000 in de datum? Of heeft dat niets te maken met het aantal seconden sinds epoch?

Voorts, als de timestamps idd in UTC zijn, klopt het dan dat ik gewoon 18.000 van elke timestamp kan afhalen (bv. in nieuwe kolom created_at_new) en dan met de volgende query nyse_date en nyse_time kan vullen?

SQL:

UPDATE tweets 
SET nyse_date = DATE_FORMAT(FROM_UNIXTIME(created_at_new), '%Y-%m-%d'), 
       nyse_time = DATE_FORMAT(FROM_UNIXTIME(created_at_new), '%H-%i-%s')

[MikeN]

Met each parameter bedoelen ze voor zover ik weet onder andere de +0000 in de datum. Timestamps zijn inherent UTC (dat is de definitie), en als het goed is houdt strtotime() rekening met de tijdzone bij het omzetten. Voor 100% zekerheid kun je het gewoon testen met een testwaarde natuurlijk.

Ik snap alleen niet helemaal wat je nu vervolgens in de database wil doen met omrekeningen. Waarom reken je zaken niet gewon om wanneer je ze displayed aan de client? Timezoneconversie is nu typisch iets wat je normaalgesproken zo laat mogelijk doet in je applicatie, en overal in je applicatie werk je met UTC/timestamps.

[Verwijderd]

@MikeN - de reden is; deze database is geen onderdeel van een applicatie maar van een onderzoekje dat deel uitmaakt van mijn scriptie. Daarin doe ik onderzoek naar aandelen uit new york. Ik moet dus van alle tweets weten wanneer ze, naar ney york (UTC -5) tijd getikt zijn. Uiteindelijk wil ik deze tabel exporteren naar excel, om er wat grafiekjes uit te halen. Het is makkelijk als ik in de excel sheet niks meer hoef te converteren. Dat het aanleggen van kolommen met geconverteerde timestamps normaal gesproken geen best practice is, ben ik met je eens Aangenomen dat de timestamps in UTC zijn (zoals je aangeeft controleer ik nog even voor de zekerheid), dan kan ik ze toch zo omzetten?

SQL:

UPDATE tweets  
SET nyse_date = DATE_FORMAT(FROM_UNIXTIME(created_at - 18000), '%Y-%m-%d'),  
    nyse_time = DATE_FORMAT(FROM_UNIXTIME(created_at - 18000), '%H-%i-%s')

[MueR]

Kijk eens naar DateTime en DateTimeZone

[Verwijderd]

@MUeR. Thanks, maar (i) dat is PHP en ik zoek een oplossing in SQL en (ii) ik zie niet hoe mij dat helpt? Ik stel in mijn laatste post een hele concrete vraag en snap niet hoe ik die kan beantwoorden door de links die je geeft. DateTimeZone bijvoorbeeld, is een class met allerhande timezone info. Maar die heb ik imho niet nodig, want ik weet al dat ik (waarschijnlijk) timestamps heb van timezone UTC, en ik wil die omrekenen naar EST (= UTC - 5 uur). Dat is imho alle timezone info die ik nodig heb dus wat draagt die class dan bij?

[edit]

Intussen zie ik dat de door mij voorgestelde query niet gaat werken. De timestamp 1298063318 wordt door mijn MySQL server automatisch vertaald naar een UTC/GMT+1 datestring (mijn server is ingesteld op timezone Europe/Paris, wat GMT+1 is, evt. +2 wanneer DST in effect is):

code:

MYSQL> DATE_FORMAT(FROM_UNIXTIME(1298063318), '%Y/%m/%d %H:%i')
MYSQL> 2011/02/18 22:08

Ik weet dus al niet hoe ik in MySQL de timestamp 1298063318 naar een UTC datumstring kan omzetten, zoals onder andere die van IBBoard doet.

Daarnaast vertaalt de IBBoard website deze timestamp naar de volgende EST (GMT-5) datestring (Zie zelfde link):

code:

18/2/2011, 15:08

Nogmaals, omdat EST gelijk is aan UTC/GMT - 5 uur maar mijn server zit op UTC+1 (=21,600 seconden totaal verschil), dacht ik dat de volgende query op mijn server ook 2011/02/18, 15:08 zou opleveren:

code:

MYSQL> SELECT DATE_FORMAT(FROM_UNIXTIME(1298063318 -21600), '%Y/%m/%d %H:%i')
MYSQL> 2011/02/18 16:08

Maar wat blijkt: mijn server converteert naar 1 uur later (16:08 ipv 15:08). Mijn vraag is dus: hoe los ik dit op? Met welke query kan ik de timestamp 1298063318 converteren naar 2011/02/18, 15:08? Mijn MySQL is ingesteld op Europe/Paris timezone (UTC+1 of +2, afhankelijk dan DST). Desnoods kan ik de timezone (tijdelijk) aanpassen. Wie weet een oplossing?

[ Voor 61% gewijzigd door Verwijderd op 08-04-2012 16:35 ]

[Cartman!]

Zorg dat alle tijden in je DB op UTC staan en ga in PHP pas omrekenen van een naar de juiste timezone, wat je nu probeert te doen klinkt als een lapmiddel waar je alleen maar meer bugs van zult krijgen.