[#MDEV-21249] MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes

 create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间');

insert into test_timestamp (`create_timestamp`) value (1569495326.861);

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.00 sec)

 mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间',

    ->   `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间');

Query OK, 0 rows affected (0.31 sec)

mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861);

Query OK, 1 row affected (0.03 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 10:55:26.861 |

+------------------+-------------------------+

1 row in set (0.01 sec)

mysql> set time_zone = "+01:00";

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_timestamp;

+------------------+-------------------------+

| create_timestamp | create_time             |

+------------------+-------------------------+

|    1569495326861 | 2019-09-26 11:55:26.861 |

+------------------+-------------------------+

1 row in set (0.00 sec)

create table t1 (a int, b timestamp GENERATED ALWAYS AS (from_unixtime(a)) VIRTUAL);

insert into t1 (a) value (1569495327);

select a, b, from_unixtime(a) from t1;

set time_zone = "+01:00";

select a, b, from_unixtime(a) from t1;

drop table t1;

MariaDB [test]> select a, b, from_unixtime(a) from t1;

+------------+---------------------+---------------------+

| a          | b                   | from_unixtime(a)    |

+------------+---------------------+---------------------+

| 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 12:55:27 |

+------------+---------------------+---------------------+

1 row in set (0.000 sec)

MariaDB [test]> set time_zone = "+01:00";

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select a, b, from_unixtime(a) from t1;

+------------+---------------------+---------------------+

| a          | b                   | from_unixtime(a)    |

+------------+---------------------+---------------------+

| 1569495327 | 2019-09-26 12:55:27 | 2019-09-26 11:55:27 |

+------------+---------------------+---------------------+

1 row in set (0.000 sec)

[MDEV-21249] MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes Created: 2019-12-08 Updated: 2020-01-29 Resolved: 2020-01-21
Status:	Closed
Project:	MariaDB Server
Component/s:	Virtual Columns
Affects Version/s:	5.5, 10.0, 10.1, 10.3.20, 10.2, 10.3, 10.4
Fix Version/s:	10.2.31, 10.3.22, 10.4.12

[MDEV-21249] MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes Created: 2019-12-08 Updated: 2020-01-29 Resolved: 2020-01-21