hgdb中的时区与时间戳

文档用途

本文介绍了在HGDB中，带时区和不带时区的时间戳类型如何使用及选择

详细信息

时间戳类型

hgdb中提供了两种用于处理时间戳的数据类型，一种不带时区（timestamp），一种带时区（timestamptz）。

timestamp同时存储日期和时间，但是没有时区数据，当数据库时区修改时，存储在数据库中的时间戳不会更改。

timestampz将存储为UTC形式，hgdb会将timestampz转换为格林威治时间1970年01月01日00时00分00秒起至现在的总秒数（UTC)，查询时，根据每个会话设定的时区进行转换。

下面示例演示timestamp与timestamptz的不同

  highgo=# create table timestamp_test(ts timestamp,tstz timestamptz);

    CREATE TABLE

    highgo=# insert into timestamp_test(ts,tstz) values('2020-03-04 11:12:13','2020-03-04 11:12:13');

    INSERT 0 1

    highgo=# show timezone;

     TimeZone 

    ----------

     PRC

    (1 row)

    highgo=# select * from timestamp_test ;

             ts          |          tstz          

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

     2020-03-04 11:12:13 | 2020-03-04 11:12:13+08

    (1 row)

    --修改会话的时区

    highgo=# set timezone=UTC;

    SET

    --修改时区后，可以看到，tstz(带时区)的数值根据时间发生了改变

    highgo=# select * from timestamp_test ;

             ts          |          tstz          

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

     2020-03-04 11:12:13 | 2020-03-04 03:12:13+00

    (1 row)

时区转换

方式一

在SQL语句中，使用“at time zone”进行转换

   --timestamp类型不会显示时区，只是增加“+00”表示进行了时区转换。timestamptz时间根据设定的时区显示

    highgo=# select ts at time zone 'UTC',tstz at time zone 'UTC'  from timestamp_test ;

            timezone        |      timezone       

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

     2020-03-04 19:12:13+08 | 2020-03-04 03:12:13

    (1 row)

方式二

使用函数timezone()

highgo=# select timezone('UTC',ts),timezone('UTC',tstz) from timestamp_test ;

        timezone        |      timezone       

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

 2020-03-04 19:12:13+08 | 2020-03-04 03:12:13

(1 row)

带时区与不带时区间的类型转换

timestamp with time zone可以将时间转换为带当前数据库会话设置使用的时区，显示时间不会变化，只是最后添加时区。timestamp without time zone可以将带时区数据显示为不带时区的数据，但显示时间会根据当前会话使用时区发生变化。

下面示例通过调整会话时区展示类型转换效果，PRC为北京/上海时区（东8区），UTC为格林威治时区（00区）

   highgo=# show timezone;

     TimeZone 

    ----------

     PRC

    (1 row)

    

    highgo=# select ts::timestamp with time zone,tstz::timestamp without time zone from timestamp_test ;

               ts           |        tstz         

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

     2020-03-04 11:12:13+08 | 2020-03-04 11:12:13

    (1 row)

    

    highgo=# set timezone=UTC;

    SET

    highgo=# select ts::timestamp with time zone,tstz::timestamp without time zone from timestamp_test ;

               ts           |        tstz         

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

     2020-03-04 11:12:13+00 | 2020-03-04 03:12:13

    (1 row)

与会话使用时区相关的函数

now()、CURRENT_TIMESTAMP、CURRENT_TIME、timeofday()等函数都能返回带时区的当前时间。

  highgo=# select now();

                  now              

    -------------------------------

     2020-03-25 15:59:46.206333+08

    (1 row)

    

    highgo=# SELECT CURRENT_TIMESTAMP;

           current_timestamp       

    -------------------------------

     2020-03-25 16:03:48.065896+08

    (1 row)

    

    highgo=# SELECT CURRENT_TIME;

       current_time    

    -------------------

     16:04:14.51311+08

    (1 row)

    

    highgo=# SELECT timeofday();

                  timeofday              

    -------------------------------------

     Wed Mar 25 16:06:06.998214 2020 CST

    (1 row)

to_timestamp

to_timestamp将符合条件的其他类型（字符串、数值等）转换为带时区的时间戳，通过下面示例可以看到转换后的数据是带时区的。

highgo=# SELECT to_timestamp(1582384720.12);

       to_timestamp        

---------------------------

 2020-02-22 23:18:40.12+08

(1 row)



highgo=# select to_timestamp('2020-03-14 12:13:14','yyyy-mm-dd H24:mi:ss');

      to_timestamp      

------------------------

 2020-03-14 00:03:14+08

(1 row)

时区类型的选择

在HGDB中，每个会话可以即时修改当前使用的时区，因此，如果使用没有时区的类型可能会在写入时造成数据混乱。下面示例演示了客户端时区变化时，带有时区的时间跟随时区发生变化，但不带时区的时间并未跟随时区改变而改变。如果数据需要跨时区迁移，或客户端可以自定义时区，选择使用带有时区的时间类型更友好。

  highgo=# set timezone=UTC;

    SET

    highgo=# show timezone;

     TimeZone 

    ----------

     UTC

    (1 row)

    

    highgo=# insert into timestamp_test (ts,tstz) values(to_timestamp('2020-03-14 12:13:14','yyyy-mm-dd HH24:mi:ss'),to_timestamp('2020-03-14 12:13:14','yyyy-mm-dd HH24:mi:ss'));

    INSERT 0 1

    highgo=# select * from timestamp_test ;

             ts          |          tstz          

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

     2020-03-14 12:13:14 | 2020-03-14 12:13:14+00

    (1 row)

    

    highgo=# set timezone=PRC;

    SET

    highgo=# select * from timestamp_test ;

             ts          |          tstz          

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

     2020-03-14 12:13:14 | 2020-03-14 20:13:14+08

    (1 row)