hanji hanji - 3 months ago 10
Linux Question

Convert UTC to MDT via MySQL

I'm having trouble changing my queries to use the my current MDT timezone. The server and MySQL are UTC

`date` = Tue Aug 9 23:32:52 UTC 2016
SELECT NOW() = 2016-08-09 23:33:28
cat /etc/localtime = UTC0


When I try to run CONVERT_TZ() to US/Mountain, my time is way off.

SELECT CONVERT_TZ( CURDATE( ) , 'UTC', 'US/Mountain' )


Returns 2016-08-08 18:00:00

Not sure what I'm doing wrong here.

Answer

Instead of curdate, you should use now like you did in your example:

SELECT CONVERT_TZ( NOW() , 'UTC', 'US/Mountain' )

curdate only returns the date, not the time. So converting 2016-08-09 to US/Mountain would offset it by 6 hours and return the previous day.

Comments