2

I'm new to Informix, and I'm trying to convert a bigint to a datetime.

SELECT sdatetime
FROM CallDetail;

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

This seems to be the number of ms from 1-1-1970 but I can't get the syntax right to convert this. The closest I got was getting a date with no time. I need both.

How can I do this?

2

One way to do it is with this procedure:

{
#   "@(#)$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;

You'd use:

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

Example output:

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

I suspect there may now be a built-in method to achieve an equivalent result, but I've forgotten (or never learned) what it is.

  • I can't find a built-in version but your function does just what I need. Thanks! – Leezard Oct 31 '19 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.