I use mysql for my application and the application is business hour driven.
Now the time in my system is
8:53 PM. (EST)
select now(); -> returns 2015-10-21 20:52:48 select utc_timestamp(); -> returns 2015-10-22 00:53:50 I would like to convert the time to user timezone specific time.
So use the below query to convert to user specific timezone.
select CONVERT_TZ(utc_timestamp(),'+00:00','-05:00'); The above query returns
2015-10-21 19:55:49
It considers daylight saving time. But, I would like this to return the current time in my system > 2015-10-21 20:55:49.
Is there anyway to instruct the convert tz query not to consider daylight saving?
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); The above query returns
-04:00:00
I expect this to return -05:00 as I am in EST.
Thanks
1 Answer
Actually, you have it backwards. When you specify -05:00, that is not considering daylight saving time. It can't, as you only provided an offset.
Later, you said you expected it to return -05:00, but if you are in US Eastern time, then currently (until Nov 1, 2015) daylight saving time is in effect. You are in EDT, which is -04:00.
So SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); is giving the correct result. If you want CONVERT_TZ to give the correct result, then you would do this:
select CONVERT_TZ(utc_timestamp(),'+00:00','SYSTEM'); Passing SYSTEM uses the system's local time zone, which is what you asked for. However, it's usually a better idea to use a named time zone, such as America/New_York. To use these, ensure your MySQL installation is set up correctly for time zone support.
