CODEWITHSUNDEEP
phpmysqltimetimezonedst
Scalable
Scalable

Reputation: 1681

Unix Timestamp Conversion Back To Daylight Saving

Looks like I am having a case of Monday morning!!!

Setup

As you can my local machine is in Eastern time zone with Day light Saving is in effect. That can be seen from 'date' command below. 

date ; php -r 'echo mktime() .PHP_EOL ;'
Mon Apr 18 11:14:29 EDT 2016
1460992469

I then generated a unix timestamp using php. It is suppose to give your current time and convert that to Unix epoch at UTC 0:0:0 on Jan 1 1970.

My mysql Session is set to UTC, which I imagine 1460992469 represent as it is converted to UTC by mktime.

The Problem

The trouble is the conversion back to est does not recognizes Daylight Saving. Can anyone help to point the flaw in my logic. 

SELECT CONVERT_TZ(FROM_UNIXTIME(1460992469), @@session.time_zone ,'EST') as converted_to_est , FROM_UNIXTIME(1460992469) , @@session.time_zone;
+---------------------+---------------------------+---------------------+
| converted_to_est    | FROM_UNIXTIME(1460992469) | @@session.time_zone |
+---------------------+---------------------------+---------------------+
| 2016-04-18 10:14:29 | 2016-04-18 15:14:29       | UTC                 |
+---------------------+---------------------------+---------------------+

I tried using 'EDT' in CONVERT_TZ to no avail already .

Upvotes: 0

Views: 501

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241758

Don't use 'EST'. Use 'America/New_York' (assuming United States).

Upvotes: 1

Related Questions