主页 / dba / 问题 / 252337
Accepted
Leezard
Asked: 2019-11-01 13:15:07 +0800 CST

如何在 Informix 12.1 中将 BIGINT 转换为 DATETIME

  • 772

我是 Informix 的新手，我正在尝试将 bigint 转换为日期时间。

SELECT sdatetime
FROM CallDetail;

Results:
sdatetime
----------
1572509662678
1572518550704
1572519033540

这似乎是 1-1-1970 的毫秒数，但我无法正确转换它的语法。我得到的最接近的是没有时间的约会。我两个都需要。

我怎样才能做到这一点？

sql-standard query

1 个回答

  1. Best Answer

    一种方法是使用以下过程：

    {
#   "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
# ([email protected]) as counterpart to TO_UNIX_TIME.
#
# If you run this procedure with no arguments (use the default), you
# need to worry about the time zone the database server is using because
# the value of CURRENT is determined by that, and you need to compensate
# for it if you are using a different time zone.
#
# Note that this version works for dates after 2001-09-09 when the
# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
# range of INTERVAL SECOND(9) TO SECOND.  Accepting DECIMAL(18,5) allows
# it to work for all valid datetime values including fractional seconds.
# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
# -62135596800 (11 digits).  The integer part of both these values is
# unrepresentable in a 32-bit integer, of course, so most Unix systems
# won't handle this range, and the so-called 'Proleptic Gregorian
# Calendar' used to calculate the dates ignores locale-dependent details
# such as the loss of days that occurred during the switch between the
# Julian and Gregorian calendar, but those are minutiae that most people
# can ignore most of the time.
}

CREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
            RETURNING DATETIME YEAR TO FRACTION(5);
    DEFINE n DATETIME YEAR TO FRACTION(5);
    DEFINE i1 INTEGER;
    DEFINE i2 DECIMAL(11,5);
    LET i1 = v / (24 * 60 * 60);
    LET i2 = v - (i1 * 24 * 60 * 60);
    LET n  = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
    LET n  = n + i1 UNITS DAY;
    LET n  = n + i2 UNITS FRACTION(5);
    RETURN n;
END PROCEDURE;

    你会使用：

    SELECT sdatetime, from_unix_time(sdatetime / 1000)
  FROM CallDetail;

    示例输出：

    1572509662678    2019-10-31 08:14:22.67800
1572518550704    2019-10-31 10:42:30.70400
1572519033540    2019-10-31 10:50:33.54000

    我怀疑现在可能有一种内置方法可以实现等效结果，但我已经忘记（或从未了解）它是什么。

    • 2

相关问题