convert-tz – Tarik Billa

MySQL query for current GMT time

December 21, 2023 by Tarik

Just use UTC (doesnt get affected with daylight savings time) SELECT UTC_TIMESTAMP(); Old Content for reference: this should work, but with SELECT CONVERT_TZ(NOW(),’PST’,’GMT’); i got also NULL as result. funny enough the example in the mysql docu also returns null SELECT CONVERT_TZ(‘2004-01-01 12:00:00′,’GMT’,’MET’); http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz seems you found a bug in mysql. (thanks to +Stephen Pritchard) … Read more

MySQL CONVERT_TZ()

June 1, 2023 by Tarik

If this yields null then the TZ tables have not been set up: SELECT CONVERT_TZ(now(),’US/Eastern’,’US/Central’); If you do not have the time zone tables set up you could update the hour offset in the user table and then do: select utc_timezone() – interval user_timezone_offset_in_hours hour from userinfo a where user_id = 999; You’d still need … Read more

How to Convert UTC Date To Local time Zone in MySql Select Query

March 19, 2023 by Tarik

SELECT CONVERT_TZ() will work for that.but its not working for me. Why, what error do you get? SELECT CONVERT_TZ(displaytime,’GMT’,’MET’); should work if your column type is timestamp, or date http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz Test how this works: SELECT CONVERT_TZ(a_ad_display.displaytime,’+00:00′,’+04:00′); Check your timezone-table SELECT * FROM mysql.time_zone; SELECT * FROM mysql.time_zone_name; http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html If those tables are empty, you have … Read more

Can MySQL convert a stored UTC time to local timezone?

January 31, 2023 by Tarik

Yup, there’s the convert_tz function.

convert_tz returns null

December 18, 2022 by Tarik

This will happen if you haven’t loaded the time zone table into mysql. mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql mysql is the name of the built-in database that holds MySQL-specific configuration data.