Timezones in WordPress and MySql

  • Resolved zagreus

    (@zagreus)


    7 years, 9 months ago

    I’ve got a custom table and am storing everything in UTC time for various reasons.

    I want to use the mysql CONVERT_TZ function to convert to the blog timezone. Using get_option(‘gmt_offset’) I can get the blog time offset, however this is in a different format to MySQL.

    get_option(‘gmt_offset’) returns values like ‘0’, ‘-0.5’, etc.
    MySql expects values like ‘+00:00’, ‘-00:30’

    Is there an easy way to convert from the one style to the other that anyone has used? Or is there some other way around that I should be looking at this?

Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator bcworkz

    (@bcworkz)

    The PHP function strtotime() will convert almost any textual time/date input into a UNIX timestamp. Providing an argument like ‘-5.0 hours’ will result in a 5 hour adjustment to the provided time, so basically doing the SQL timezone adjustment for you itself. If you still prefer to use SQL for this, you can subtract out the provided time from the resulting timestamp and use the PHP date() function to format the difference in any time format you want.

    I agree storing dates in UTC is a good idea. I also advocate storing UNIX timestamps instead of date/time strings. Queries are much easier to construct this way. Certainly, date/time strings can be made to work if timestamps are not an option. If everything is stored as UTC, I don’t see why you need to use SQL to convert to local time. I would be doing all operations based on UTC, local time is not a factor until it comes time to present the time to a user who is expecting local time references. For that the offset can be applied and the output formatted with date().

    Thread Starter zagreus

    (@zagreus)

    Let me give it a try. Thank you.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Timezones in WordPress and MySql’ is closed to new replies.