如何在 Informix 12.1 中將 BIGINT 轉換為 DATETIME

我是 Informix 的新手，我正在嘗試將 bigint 轉換為日期時間。

SELECT sdatetime
FROM CallDetail;

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

這似乎是 1-1-1970 的毫秒數，但我無法正確轉換它的語法。我得到的最接近的是沒有時間的約會。我兩個都需要。

我怎樣才能做到這一點？

一種方法是使用以下過程：

{
#   "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
# (jleffler@us.ibm.com) 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

我懷疑現在可能有一種內置方法可以實現等效結果，但我已經忘記（或從未了解）它是什麼。

引用自：https://dba.stackexchange.com/questions/252337