Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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

    XMLWordPrintable

    Details

      Description

      When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes.

      mariadb10.3:

       
       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)
       

      mysql5.7:

       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)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              1152175253@qq.com hongsheng zhou
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: