Should we store date in date or in datetime format if want to run the application on multiple timezone?

Question

My client wanted to run his website in multiple countries/timezone. We are using MySQL for our application.So while storing the date field in MySQL shall i use date or datetime for storing simple date field.

Let me give an example of the issue i am facing here. I have created an order today its 3rd Oct 2017 in India but its 2nd Oct 2017 in USA. I have already asking user to set their timezone. But can we fix above issues simply by using date field or we should go for datetime?

Thanks

Answer 1

UTC

Think of UTC as the One True Time^tm. All other zones and offsets are mere variations.

Store moments on the timeline in UTC. Store actual history in UTC.

TIMESTAMP WITH TIME ZONE

In standard SQL, this means using the data type TIMESTAMP WITH TIME ZONE. Databases typically adjust inputs into UTC. Some store the original time zone too, and some such as Postgres discord the zone after using it for the UTC adjustment.

Sounds like this is the type you need.

While I do not use MySQL, it looks like the equivalent of the standard type is TIMESTAMP.

In Java, you would retrieve this value as a Instant object. Then present to user as a ZonedDateTime by assigning a ZoneId.

In SQL, you can assign a time zone during retrieval. Generally best to let the app adjust from UTC to a zone in my opinion, but use your own judgement.

TIMESTAMP WITHOUT TIME ZONE

When discussing a general moment across time zones such as "All our factories worldwide stop at 12:30 for lunch break", use the standard type TIMESTAMP WITHOUT TIME ZONE.

Also use this type for scheduling future events far enough out in the future that time zones definitions may change. Politicians around the world have shown a penchant for messing with time zones. Often they do so with little notice, sometimes just several weeks. Dynamically apply a time zone when retrieving values.

While I do not use MySQL, it looks like the equivalent of the standard type is DATETIME.

DATE

The standard type DATE represents a date-only value, without time-of-day and without time zone.

As you have figured out, a date-only value is ambiguous. For any given moment, the date varies around the globe by zone.

Sounds like this is not a match for your needs. When you referred to a date you were implicitly intending a moment. Perhaps start-of-business in India time (Asia/Kolkata), or noon in Chicago time (America/Chicago), or first moment of the day in UTC, whatever your business rule is.

You need to investigate to determine the actual meaning of the information you are trying to represent as data.

All this has been discussed many times on Stack Overflow already. Please search thoroughly before posting, and to learn more now.

Beware that date-time work is tricky stuff, and often counter-intuitive. If you naïvely think date-time work should be quick and easy, you need to learn more. Slow down, study and experiment.

Example

If you are recording an order being taken in India at 8:00 AM on October 3, store that in UTC: 2017-10-03 02:30Z, as India is five and a half hours ahead of UTC. Tip: the Z is short for Zulu and means UTC.

If a user wants to view that same moment as another time zone, adjust. For America/Chicago that would be five hours behind UTC, so the previous date of Oct 2nd rather than 3rd: 2017-10-02 21:30. This is the same simultaneous moment as the other two. We have three ways of viewing the same single moment.