This is a feature request from Booking.com. The aim here is to mention the time zone details along with the TIMESTAMP value.

Without explicit time zone information or offset, there are date/times in some time zones that are not well defined, like when Daylight Saving Time changes the time 'backwards', like when CEST goes to CET at 02:59:59, and is adjusted back. 02:00:00 then happens again for the second time that day.

Currently, the user can set the session variable time_zone, but this can lead to different time zones across different sessions. Also, Booking would like to have a more general way to specify time zone information.

See BUG#83852 timestamp types can not be used safely due to timezone changes

First, let us establish some terminology that will be used in this document. A literal value that can be inserted into a column of type DATETIME or TIMESTAMP is referred to as a datetime value. In order to distinguish it from the (non-standard) MySQL data type DATETIME, the latter is written in capitals and a typewriter font. The internal representation of the datetime literal is referred to as a date/time value. This term is also used to refer to values sent over the binary client/server protocol.

A datetime value can correspond to a UTC value, but be ahead or behind it by a fixed value. This value is referred to as a time zone displacement.

• F-1. Datetime literal values with a specified time zone displacement shall be accepted. Time zone displacement syntax uses the format {+-}HH:MM and is appended to a regular datetime literal with no intervening whitespace. The literal is generally interpreted as a local time value that can be converted to UTC by subtracting the time zone displacement from the date/time value. The valid range is -14:00 to +14:00, where -00:00 is not allowed, compliant with the SQL standard. Examples:

  2015-10-01 11:59:59-02:00 is 2015-10-01 13:59:59 UTC.
  2015-10-01 11:59:59.999999+05:30 is 2015-10-01 06:29:59.999999 UTC.
  

• F-2. The permitted values for the @@time_zone system variable are now in the range -14:00 to +14:00. There is no other change to permissible values for this variable.

• F-3. Accepted date/time parameter values for prepared statements are in the form of character strings that include time zone displacement, like literal values as described in F1.

• F-4. The internal date/time value shall be adjusted to UTC by subtracting the time zone displacement before performing the following operations:

  • When inserting into or updating a TIMESTAMP column from a datetime literal or date/time parameter value containing a time zone displacement.

  • When comparing a TIMESTAMP value against such a value.

  • When calling a function that accepts a TIMESTAMP argument.

  E.g. 2015-01-01 10:10:10+05:30 shall be converted to 2015-01-01 04:40:10 UTC.

• F-5 The internal date/time value shall be adjusted to UTC by subtracting the time zone displacement and adjusting the date/time value to local time by adding the value of the session variable @@time_zone, before performing the following operations:

  • When inserting into or updating a DATETIME column from a datetime literal or date/time parameter value containing a time zone displacement.

  • When comparing a DATETIME value against such a value.

  • When calling a function that accepts a DATETIME argument.

  E.g. when @@time_zone is +05:30, 2015-01-01 10:10:10+07:30 shall be converted to 2015-01-01 08:10:10.

• F-6. When using an integer value, for example 20190619095000 to mean '2019-06-19 09:50:00' when a DATETIME or TIMESTAMP value is required, it is not syntactically possible to provide an explicit time zone displacement. Thus, the integer value is always interpreted to be in local time zone. There is no change from existing behavior.

• F-7. For an operation as specified in F-4 performed with a literal or parameter without a time zone displacement, the date/time value is adjusted according to the value of the session variable @@time_zone. There is no change from existing behavior.

• F-8. For an operation as specified in F-5 performed with a literal or parameter without a time zone displacement, the date/time value is taken as-is as a value in the local time zone. There is no change from existing behavior.

• F-9. When passed over the prepared statement protocol, time zone displacement is appended to a date/time value after the microsecond value, i.e. after the 12:th byte. In other words, time zone displacement cannot be present without the presence of a microsecond value.

• F-10. There are no other changes in how date/time values are retrieved from the storage engine, processed during query execution, or shipped over the client/server protocol. Such values are always displayed in the local time zone (TIMESTAMP values after adjustment according to @@time_zone, DATETIME values as-is), and no time zone displacement is displayed as part of the values.

Schema

CREATE TABLE ts (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a TIMESTAMP NOT NULL
) AUTO_INCREMENT = 1;

CREATE TABLE dt (
  id INT NOT NULL auto_increment primary key,
  a DATETIME NOT NULL
) AUTO_INCREMENT = 1;

Example 1

mysql> SET time_zone = '+05:30';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO ts ( a ) VALUES ( '2015-01-01 10:10:10' );
Query OK, 1 row affected (0,02 sec)

mysql> INSERT INTO ts ( a ) VALUES ( '2015-01-01 10:10:10+05:30' );
Query OK, 1 row affected (0,01 sec)

mysql> SELECT a, unix_timestamp ( a )  FROM ts;
+---------------------+----------------------+
| a                   | unix_timestamp ( a ) |
+---------------------+----------------------+
| 2015-01-01 10:10:10 |           1420087210 |
| 2015-01-01 10:10:10 |           1420087210 |
+---------------------+----------------------+
2 rows in set (0,00 sec)

Example 2

mysql> SET time_zone = '+03:00';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO ts ( a ) VALUES ( '2015-01-01 10:10:10+02:00' );
Query OK, 1 row affected (0,02 sec)

mysql> SELECT a, unix_timestamp ( a ) FROM ts;
+---------------------+----------------------+
| a                   | unix_timestamp ( a ) |
+---------------------+----------------------+
| 2015-01-01 07:40:10 |           1420087210 |
| 2015-01-01 07:40:10 |           1420087210 |
| 2015-01-01 11:10:10 |           1420099810 |
+---------------------+----------------------+
3 rows in set (0,00 sec)

mysql>
mysql> SET time_zone = '+05:30';
Query OK, 0 rows affected (0,00 sec)

mysql>
mysql> INSERT INTO dt ( a ) VALUES ( '2015-01-01 10:10:10' );
Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO dt ( a ) VALUES ( '2015-01-01 10:10:10+05:30' );
Query OK, 1 row affected (0,02 sec)

mysql> INSERT INTO dt ( a ) VALUES ( '2015-01-01 10:10:10+07:30' );
Query OK, 1 row affected (0,00 sec)

mysql> SELECT a, unix_timestamp ( a )  FROM dt;
+---------------------+----------------------+
| a                   | unix_timestamp ( a ) |
+---------------------+----------------------+
| 2015-01-01 10:10:10 |           1420087210 |
| 2015-01-01 10:10:10 |           1420087210 |
| 2015-01-01 08:10:10 |           1420080010 |
+---------------------+----------------------+
3 rows in set (0,00 sec)

Changes to the Client/Server Protocol

Prior to this work, a value sent over the binary client/server protocol could occupy 4, 7 or 11 bytes. With the new format, a value can also be of length 13, adding a 16-bit signed integer value denoting the displacement from UTC in minutes. The new format is illustrated below, new fields in yellow.

Code Changes

External API

A new member MYSQL_TIME::time_zone_displacement has been added to hold the seconds value of the offset for time zone.

A new member enum_mysql_timestamp_type::MYSQL_TIMESTAMP_DATETIME_TZ will specify that time zone information is included in a literal.

Client Library

Server Code

str_to_offset() has been modified to handle the range -14:00 to +14:00.

time_zone_displacement_to_seconds() has been added to parse the time zone displacement used in datetime literals.

Modified str_to_datetime() to handle time zone information in literals. For valid values, time_type is set to MYSQL_TIMESTAMP_DATETIME_TZ and time_zone_displacement is set to the time zone offset. No error handling has been added. Value is ignored if time_zone_displacement_to_seconds() returns true.

A new function adjust_time_zone_displacement() has been added. This is used for the DATETIME datatype. This will subtract the time zone displacement and create a new MYSQL_TIME value. (Handles the requirement mentioned in F-5).

This function will be called from Field_datetimef::store_internal(), Field_datetime::store_internal(), create_temporal_literal(), MYSQL_TIME_cache::set_datetime, str_to_datetime_with_warn(), and str_to_time_with_warn().

New function my_tz_find() added to create (or return cached object) of Time_zone with offset value.

Test Changes

Changes to sys_vars.time_zone_basic:Earlier in -str_to_offset (tztime.cc) range was -13 to +13. The renamed convert_timezone_to_seconds changed it to -/+14 for SQL standard compliance.