Топ-100
 

CAST

База данных: MySQL
Текст

Syntax:
CAST(expr AS type [ARRAY])

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

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

The CAST() function takes an expression of any type and produces a
result value of the specified type, similar to CONVERT(). For more
information, see the description of CONVERT().

In MySQL 8.0.17 and later, 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.0/en/create-index.html#create-index-
multi-valued.

*Note*:

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

Beginning with MySQL 8.0.22, 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.

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

Пример

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)