1

I'm working on a PHP(CodeIgniter)/MySQL application that allows users select when their blog post will be published. How I have designed it to work so far is as below:

  1. User sets the timezone in his/her profile.
  2. User sets the publish date of the blog post. This date/time is assumed to be based on user's timezone.
  3. Based on user's timezone, the publish date is converted to UTC (00:00).
  4. The converted date is stored as DATETIME in mysql.
  5. Server frequently converts the stored DATETIME values to server timezone and publishes the content when their time has come.

I was wondering if there is a better way to handle this situation, considering that my DB server and web server are on different machines on the cloud and might change location as well.

Sahand Seifi
  • 225
  • 3
  • 11
  • 3
    store everything as utc, and convert as needed when it comes time to display it. – Marc B Sep 05 '14 at 21:11
  • @MarcB - That's what he said he was doing (at least for points 1-4). But the key is in the first sentence: "*will be* published" (future tense) – Matt Johnson-Pint Sep 05 '14 at 21:52
  • @matt: still holds. accept any date the user provides, but assume it's their local timezone, then convert to utc upon storage. unless that locale changes their timezone rules in between now and the publish date, that utc date would still be the same. – Marc B Sep 05 '14 at 21:55
  • @MarcB - Yes exactly. That's the case I described in my answer. And it does indeed happen just like that. – Matt Johnson-Pint Sep 05 '14 at 21:57

1 Answers1

2

Since you are talking about scheduling of future time, storing only UTC isn't necessarily the best approach.

Keep in mind that time zone rules can (and do) change. If you apply an update to your time zone data (in PHP, it's done with PECL's timezonedb package), then any data you already converted to UTC might be invalid.

The better solution is to store multiple values:

  • The original local date and time

  • The original time zone (ex. "America/New_York")

  • The UTC date and time, converted from the local values

When you want to see if it's time to run the task, you'd compare the current UTC date and time against the stored UTC value.

When you apply time zone updates, you throw out the previous converted UTC values for all future entries, and recalculate new ones based on the updated data.

If you don't do this, then you're losing the intent that the user originally provided. And if you don't keep your time zone data updated, then your system won't be aware of various changes happening around the world, such as the changes made in Egypt earlier this year, or the changes coming in October for Russia.

Last point - If implemented properly, the time zone of your server should not matter. Compare UTC to UTC.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • How would I know if a change to timezone rules is about to happen? – Sahand Seifi Sep 06 '14 at 18:10
  • 1
    The data originates at [IANA](http://www.iana.org/time-zones). You can subscribe to their Announcements mailing list there. But PHP is *not* self-updating. To get the new data, you either have to update PHP, or use the PECL package directly. See [this question and its answers](http://stackoverflow.com/questions/3564478/how-to-update-timezonedb-in-php-updating-timezones-info). – Matt Johnson-Pint Sep 06 '14 at 18:27
  • @MattJohnson It seems like Postgres recommends not storing timezone wiht the time here (https://www.postgresql.org/docs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES). Any thoughts? – geoboy May 23 '17 at 01:44
  • @MattJohnson And if have a moment to also critique the argument made in this blog (http://www.hydrogen18.com/blog/time-zones-and-databases-dont-mix.html), that would be super helpful in understanding the pros/cons! Thank you! – geoboy May 23 '17 at 01:44