|
CONVERT_TZ isn't supported in ColumnStore, but is supported in MariaDB Enterprise.
With native support of the columnstore engine in MariaDB Enterprise, the use of CONVERT_TZ against columnstore tables seem to be a NOOP and doesn't generate an error or warning. This may confuse customers who may be expecting a different result.
*INNODB table
mysql> create table test_tbl (id bigint, name varchar(32), created_at timestamp default now());
|
Query OK, 0 rows affected (0.06 sec)
|
|
mysql> insert into test_tbl (id, name) values (1,'a'),(2,'b'),(3,'c');
|
Query OK, 3 rows affected (0.06 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
mysql> select * from test_tbl;
|
+------+------+---------------------+
|
| id | name | created_at |
|
+------+------+---------------------+
|
| 1 | a | 2020-09-11 17:46:24 |
|
| 2 | b | 2020-09-11 17:46:24 |
|
| 3 | c | 2020-09-11 17:46:24 |
|
+------+------+---------------------+
|
3 rows in set (0.05 sec)
|
mysql> select convert_tz(created_at,'UTC','US/Eastern') from test_tbl;
|
+-------------------------------------------+
|
| convert_tz(created_at,'UTC','US/Eastern') |
|
+-------------------------------------------+
|
| 2020-09-11 13:46:24 |
|
| 2020-09-11 13:46:24 |
|
| 2020-09-11 13:46:24 |
|
+-------------------------------------------+
|
3 rows in set (0.15 sec)
|
mysql> create table test_cs_tbl (id bigint, name varchar(32), created_at timestamp) engine=columnstore;
|
Query OK, 0 rows affected (0.59 sec)
|
|
mysql> insert into test_cs_tbl values (1,'z','2020-09-11 12:00:00'),(2,'y','2020-09-11 13:00:00'),(3,'x','2020-09-11 14:00:00');
|
Query OK, 3 rows affected (1.28 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
mysql> select * from test_cs_tbl;
|
+------+------+---------------------+
|
| id | name | created_at |
|
+------+------+---------------------+
|
| 1 | z | 2020-09-11 12:00:00 |
|
| 2 | y | 2020-09-11 13:00:00 |
|
| 3 | x | 2020-09-11 14:00:00 |
|
+------+------+---------------------+
|
3 rows in set (0.07 sec)
|
|
mysql> select convert_tz(created_at,'UTC','US/Eastern') from test_cs_tbl;
|
+-------------------------------------------+
|
| convert_tz(created_at,'UTC','US/Eastern') |
|
+-------------------------------------------+
|
| 2020-09-11 12:00:00 |
|
| 2020-09-11 13:00:00 |
|
| 2020-09-11 14:00:00 |
|
+-------------------------------------------+
|
3 rows in set (0.06 sec)
|
|