当前位置：文江博客话题详情

DB2 中时间戳与毫秒之间的转换

发布于 2024-12-08 14:31:00 字数 136 浏览 0 评论 0原文

我有一列数据类型时间戳。现在我需要将其转换为 MiiliSeconds 并放入另一列中。我该怎么办呢。输入的格式为 2011-10-04 13:54:50.455227，输出必须为 1317900719

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最终幸福 2024-12-15 14:31:01

有一个函数叫做timestampdiff。如果在 1970 年 1 月 1 日使用它，则可以正常工作，但该函数会给出近似结果。如果您想要准确性，您将需要使用类似的方法来计算正确答案

create function ts2millis(t timestamp)
returns bigint
return (
 ( 
  (bigint(year(t-1970))*bigint(31556926000))+
  (bigint(month(t))*bigint(2629743000))+
  (bigint(day(t))*bigint(86400000))+
  (bigint(hour(t))*bigint(3600000))+
  (bigint(minute(t))*bigint(60000))+
  (bigint(second(t))*bigint(1000))+
  (bigint(microsecond(t))/bigint(1000))
 )
)
@

There's a function called timestampdiff. Using it against January 1st 1970 would work otherwise but the function gives approximate results. If you want accuracy you will want to calculate the correct answer with something like

create function ts2millis(t timestamp)
returns bigint
return (
 ( 
  (bigint(year(t-1970))*bigint(31556926000))+
  (bigint(month(t))*bigint(2629743000))+
  (bigint(day(t))*bigint(86400000))+
  (bigint(hour(t))*bigint(3600000))+
  (bigint(minute(t))*bigint(60000))+
  (bigint(second(t))*bigint(1000))+
  (bigint(microsecond(t))/bigint(1000))
 )
)
@

回复收藏 0 原文

逆光飞翔i 2024-12-15 14:31:01

您请求的输出不是毫秒，而是相当于 CLib localtime()，具体操作方法如下：

SELECT
    86400*
    (
        DAYS(TIMESTAMP(v_timestamp))
        -
        DAYS(TIMESTAMP('1970-01-01-00:00:00'))
    )
    +
    MIDNIGHT_SECONDS(timestamp(v_timestamp))
FROM
    SYSIBM.SYSDUMMY1;

其中 v_timestamp 是要计算的变量或列。

Your requested output is not miliseconds, but the equivalent to CLib localtime(), here's how to do it:

SELECT
    86400*
    (
        DAYS(TIMESTAMP(v_timestamp))
        -
        DAYS(TIMESTAMP('1970-01-01-00:00:00'))
    )
    +
    MIDNIGHT_SECONDS(timestamp(v_timestamp))
FROM
    SYSIBM.SYSDUMMY1;

where v_timestamp is the variable or column to be calculated.

回复收藏 0 原文

~没有更多了~