Syntax:
CAST(expr AS type [ARRAY])

CAST(timestamp_value AT TIME ZONE timezone_specifier AS
DATETIME[(precision)])

timezone_specifier: [INTERVAL] '+00:00' | 'UTC'

With CAST(expr AS type syntax, the CAST() function takes an expression
of any type and produces a result value of the specified type. This
operation may also be expressed as CONVERT(expr, type), which is
equivalent. If expr is NULL, CAST() returns NULL.

These type values are permitted:

o BINARY[(N)]

  Produces a string with the VARBINARY data type, except that when the
  expression expr is empty (zero length), the result type is BINARY(0).
  If the optional length N is given, BINARY(N) causes the cast to use
  no more than N bytes of the argument. Values shorter than N bytes are
  padded with 0x00 bytes to a length of N. If the optional length N is
  not given, MySQL calculates the maximum length from the expression.
  If the supplied or calculated length is greater than an internal
  threshold, the result type is BLOB. If the length is still too long,
  the result type is LONGBLOB.

  For a description of how casting to BINARY affects comparisons, see
  https://dev.mysql.com/doc/refman/8.4/en/binary-varbinary.html.

o CHAR[(N)] [charset_info]

  Produces a string with the VARCHAR data type, unless the expression
  expr is empty (zero length), in which case the result type is
  CHAR(0). If the optional length N is given, CHAR(N) causes the cast
  to use no more than N characters of the argument. No padding occurs
  for values shorter than N characters. If the optional length N is not
  given, MySQL calculates the maximum length from the expression. If
  the supplied or calculated length is greater than an internal
  threshold, the result type is TEXT. If the length is still too long,
  the result type is LONGTEXT.

  With no charset_info clause, CHAR produces a string with the default
  character set. To specify the character set explicitly, these
  charset_info values are permitted:

  o CHARACTER SET charset_name: Produces a string with the given
    character set.

  o ASCII: Shorthand for CHARACTER SET latin1.

  o UNICODE: Shorthand for CHARACTER SET ucs2.

  In all cases, the string has the character set default collation.

o DATE

  Produces a DATE value.

o DATETIME[(M)]

  Produces a DATETIME value. If the optional M value is given, it
  specifies the fractional seconds precision.

o DECIMAL[(M[,D])]

  Produces a DECIMAL value. If the optional M and D values are given,
  they specify the maximum number of digits (the precision) and the
  number of digits following the decimal point (the scale). If D is
  omitted, 0 is assumed. If M is omitted, 10 is assumed.

o DOUBLE

  Produces a DOUBLE result.

o FLOAT[(p)]

  If the precision p is not specified, produces a result of type FLOAT.
  If p is provided and 0 <= < p <= 24, the result is of type FLOAT. If
  25 <= p <= 53, the result is of type DOUBLE. If p < 0 or p > 53, an
  error is returned.

o JSON

  Produces a JSON value. For details on the rules for conversion of
  values between JSON and other types, see
  https://dev.mysql.com/doc/refman/8.4/en/json.html#json-comparison.

o NCHAR[(N)]

  Like CHAR, but produces a string with the national character set. See
  https://dev.mysql.com/doc/refman/8.4/en/charset-national.html.

  Unlike CHAR, NCHAR does not permit trailing character set information
  to be specified.

o REAL

  Produces a result of type REAL. This is actually FLOAT if the
  REAL_AS_FLOAT SQL mode is enabled; otherwise the result is of type
  DOUBLE.

o SIGNED [INTEGER]

  Produces a signed BIGINT value.

o spatial_type

  CAST() and CONVERT() support casting geometry values from one spatial
  type to another, for certain combinations of spatial types. For
  details, see
  https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html#cast-spat
  ial-types.

o TIME[(M)]

  Produces a TIME value. If the optional M value is given, it specifies
  the fractional seconds precision.

o UNSIGNED [INTEGER]

  Produces an unsigned BIGINT value.

o YEAR

  Produces a YEAR value. These rules govern conversion to YEAR as
  follows:

  o For a four-digit number in the range 1901-2155 inclusive, or for a
    string which can be interpreted as a four-digit number in this
    range, return the corresponding YEAR value.

  o For a number consisting of one or two digits, or for a string which
    can be interpreted as such a number, return a YEAR value as
    follows:

    o If the number is in the range 1-69 inclusive, add 2000 and return
      the sum.

    o If the number is in the range 70-99 inclusive, add 1900 and
      return the sum.

  o For a string which evaluates to 0, return 2000.

  o For the number 0, return 0.

  o For a DATE, DATETIME, or TIMESTAMP value, return the YEAR portion
    of the value. For a TIME value, return the current year.

    If you do not specify the type of a TIME argument, you may get a
    different result from what you expect, as shown here:

mysql> SELECT CAST("11:35:00" AS YEAR), CAST(TIME "11:35:00" AS YEAR);
+--------------------------+-------------------------------+
| CAST("11:35:00" AS YEAR) | CAST(TIME "11:35:00" AS YEAR) |
+--------------------------+-------------------------------+
|                     2011 |                          2021 |
+--------------------------+-------------------------------+

  o If the argument is of type DECIMAL, DOUBLE, DECIMAL, or REAL, round
    the value to the nearest integer, then attempt to cast the value to
    YEAR using the rules for integer values, as shown here:

mysql> SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR);
+-----------------------+-----------------------+
| CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) |
+-----------------------+-----------------------+
|                  1944 |                  1945 |
+-----------------------+-----------------------+

mysql> SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR);
+---------------------+---------------------+
| CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) |
+---------------------+---------------------+
|                2066 |                2067 |
+---------------------+---------------------+

  o An argument of type GEOMETRY cannot be converted to YEAR.

  o For a value that cannot be successfully converted to YEAR, return
    NULL.

  A string value containing non-numeric characters which must be
  truncated prior to conversion raises a warning, as shown here:

mysql> SELECT CAST("1979aaa" AS YEAR);
+-------------------------+
| CAST("1979aaa" AS YEAR) |
+-------------------------+
|                    1979 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' |
+---------+------+-------------------------------------------+

InnoDB allows the use of an additional ARRAY keyword for creating a
multi-valued index on a JSON array as part of CREATE INDEX, CREATE
TABLE, and ALTER TABLE statements. ARRAY is not supported except when
used to create a multi-valued index in one of these statements, in
which case it is required. The column being indexed must be a column of
type JSON. With ARRAY, the type following the AS keyword may specify
any of the types supported by CAST(), with the exceptions of BINARY,
JSON, and YEAR. For syntax information and examples, as well as other
relevant information, see
https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-
multi-valued.

*Note*:

CONVERT(), unlike CAST(), does not support multi-valued index creation
or the ARRAY keyword.

CAST() supports retrieval of a TIMESTAMP value as being in UTC, using
the AT TIMEZONE operator. The only supported time zone is UTC; this can
be specified as either of '+00:00' or 'UTC'. The only return type
supported by this syntax is DATETIME, with an optional precision
specifier in the range of 0 to 6, inclusive.

TIMESTAMP values that use timezone offsets are also supported.

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EDT                |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tz (c TIMESTAMP);
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO tz VALUES
    ->     ROW(CURRENT_TIMESTAMP),
    ->     ROW('2020-07-28 14:50:15+1:00');
Query OK, 1 row affected (0.08 sec)

mysql> TABLE tz;
+---------------------+
| c                   |
+---------------------+
| 2020-07-28 09:22:41 |
| 2020-07-28 09:50:15 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz;
+---------------------+
| u                   |
+---------------------+
| 2020-07-28 13:22:41 |
| 2020-07-28 13:50:15 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT CAST(c AT TIME ZONE 'UTC' AS DATETIME(2)) AS u FROM tz;
+------------------------+
| u                      |
+------------------------+
| 2020-07-28 13:22:41.00 |
| 2020-07-28 13:50:15.00 |
+------------------------+
2 rows in set (0.00 sec)

If you use 'UTC' as the time zone specifier with this form of CAST(),
and the server raises an error such as Unknown or incorrect time zone:
'UTC', you may need to install the MySQL time zone tables (see
https://dev.mysql.com/doc/refman/8.4/en/time-zone-support.html#time-zon
e-installation).

AT TIME ZONE does not support the ARRAY keyword, and is not supported
by the CONVERT() function.

URL: https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html

