Convert Datetime to Unix Epoch in Informix

Convert DATETIME to Unix Epoch in Informix

Assuming that the mytime column is a DATETIME YEAR TO SECOND column (despite the formatting shown in the question), then the following stored procedure does the job. It has more comment than procedure, but the comments explain what it is doing.

{
#   "@(#)$Id: tounixtime.spl,v 1.6 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure TO_UNIX_TIME written by Jonathan Leffler (previously
# jleffler@informix.com and now jleffler@us.ibm.com).  Includes fix for
# bug reported by Tsutomu Ogiwara <Tsutomu.Ogiwara@ctc-g.co.jp> on
# 2001-07-13.  Previous version used DATETIME(0) SECOND TO SECOND
# instead of DATETIME(0:0:0) HOUR TO SECOND, and when the calculation
# extended the shorter constant to DATETIME HOUR TO SECOND, it added the
# current hour and minute fields, as documented in the Informix Guide to
# SQL: Syntax manual under EXTEND in the section on 'Expression'.
# Amended 2002-08-23 to handle 'eternity' and annotated more thoroughly.
# Amended 2002-09-25 to handle fractional seconds, as companion to the
# new stored procedure FROM_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.  Returning 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).  Both these values are unrepresentable in
# 32-bit integers, 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 to_unix_time(d DATETIME YEAR TO FRACTION(5)
                                DEFAULT CURRENT YEAR TO FRACTION(5))
            RETURNING DECIMAL(18,5);
    DEFINE n DECIMAL(18,5);
    DEFINE i1 INTERVAL DAY(9) TO DAY;
    DEFINE i2 INTERVAL SECOND(6) TO FRACTION(5);
    DEFINE s1 CHAR(15);
    DEFINE s2 CHAR(15);
    LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
    LET s1 = i1;
    LET i2 = EXTEND(d, HOUR TO FRACTION(5)) -
                DATETIME(00:00:00.00000) HOUR TO FRACTION(5);
    LET s2 = i2;
    LET n = s1 * (24 * 60 * 60) + s2;
    RETURN n;
END PROCEDURE;

Converting epoch to a readable date/time format in Informix

Use the internal dbinfo function. More about it, read the manual here

select dbinfo('utc_to_datetime', dateTimeOrigination) ,
  callingPartyNumber,finalCalledPartyNumber 
from tbl_billing_data 
where finalCalledPartyNumber like 'XXXX' 
  or callingpartynumber like 'XXXX' 
order by datetimeOrigination

convert date unix to timestamp

In Informix, you can use dbinfo() and 'utc_to_datetime':

select dbinfo('utc_to_datetime', myepoch)

Informix FROM_UNIXTIME alternative

If the timestamp column is of type DATETIME YEAR TO SECOND or similar, then you can convert it to a DECIMAL(18,5) number of seconds since the Unix Epoch, aka 1970-01-01 00:00:00Z (UTC; time zone offset +00:00) using a procedure such as this:

{
#   "@(#)$Id: tounixtime.spl,v 1.6 2002/09/25 18:10:48 jleffler Exp $"
#
# Stored procedure TO_UNIX_TIME written by Jonathan Leffler (previously
# jleffler@informix.com and now jleffler@us.ibm.com).  Includes fix for
# bug reported by Tsutomu Ogiwara <Tsutomu.Ogiwara@ctc-g.co.jp> on
# 2001-07-13.  Previous version used DATETIME(0) SECOND TO SECOND
# instead of DATETIME(0:0:0) HOUR TO SECOND, and when the calculation
# extended the shorter constant to DATETIME HOUR TO SECOND, it added the
# current hour and minute fields, as documented in the Informix Guide to
# SQL: Syntax manual under EXTEND in the section on 'Expression'.
# Amended 2002-08-23 to handle 'eternity' and annotated more thoroughly.
# Amended 2002-09-25 to handle fractional seconds, as companion to the
# new stored procedure FROM_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.  Returning 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).  Both these values are unrepresentable in
# 32-bit integers, 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 to_unix_time(d DATETIME YEAR TO FRACTION(5)
                                DEFAULT CURRENT YEAR TO FRACTION(5))
            RETURNING DECIMAL(18,5);
    DEFINE n DECIMAL(18,5);
    DEFINE i1 INTERVAL DAY(9) TO DAY;
    DEFINE i2 INTERVAL SECOND(6) TO FRACTION(5);
    DEFINE s1 CHAR(15);
    DEFINE s2 CHAR(15);
    LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
    LET s1 = i1;
    LET i2 = EXTEND(d, HOUR TO FRACTION(5)) -
                DATETIME(00:00:00.00000) HOUR TO FRACTION(5);
    LET s2 = i2;
    LET n = s1 * (24 * 60 * 60) + s2;
    RETURN n;
END PROCEDURE;

Some of the commentary about email addresses is no longer valid – things have changed in the decade and a half since I wrote this.

Informix - Convert datetime to integer

`DBINFO('utc_to_datetime', …)` isn't the correct tool

Using:

SELECT DBINFO('utc_to_datetime', 0)
  FROM sysmaster:sysdual

yields:

1970-01-01 00:00:00

(at least, it does when the server's time zone is set via TZ=UTC0). Changing that to:

SELECT DBINFO('utc_to_datetime', 1506107444)
  FROM sysmaster:sysdual

yields (with the same server running in UTC):

2017-09-22 19:10:44

Changing to the time zone on your server

There is some room to discuss the exact details, but basically what you need to do is add or subtract an INTERVAL representing the time zone to the DATETIME YEAR TO FRACTION(3) values.

For example:

DROP TABLE IF EXISTS datetime_values;

CREATE TABLE datetime_values
(
    original    DATETIME YEAR TO FRACTION(3) NOT NULL,
    modified    DATETIME YEAR TO FRACTION(3)
);

INSERT INTO datetime_values(original) VALUES('1970-01-01 00:00:00');
INSERT INTO datetime_values(original) VALUES('2017-09-22 12:31:46');
INSERT INTO datetime_values(original) VALUES('1066-10-21 14:20:04');
INSERT INTO datetime_values(original) VALUES('9989-01-20 00:00:00');

SELECT * FROM datetime_values;

UPDATE datetime_values
   SET modified = original - INTERVAL(-5:30) HOUR TO MINUTE;

SELECT * FROM datetime_values;

When run, that gives:

1970-01-01 00:00:00.000
2017-09-22 12:31:46.000
1066-10-14 14:20:04.000
9989-01-20 00:00:00.000
1970-01-01 00:00:00.000 1970-01-01 05:30:00.000
2017-09-22 12:31:46.000 2017-09-22 18:01:46.000
1066-10-14 14:20:04.000 1066-10-14 19:50:04.000
9989-01-20 00:00:00.000 9989-01-20 05:30:00.000

There are multiple ways to represent the time zone offset. What's best may depend on what format you currently have it available in. If there's a table with the value in a column as an INTERVAL HOUR TO MINUTE value, that's probably easiest — it could be a temporary table created for the job. You can handle strings, or two integers (for hours and minutes), or one integer for hours (won't handle time zones for India (UTC+5:30), Nepal (UTC+5:45) or Newfoundland (UTC-4:30).

There's the interesting question of is a time zone offset east of UTC positive or negative: ISO 9945 says positive west, negative east; ISO 8601 (and ISO 9075, SQL) says positive east, negative west. Follow ISO 8601 by choice, but be aware of the issue.

Winter vs Summer Time — Daylight Saving vs Standard Time

My problem with interval is that we are sometimes on CST and sometimes CDT, ie. -6 hours and -5 hours. The server automatically updates its time, so I'd like to use that to my benefit. Can I dynamically choose the interval somehow?

This gets very tricky, very quickly. In some areas of the world, the rules about switching between winter and summer time (I'm going to use that term, though it isn't particularly standard, but then daylight saving isn't standard worldwide except to the extent US software has bludgeoned people into accepting it) change most years, sometimes on a political whim, sometimes because of interactions between observational lunar calendars and events such as Ramadan. The 'Olson Time Zone Database' is hosted by IANA at https://www.iana.org/time-zones (aka https://www.iana.org/tz; this redirects to the longer name). This year, the current release of the database is still 2017b; however, in years past, there were releases such as 2005r, 2006n, 2007k, 2008h, 2009r, 2010n, 2011i, 2012i, 2013i, 2014g, 2015g, 2016j — they're not necessarily the last release in those years, but they are the latest release I have for each of those years. I believe there will be (at least) one more release this year, somewhere close to the end of October. One problem is that there often isn't very much notice of the change of time zone rules.

Ignoring the issue of changing definitions of the switch between winter and summer time in some parts of the world, you have the problem of determining the time zone offset that the server uses. There is a DBINFO('get_tz') call that returns the server's time zone string; it does not, however, tell you the offset from UTC. You could retrieve that string and apply it locally (to code running in a client), but that is indubitably messy.

SELECT DBINFO('utc_current'), DBINFO('get_tz'),
       CURRENT YEAR TO SECOND
  FROM sysmaster:sysdual

At one point, I ran that query and got:

1506114539   UTC0   2017-09-22 21:08:59

Using GNU date, I could analyze that:

$ /opt/gnu/bin/date -u -d @1506114539
Fri Sep 22 21:08:59 UTC 2017
$ /opt/gnu/bin/date    -d @1506114539
Fri Sep 22 14:08:59 PDT 2017
$

This shows that the number of seconds since 'The Epoch' was 1,506,114,539, which translates to 21:08:59 on 22nd September 2017 in the UTC (GMT, more or less) time zone. It was also 14:08:59 in the US/Pacific or America/Los_Angeles time zone, which has the abbreviation PDT (Pacific Daylight Time).

Determining that difference (7 hours during summer or daylight saving time) in the Informix server is tricky. Doubly so when dealing with times in the other time zone offset value, and ambiguously so when dealing with times in the hours between 01:00 and 02:00 on the morning when the clocks 'fall back' (there is no way to tell whether the time value was stored using the winter time zone offset or the summer time zone offset).

I'm going to need to meditate on this — and poke around previous answers, etc.

Is there a direct way to convert Unix Time on Informix to YYYY-MM-DD HH:MM:SS?

I think you can do it using dbinfo with 'utc_to_datetime' as a parameter: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_1484.htm

Extract TIME from DATETIME - informix

Ok, I managed to do this:

SELECT to_char(extend (my_datetime_column, hour to second),'%H:%M:%S') as my_time FROM my_table

Hope it will help someone!

DATETIME - DATETIME in HH:MM:SS format from Informix

The difference between two DATETIME YEAR TO SECOND values is an INTERVAL DAY(n) TO SECOND value by default (n = 8), and the leading 0 tells you that there are 0 days (plus the 5 hours, etc) in the difference between the times. It appears that you'd rather have an INTERVAL HOUR(9) TO SECOND result, so you need to ask for it:

SELECT CAST(DBINFO('utc_to_datetime', logout) -
            DBINFO('utc_to_datetime', login) AS INTERVAL HOUR(9) TO SECOND)
  FROM AnonymousTableWithColsLoginAndLogout

Test:

CREATE TABLE AnonymousTableWithColsLoginAndLogout
(
    login INTEGER NOT NULL,
    logout INTEGER NOT NULL
);
INSERT INTO AnonymousTableWithColsLoginAndLogout VALUES(1473961283, 1473961283 + (5 * 60 + 50) * 60 + 23);
SELECT CAST(DBINFO('utc_to_datetime', logout) -
            DBINFO('utc_to_datetime', login) AS INTERVAL HOUR(9) TO SECOND)
      FROM AnonymousTableWithColsLoginAndLogout
;

Result:

5:50:23

The key is knowing the data type returned from the difference, and knowing how to cast that to a type that you want.

Note that if there are 3 days, 5 hours, 50 minutes, 23 seconds difference, then the result will be shown as 77:50:23.

Incidentally, you could avoid using DBINFO by writing:

SELECT CAST((logout - login) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND)
  From AnonymousTableWithColsLoginAndLogout;

It produces the same answer.

Is there a way to have this format: HH:MM:SS. I mean, in the example you gave me, is there a way to add a zero before number 5?

Not sure, but probably. One issue is 'which language are you collecting the results in'? Or 'how are you converting the INTERVAL DAY(9) TO SECOND into a string'? It may affect the options. I was using my SQLCMD — a DB-Access work-a-bit-alike-but-better program. If you need to do it in a similar environment, rather than perhaps C or Java or C# or …, then you could write ghastligrams which check whether there's a single leading digit in the string and add a zero if not. There's also a trick:

SELECT EXTEND(DATETIME(2000-01-01 00:00:00) YEAR TO SECOND +
       CAST((logout - login) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND), HOUR TO SECOND)
  From AnonymousTableWithColsLoginAndLogout;

On the previous sample data, that produces the desired result:

05:50:23

The date specified in the DATETIME literal is arbitrary; the key is to use 'midnight'. The result is now a DATETIME HOUR TO SECOND instead of INTERVAL HOUR(9) TO SECOND or similar. This means that if you have an interval of 24 hours or more, you are not going to get a good representation of the data because it will produce an answer modulo 24 hours. The addition will succeed, but the information about the extra days will be thrown away. For intervals of less than 24 hours, it will be fine.

You might need to look up whether the TO_CHAR() function provides sufficient format control when formatting an INTERVAL (indeed, you'll need to check whether it accepts an interval type at all, and what it does if given one).

Convert Datetime to Unix Epoch in Informix