This work will add a standard SQL construction that can be used for retrieving a TIMESTAMP value in UTC time, the way it is read from the handler.

The construction adds the AT TIME ZONE operator, but it may only be used in a very limited context. See Functional and Non-Functional Requirements for details.

Example when the session's time zone is CET:

mysql> CREATE TABLE t1 ( a TIMESTAMP );
mysql> INSERT INTO t1 VALUES ( CURRENT_TIMESTAMP );
mysql> SELECT a FROM t1;
+---------------------+
| a                   |
+---------------------+
| 2020-04-29 16:43:19 |
+---------------------+
mysql> SELECT cast( a AT TIME ZONE 'UTC' AS DATETIME ) FROM t1;
+------------------------------------------+
| cast( a AT TIME ZONE 'UTC' AS DATETIME ) |
+------------------------------------------+
| 2020-04-29 14:43:19                      |
+------------------------------------------+

• F-1 The construction CAST ( <timestamp> AT TIME ZONE [INTERVAL] '+00:00' AS DATETIME[(<precision>)] ) is now allowed and will interpret a TIMESTAMP value as being in UTC.

• F-2 The construction CAST ( <timestamp> AT TIME ZONE 'UTC' AS DATETIME[(<precision>)] ) is also allowed and is a synonym for the above. Note that the INTERVAL keyword may not be used.

• F-3 Only a value of type TIMESTAMP may be used in the new CAST() constructions above. The time is then conceptually converted to UTC.

• NF-1 For a TIMESTAMP column used in the new CAST() construction as in F-3, the conversion from UTC to the current time zone is in fact elided as internally the value is stored in UTC.

Syntax

Standard SQL cast syntax is defined as follows:

<cast specification> ::=
    CAST <left paren> <cast operand> AS <cast target> <right paren>

This work borrows from the syntax, allowing the following:

<cast specification> ::=
    CAST <left paren> <expr> <time zone> AS <cast target> <right paren>

<time zone> ::=
    AT <time zone specifier>

<time_zone_specifier>
    LOCAL
  | TIME ZONE [INTERVAL] TEXT_STRING

<cast target> ::=
    DATETIME

Within the CAST clause, AT TIME ZONE may now be appended to the <cast operand>. This is the only place where this construction may be used. In standard SQL, this specifies that the source expression is converted to having a data type with time zone information. Since the construction may only be used on the <cast operand>, we ensure that the data type does not appear outside the evaluation of the CAST clause. What happens is that the value is interpreted as-is –in UTC– and is returned as such. The only case where the behavior differs is the case when the <cast operand> is a TIMESTAMP column, and this is the goal: In this case the value stored is returned without conversion rather than being converted to the session's time zone.

Errors

Use of New Errors

The new syntax only borrows from standard SQL, but does not add the types TIMESTAMP WITH TIME ZONE or INTERVAL, nor does it support the AT TIME ZONE operator except in the special case described above. To guard against such syntax ever being executed, the following two errors are added:

• ER_INVALID_TIME_ZONE_INTERVAL

This error will be raised for any attempt at using any time zone except UTC or +00:00, or when using UTC in conjunction with INTERVAL.

• ER_INVALID_CAST

This error will be raised for any attempt at using anything else than a TIMESTAMP column or a datetime literal as cast operand.

Use of Existing Errors

A normal parse error, ER_PARSE_ERROR will be raised if the cast target is anything other than DATETIME.

Parser

The new construction is only added in the production rule handling the CAST syntax. This ensures that it cannot be used in any other place.

AST

New parse tree node classes are created for the <expr> <time zone> construction. Standard SQL names are used as far as possible.

Items

The AT TIME ZONE construction is implemented as a function (Item_func) from a (<datetime> WITHOUT TIME ZONE, <time zone>) to a <datetime> WITH TIME ZONE. Since there is no get_xxx() function for types with time zone details, the result is inevitably always converted to a <datetime> WITHOUT TIME ZONE, however.

A new interface Item::get_timeval_at() is added, which extends the Item::get_timeval() interface by taking a Time_zone as parameter. The get_timeval() now calls the new function with the sessions time zone as parameter.

Fields

An interface similar to the above, Field::get_timestamp_at() is also added. The difference is that the existing functions convert a TIMESTAMP value to the session's time zone. This new interface allows the caller to pass the UTC time zone as argument, and in the future any time zone can be passed, should we decide to implement casting from any time zone, or for that matter a full-fledged WITH TIME ZONE data type.