Scalable Scalable - 2 years ago 144
SQL Question

Unix Timestamp Conversion Back To Daylight Saving

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


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

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 .

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download