UTC
Think of UTC as the One True Timetm. 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.