Guide to Time-zones, Conversion between Zones and Storing in MySQL
We often deal with date and time information in our applications and sometimes need to store this information and convert between different time zones. So here I am discussing few use-cases of time zone information and how to properly handle those situations. This article will discuss the below points,
- What is UTC time & GMT time ?
- TIMESTAMP vs DATETIME in MySQL
- Convert time information between different zones
- How to store time zone information in MySQL ?
What is UTC time & GMT time ?
Since time needs to be known and coordinated across the entire world, Coordinated Universal Time (UTC) is used as the standard absolute time reference. UTC evolved from Greenwich Mean Time (GMT) for use as a standard.
GMT is currently used as a time zone that has no offset from UTC. UTC is a time reference and GMT is a time zone. No place on earth uses UTC as an official time zone.
Practically speaking, GMT time is the equivalent of UTC time (GMT = UTC+0). In summary, the current time reference is not GMT but UTC.
TIMESTAMP vs DATETIME in MySQL
DATETIME: It 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.
TIMESTAMP: It 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.
- In MySQL5+, TIMESTAMP values are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval. But DATETIME does not do any conversion.
- As a result TIMESTAMP differs with current timezone settings while DATETIME remains constant.
- Queries with DATETIME will not be cached but queries with TIMESTAMP will be cached.
- TIMESTAMP data can be indexed while DATETIME data can not.
When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system_time_zone system variable. The value does not change thereafter.
Below MySQL commands can be used to view those time zone information and to change them manually.
mysql> SELECT @@global.time_zone, @@session.time_zone;
mysql> SET session time_zone =’+5:00';
Convert time information between different zones
When we are dealing with these time zone information, sometimes we need to convert between different time zones. We can use java 8 libraries to easily implement a simple time zone convertor for converting time between different time zones.Object instances from below Java 8 classes will store an instant on the timeline to nanosecond precision.
- OffsetDateTime -This class stores all date and time fields, to a precision of nanoseconds, as well as the offset from UTC/Greenwich.
eg: 13:45.30.123456789 +02:00 - ZonedDateTime -This class stores all date and time fields, to a precision of nanoseconds, and a time-zone, with a zone offset used to handle ambiguous local date-times.
eg: 13:45.30.123456789 +02:00 - Instant -This class models a single instantaneous point on the time-line. This might be used to record event time-stamps in the application.
Instant is the simplest, simply representing the epoch-seconds (without offset information). OffsetDateTime adds to the instant the offset from UTC, which allows the local date-time to be obtained. ZonedDateTime uses full time-zone rules while handling dates.
For more information on this, you may read the official oracle documentation
Below is an example of how to implement this time zone conversion with Java. Let’s assume a time stamp is given in the ISO 8601 format as 2019-10-10T10:00:00+04:00Z and we need to convert this to Colombo time stamp of UTC+5: 30 hours. In the above time representation, T is just a literal to separate the date from the time and Z means “zero hour offset” also known as “Zulu time” (UTC).
1) First remove the character ‘Z’ from input string and define the ‘DateTimeFormatter’ to map the input string.
2) Then let’s read the timestamp information to a OffsetDateTime instance by passing the input timestamp string with the DateTimeFormatter defined for the input timestamp.
3) Now convert the time obtained in input time zone to an OffsetDateTime instance of required time zone
4) Finally you can convert OffsetDateTime instance to any required time format.
5) You can follow the same procedure to obtain the time in server’s time zone by defining a ZoneOffset to get the the local time zone offset.
Source-code and unit tests are available in the below GitHub repository.
How to store time zone information in MySQL
There is no any inbuilt feature in MySQL to store different time values with multiple time zone information. So if you need to store the time zone details along with the time, then you will need to store the time zone (like “Colombo”) in another VARCHAR column. And the time value can be stored in a DATETIME or TIMESTAMP column in MySQL. You can store the numeric value of time either in UTC time or server time using the above time conversion method. Then you can either convert it back to stored time zone or any other required time zone when retrieving back the time stamp.
But it is not recommended to store the time with the offset due to the effect of the DST.
Daylight Saving Time (DST), also known as summer time, is observed by advancing clocks one hour in the spring and then reverting to standard time in the autumn. Since UTC is the absolute time reference, it is not affected by DST. What is affected by DST is the local offset.
