Db2的时间转换（字符串，时间戳，Unix时间戳）

环境

Ubuntu 22.04
Db2 11.5.0

时间类型

参见 https://www.ibm.com/docs/en/db2/11.5?topic=list-datetime-values

Db2有3种时间类型：

DATE ：例如 03/20/2023 ，当前日期的special register为 CURRENT DATE
TIME ：例如 21:23:23 ，当前时间的special register为 CURRENT TIME
TIMESTAMP ：例如 2023-03-20-21.23.49.513704 ，当前时间戳的special register为 CURRENT TIMESTAMP

可以通过

values current date

，也可以通过

select current date from sysibm.sysdummy1

的方式来查询当前日期/时间。

➜  ~ db2 values current date

1         
----------
03/20/2023

  1 record(s) selected.

➜  ~ db2 values current time

1       
--------
21:23:23

  1 record(s) selected.

➜  ~ db2 values current timestamp

1                         
--------------------------
2023-03-20-21.23.49.513704

  1 record(s) selected.

时间是可以做加减运算的，以时间戳为例：

➜  ~ db2 "select timestamp('2023-03-22-11.26.39.483219') - timestamp('2023-03-22-11.26.38.065497') from sysibm.sysdummy1"

1
----------------------
              1.417722

  1 record(s) selected.

可见，这2个时间戳相差

1.417722

秒。

但是，当时间戳相差超过1分钟时，情况就不一样了：

➜  ~ db2 "select timestamp('2023-03-22-11.26.39.483219') - timestamp('2023-03-22-11.25.38.065497') from sysibm.sysdummy1"

1
----------------------
            101.417722

  1 record(s) selected.

结果并不是期望的

61.417722

，这一点要注意。要想获取时间差，需要使用

timestampdiff()

函数，后面会有介绍。

时间转换

即 字符串，时间戳，Unix时间戳 之间的相互转换。

注：广义的时间戳，也称为Unix时间戳，是指格林威治时间自1970年1月1日（00:00:00 GMT）至当前时间的总秒数（或毫秒数）。比如

1679408091745

，其对应的北京时间是

2023-03-21 22:14:51

。

为了避免混淆，本文中把广义的时间戳称为 Unix时间戳 ，把Db2的timestamp类型称为 时间戳 。

准备

首先创建表

t1

，它有3个字段：

c1 ： timestamp 类型
c2 ： varchar 类型
c3 ： bigint 类型

➜  ~ db2 "create table t1 (c1 timestamp, c2 varchar(50), c3 bigint)"
DB20000I  The SQL command completed successfully.

插入一条记录：

➜  ~ db2 "insert into t1 values ('2023-03-20-21.23.49.513704', '2023-03-20-21.23.49.513704', 1679319723841)"
DB20000I  The SQL command completed successfully.

注意：

c1

是

timestamp

类型，实际插入的值是字符串，它能正确识别并隐式转换。

查询记录：

➜  ~ db2 "select * from t1"                                         

C1                         C2                                                 C3                  
------------------------------------------------------------------------------------------------
2023-03-20-21.23.49.513704 2023-03-20-21.23.49.513704                                1679319723841

  1 record(s) selected.

字符串转时间戳

可以使用

timestamp()

函数来转换，例如：

➜  ~ db2 "select c2, timestamp(c2) from t1"

C2                                                 2                         
----------------------------------------------------------------------------
2023-03-20-21.23.49.513704                         2023-03-20-21.23.49.513704

  1 record(s) selected.

timestamp()

函数

参见 https://www.ibm.com/docs/en/db2/11.5?topic=functions-timestamp

timestamp()

函数的参数可以是字符串，也可以是date、timestamp类型的数据（不能是time类型的，因为缺少日期），比如：

select timestamp(current date) from sysibm.sysdummy1 // 时间会当作0点整
select timestamp('03/22/2023') from sysibm.sysdummy1 // 时间会当作0点整
select timestamp(current timestamp) from sysibm.sysdummy1
select timestamp('2023-03-20-21.23.49.513704') from sysibm.sysdummy1

其参数还可以是

GENERATE_UNIQUE()

函数的结果。先来看一下该函数：

➜  ~ db2 "select GENERATE_UNIQUE() from sysibm.sysdummy1"

1                            
-----------------------------
x'20230322004530944340000000'

  1 record(s) selected.

其类型是

CHAR FOR BIT DATA

，它用16进制数来表示精度到皮秒（10的-12次方秒）的时间戳。在本例中，该时间戳为

2023-03-22-00.45.30.944340

。我想计算机的精度可能到不了皮秒，只能到微秒，所以最后6位都是

。

这么做的好处是省空间，只用了13个字节（在16进制中，每2个数字占用1个字节）

该数值也可以作为

timestamp()

函数的参数：

➜  ~ db2 "select timestamp(x'20230322004530944340000000') from sysibm.sysdummy1"

1                         
--------------------------
2023-03-22-00.45.30.944340

  1 record(s) selected.

timestamp()

函数的参数还可以是14个数字，代表

yyyyxxddhhmmss

，比如：

➜  ~ db2 "select timestamp('20230322092651') from sysibm.sysdummy1"

1                         
--------------------------
2023-03-22-09.26.51.000000

  1 record(s) selected.

此外，

timestamp()

函数还可以有第2个参数，根据第2个参数的类型，又分为两种情况：

非数字：第1个参数必须是date类型，或者能表示date的字符串，第2个参数必须是time类型，或者能表示time的字符串，比如：

➜  ~ db2 "select timestamp('03/22/2023', '09:26:51') from sysibm.sysdummy1"

1                         
--------------------------
2023-03-22-09.26.51.000000

  1 record(s) selected.

数字：第1个参数必须是date、timestamp或者能表示date、timestamp的字符串，第2个参数必须是0到12之间的整数，代表时间戳的精度（也就是以秒为单位，小数点后面保留几位）。比如：

➜  ~ db2 "select timestamp('2023-03-20-21.23.49.513704', 12) from sysibm.sysdummy1"

1                               
--------------------------------
2023-03-20-21.23.49.513704000000

  1 record(s) selected.

时间戳转字符串

to_char()

函数（也叫

varchar_format()

函数）可以把date或者timestamp转换为varchar，参见 https://www.ibm.com/docs/en/db2/11.5?topic=functions-varchar-format

例如：

➜  ~ db2 "select c1, to_char(c1, 'YYYY-MM-DD HH24:MI:SS.NNNNNN') from t1"

C1                         2                                                                                                                                                                                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2023-03-20-21.23.49.513704 2023-03-20 21:23:49.513704                                                                                                                                                                                                                                    

  1 record(s) selected.

to_char()

函数有很多参数选项，没有深入研究，等有需要的时候再查官方文档。

时间戳转Unix时间戳

前面提到，两个时间戳直接相减，并不能得到期望的结果，而要使用

timestampdiff()

函数。参见 https://www.ibm.com/docs/en/db2/11.5?topic=functions-timestampdiff

例如：

➜  ~ db2 "select timestampdiff(1, char(timestamp('2023-03-22-11.26.39.483219') - timestamp('2023-03-22-11.25.38.065497'))) from sysibm.sysdummy1"

1
-----------
   61417722

  1 record(s) selected.

这回得到了期望的

秒（

61417722

的单位是微秒）。

timestampdiff()

函数有2个参数：

第1个参数是一个整数，意义如下： - 1：Microseconds- 2： Seconds- 4： Minutes- 8： Hours- 16： Days- 32： Weeks- 64： Months- 128： Quarters- 256： Years
第2个参数是一个字符串（若不是字符串则会隐式转换为字符串），表示两个时间戳相减的结果。

**要格外注意的是，

timestampdiff()

函数的结果是不精准的**。它认为1年就是365天，1个月就是30天，例如：

➜  ~ db2 "select timestampdiff(16, char(timestamp('2023-03-01-00.00.00.000000') - timestamp('2023-02-01-00.00.00.000000'))) from sysibm.sysdummy1"

1
-----------
         30

  1 record(s) selected.

从2月1日到3月1日，期望结果是

天，而实际结果是

天。

既然

timestampdiff()

函数不精准，就要想其它办法。比如我们可以来“硬算”时间，具体方法为，对于给定的时间戳，先计算它和

1970-01-01

之间的日期差别，乘以一天的秒数

，再加上秒数即可。

例如，对于时间戳

2023-01-01 06:12:34.567

：

➜  ~ db2 "select 86400 * (days(timestamp('2023-01-01 06:12:34.567')) - days('1970-01-01')) + midnight_seconds(timestamp('2023-01-01 06:12:34.567')) from sysibm.sysdummy1"

1
-----------
 1672553554

  1 record(s) selected.

在表

t1

中，

c1

是时间戳，其值为

2023-03-20-21.23.49.513704

，对应的Unix时间戳为

1679347429513

（精确到毫秒），我们来验证一下：

➜  ~ db2 "select c1,  86400 * (days(c1) - days('1970-01-01')) + midnight_seconds(c1) from t1"

C1                         2
-------------------------------------
2023-03-20-21.23.49.513704  1679347429

  1 record(s) selected.

注意：该方法的精度只到秒级别。

Unix时间戳转时间戳

只需把Unix时间戳加到

1970-01-01 00:00:00.000

上，就可以得到其对应的时间戳，例如：

➜  ~ db2 "select timestamp('1970-01-01 00:00:00.000') + 120.123456 SECONDS from sysibm.sysdummy1"

1
--------------------------
1970-01-01-00.02.00.123456

  1 record(s) selected.

在表

t1

中，

c3

是Unix时间戳（类型为bigint），其值为

1679319723841

，对应的时间戳为

2023-03-20 13:42:03.841

，我们来验证一下：

➜  ~ db2 "select c3, timestamp('1970-01-01 00:00:00.000') + (c3 / 1000.0) SECONDS from t1"

C3                   2
----------------------------------------------
       1679319723841 2023-03-20-13.42.03.841000

  1 record(s) selected.

注意：

1679319723841

是毫秒精度的，要得到秒级的数值，需要除以

，但整数运算的结果还是整数，为了保留小数，需要除以

1000.0

。

时区

前面的例子，都假设是UTC时间，没有做任何时区转换的处理。

对于时间戳和Unix时间戳而言：

Unix时间戳：它只是两个时间戳之间的差值，我们可以认为它不涉及时区问题。
时间戳：就涉及到时区问题了，显然中国的3月22号20点和美国的3月22号20点是不同的时间。

比如

2023-03-20 13:42:03.841

，假定这是UTC的时间，则在中国和美国表现为：

中国（东8区）： 2023-03-20 21:42:03.841
美国太平洋时区（西8区）： 2023-03-20 06:42:03.841 （夏令时时间）

前面提到，

current timestamp

special register表示当前时间戳，这个时间戳是带时区的，也就是数据库本地的时间。

Db2还提供了

current timezone

special register，表示所在时区，例如，对于美国太平洋时区：

➜  ~ db2 "select current timezone from sysibm.sysdummy1"

1
---------70000.

  1 record(s) selected.

虽然是西8区，但现在是美国的夏令时，所以是和UTC时间差7个小时。

对于带时区信息的时间戳，若想获取对应的UTC时间戳，只需减去

current timezone

special register。

➜  ~ db2 "select current timestamp - current timezone from sysibm.sysdummy1"

1
--------------------------
2023-03-23-02.55.39.808973

  1 record(s) selected.

标签：数据库 sql db2

本文转载自: https://blog.csdn.net/duke_ding2/article/details/129676993
版权归原作者 蓝黑2020 所有，如有侵权，请联系我们删除。

Db2的时间转换（字符串，时间戳，Unix时间戳）

环境

时间类型

时间转换

准备

字符串转时间戳

时间戳转字符串

时间戳转Unix时间戳

Unix时间戳转时间戳

时区

发表评论

“Db2的时间转换（字符串，时间戳，Unix时间戳）”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航