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

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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21593 from_unixtime can not be longer used in Virtual Column

          • Major - Major loss of function.
          • Stalled

          Activity

            alice Alice Sherepa added a comment -

            Thanks a lot! I repeated on MariaDb 5.5-10.4:

            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)

            alice Alice Sherepa added a comment - Thanks a lot! I repeated on MariaDb 5.5-10.4: 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)

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.