1. Toy模板网首页
  2. > Toy博客

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

10月前 作者：蓝黑2020 分类：Toy博客 阅读(59) 违法举报

这篇具有很好参考价值的文章主要介绍了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位都是 0 。

这么做的好处是省空间，只用了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.

这回得到了期望的 61 秒（ 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日，期望结果是 28 天，而实际结果是 30 天。

既然 timestampdiff() 函数不精准，就要想其它办法。比如我们可以来“硬算”时间，具体方法为，对于给定的时间戳，先计算它和 1970-01-01 之间的日期差别，乘以一天的秒数 86400 ，再加上秒数即可。

例如，对于时间戳 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 ，但整数运算的结果还是整数，为了保留小数，需要除以 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。文章来源地址https://www.toymoban.com/news/detail-476418.html

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

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

  1 record(s) selected.

到了这里，关于Db2的时间转换（字符串，时间戳，Unix时间戳）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • pg使用sql将文本字符串转换成时间格式

    使用 PostgreSQL 数据库的 SQL 查询语句将文本字符串转换为时间格式，可以使用 to_timestamp 函数。 假设您的文本字符串时间格式为 “yyyy-MM-dd HH:mm:ss”，您可以使用以下 SQL 查询来转换： 这将返回一个时间戳类型的结果，其中包含从文本字符串转换而来的时间。 您可以在您的

    2024年02月12日
    浏览(60)

  • Pandas中的字符串和时间转换与格式化

    Pandas 提供了若干个函数来格式化时间。 其中，最常用的是 to_datetime() 函数。 可以使用 to_datetime() 函数将一个字符串解析为时间，并指定字符串的格式。例如： 输出： 还可以使用 strftime() 函数将时间格式化为字符串。例如： 输出： 如果想要格式化某一列中的时间，可以使用

    2024年02月04日
    浏览(45)

  • JS时间对象（Date）与字符串（String）相互转换

    运行结果： D:DevEnvironmentNodeJsnode.exe D:MyProjectscoding-studysrcmainresourcesstaticutils.js format1 = 2022年09月23日 23:24:39.836 星期五 第3季度 format2 = 2022年9月23日 11:24:39.836 am 周五 第3季度 format3 = 2022-09-23 23:24:39.836 am 五 Process finished with exit code 0 运行结果： D:DevEnvironmentNodeJsnode.exe

    2024年02月16日
    浏览(49)

  • 52_Pandas处理日期和时间列（字符串转换、日期提取等）

    将解释如何操作表示 pandas.DataFrame 的日期和时间（日期和时间）的列。字符串与 datetime64[ns] 类型的相互转换，将日期和时间提取为数字的方法等。 以下内容进行说明。 如何将 datetime64[ns] 类型指定为索引并将其处理为时序数据以及如何使用，请参考以下文章。 26_Pandas.DataFr

    2024年01月22日
    浏览(50)

  • hive中时间戳与时间字符串相互转换的方法教程

    时间戳是数据库常用的存放日期的形式之一，表示从 UTC 时间’1970-01-01 00:00:00’开始到现在的秒数，与常规时间格式如 ‘2018-01-01 00:00:00’可以相互转换，方法如下。 一、unix_timestamp 函数用法 1、unix_timestamp() 返回当前时间戳。另外，current_timestamp() 也有同样作用。 2、unix_ti

    2024年02月13日
    浏览(53)
  • 前端中不同格式的日期相互转换（字符串、时间戳）js相关

    前端中不同格式的日期相互转换（字符串、时间戳）js相关

    在项目中遇到了，需要实现字符串和Unix时间戳的相互转换，随手记录一下。 我使用的组件库为Naive UI，涉及到的组件为日期选择器（Date Picker）。作者在文档中写道： 实话说我不喜欢这个 feature，因为多数情况下，传递时间字符串不是个最佳实践。但是现实世界是复杂的，我

    2024年02月02日
    浏览(69)

  • spark SQL 怎么将一个时间戳字符串转换成hive支持的时间日期类型？

    在 Spark SQL 中，可以使用 to_timestamp 函数将一个时间戳字符串转换成 Hive 支持的时间日期类型。这个函数的语法如下： 其中，timestampStr 表示要转换的时间戳字符串，format 表示时间戳字符串的格式，格式必须与时间戳字符串的实际格式相匹配。如果不指定格式，Spark 会使用默认

    2024年02月11日
    浏览(49)
  • JS日期与字符串相互转换（时间格式化YYYY-MM-DD，Dayjs的使用）

    JS日期与字符串相互转换（时间格式化YYYY-MM-DD，Dayjs的使用）

    文章内容 文章链接 JS数组对象—— 根据日期进行排序 ， 按照时间进行升序或降序排序 https://blog.csdn.net/XSL_HR/article/details/128579840?spm=1001.2014.3001.5501 JS日期时间格式化—— 数字日期转中文日期 （封装函数，dayjs转换时间格式） https://blog.csdn.net/XSL_HR/article/details/128607024?spm=100

    2024年01月18日
    浏览(77)

  • [SQL Server]SQL Server数据库中如何将时间日期类型(DateTime)转换成字符串类型(varchar,nvarchar)

    SQL Server数据库中，如何将时间日期类型(DateTime)的数据转换成字符串类型(varchar,nvarchar)，并对其进行 yyyy-mm-dd 形式的格式化输出 使用SQL Server的 CONVERT() 函数，如下： SELECT LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10) 或者 SELECT CONVERT(VARCHAR(10), GETDATE(), 120) 在SQL Server 2012及以上版本中，新增

    2024年02月07日
    浏览(70)

  • [22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]从字符串转换日期和/或时间时，转换失败。 (241)过滤非法日期格式数据

    出现这个错误是因为你在SQL Server中尝试将一个无效的字符串转换为日期或时间格式。为了解决这个问题，你需要过滤掉不合法的数据。 你可以使用TRY_CONVERT函数来尝试将字符串转换为日期或时间格式，如果转换失败，则返回NULL。然后你可以使用IS NOT NULL来过滤掉这些NULL值。

    2024年02月08日
    浏览(86)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包