CODEWITHSUNDEEP
mysqltimezone
user1578872
user1578872

Reputation: 9048

MySQL UTC to local time zone

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

Upvotes: 1

Views: 3654

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241603

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.

Upvotes: 3

Related Questions