4

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

3 Answers3

2

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
0

Difference between DATE, DATETIME, and TIMESTAMP MySQL

  1. The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
  2. The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
  3. The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Aman Kumar
  • 4,533
  • 3
  • 18
  • 40
  • 5
    you forgot the relevant part: `MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) ` – kscherrer Oct 03 '17 at 06:46
0

You could use Timestamp. Which automatically gets converted to UTC. Then on the server that is running compute the time difference. But that requires you to put that logic into your code (or select statement) everywhere that you access dates. See this post

Otherwise, using date or datetime might be the same in that neither stores timezone. Datetime seems to just be more precise in that it records down to the second rather than the day.

Check out the documentation

Just go to the link and hit Ctrl + F, and search "time zone," and you can see exactly how timezones are handled.

It appears as though the timezone won't be converted for datetimes. However, if you have servers running in different timezones, that still may be an issue because when you read out, the datetime of the server is used, and there is no guarantee that the server that is reading the record is in the same timezone of the server that wrote the record - this may introduce errors.

To get around this. I once worked on a team that stored datetimes as strings with the timezone appended to the end.

// In this format
Mon, 17 Apr 2006 21:22:48 GMT

We then converted them into Java 8 String Datetime objects when we read from the database. However, I understand that this is a very specific implementation.

  • If we store the date in varchar we can't put condition on that field while retrieving the records. But yes if we store date in above format our issue will be fixed but it will create problem during retrieve query. –  Oct 03 '17 at 07:28
  • Maybe the convert_tz() function will solve your problem: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz –  Oct 03 '17 at 14:36