Handling timezones and dates

Question

I am having an issue getting my head around storing and retrieving information based on a date (no time) and dealing with multiple timezones.

By default my application uses UTC, so create/update times are all in UTC and PHP functions such as date etc all use UTC.

My problem is this:

I allow users to create a news item, the news item has a date (no time, just date).

The application uses UTC, my user is in Sydney, so what I do is when the form loads I pre-populate the date field based of jS M Y for the Australia/Sydney timezone, this way it shows as today for him. All good so far.

When I save the record into the database (into a DATE field) I use the date they entered so if they said 3rd March 2016, then it goes in as 2016-03-03.

When it gets pulled out again it displays fine for them, but if I do a report saying "How many news items published today?", in my report (cron job) it might be looking for a different date, because if this person entered it in early morning, then 3rd March 2016 for Sydney is still going to be 2nd March 2016 in UTC so there will be 0 for today.

The solution it appears would be to convert the date back to UTC when you store it, but how will that work?.. Since it's just a date with no time.

2nd March 2016 00:00:00 UTC would be 2nd March 2016 10:00:00 Sydney

So.. I am not sure how to solve this without them having to also enter a time, which seems redundant.

score 0 · Answer 1 · answered Mar 03 '16 at 03:29

Always remember that savings time rules vary between countries, such as the USA and EU. Not just when the clocks change but timings of changes across time zones.

In the past I forced a local hour of 12:00 (noon). With a time value the standard conversion routines be used. Choosing noon kept everything well away from any midnight and savings time issues as an hour slip either way would still be in the same day.

score 0 · Answer 2 · answered Mar 03 '16 at 05:10

0

The simplest way is to ALWAYS work with full UTC timestamps under the hood.

Convert incoming dates to these straight away, and only convert back to readable, localized formats when rendering.

This way you have a single, solid, point of reference. Everything in your database has logged exactly when it was created (relative to the 1st of January 1970 - UTC).

Then it becomes trivial to order articles in order of creation (regardless of where their authors posted from). It's also much easier to count how many articles were added in the last 24 hours, or on a specific day (for example; seven days ago) -- notice how these examples don't require a date, just ranges of seconds.

This also makes it easy to show "user A" when "user B" added an article, relative to user A's timezone -- you don't need to look up user B's timezone to do the conversion.

Also, give your user the option to add a time to their articles, but don't make it mandatory. Have a sensible default (ideally a dynamic "now"). This is more natural, and should smooth out the midnight and DST anomalies highlighted by Gilbert, when building reports.

answered Mar 03 '16 at 05:10

CoderCreative

225
1
2
9

Please, be careful with absolutes. Any time you find yourself saying *always*, remember that there are *always* exceptions to the rule. In this case, the advice "always use UTC" has been propagated around the interwebs so much that people follow it blindly and often run into traps. There are indeed contexts where times other than UTC are more appropriate (or required even). A good example is [*scheduling*](http://stackoverflow.com/a/19627330/634824). Your other points are valid - but please be careful with this one. Thanks. – Matt Johnson-Pint Mar 03 '16 at 05:19
Thanks Matt. In every case I've come across where multiple timezones are a factor, UTC & timestamps have been an excellent approach that have saved a lot of pain. But, I totally agree with what you're saying. – CoderCreative Mar 03 '16 at 05:27
@CoderCreative When the user selects 3rd March 2016 on the calendar how would you propose I convert to a UTC timestamp? What if they selected a data from a month ago, say 14th February 2016. It becomes trivial when you include the time as well... – Wizzard Mar 03 '16 at 22:58
Create a DateTime object using the input and the user's timezone to get your UTC timestamp. Regarding hours and minutes though, I think the answer is at the bottom of your question; _"I am not sure how to solve this without them having to also enter a time, which seems redundant"_ -- if requiring the user to enter a time (or accept a default) solves the problem, then it isn't really redundant. Inevitably, a time will be implied regardless, it'll just be manifested as a bi-product your search algorithms, and wont be as transparent as absolute data in your database. – CoderCreative Mar 04 '16 at 04:00

score 0 · Answer 3 · answered Mar 03 '16 at 05:42

It actually sounds like you are doing things correctly already.

The concepts of "today" and a "date" are calendering concepts, covered by the domain of civil time. You can fly around the world with a printed calendar, and you don't need to adjust it for UTC or time zones. A date itself can't be "in UTC".

However, you can say that in the domain of absolute time, that a date in time zone A does not cover the same instantaneous moments of time that are covered by time zone B. At any given time, there could be two different "todays" in effect on the Earth.

By the perspective of your Sydney user, March 3rd was the publishing date. You might have another user in Hawaii publishing simultaneously at March 2nd.

Inherently then, your posting date is the civil date that the item was posted. When you query for all items posted "today", that is based on the civil date of wherever you're at when you're asking.

If this isn't the desired behavior, then change the question. Instead of asking "what was posted today", ask "what was posted in the last 24 hours?" For that, you would indeed need to store the full UTC-based date and time (not just date) of the post. Then you would query it by UTC time from now less 24 hours.

A thought exercise that will help: What should you do if a user posts at 23:59 UTC, and you run a query one minute later? Should it include the result - or not? Does your answer change if it's 23:59 local time and you run the query one minute later in the same time zone? Thinking this through will help you decide what you should actually be storing and querying.

I'll also say that in most cases you should indeed store a UTC based timestamp, but you may want to also store the local date (or datetime) in a separate field. This gives you the ability to do both types of queries.

Handling timezones and dates

3 Answers3