Informix - Convert datetime to integer

Question

I have values stored as "datetime year to fraction (3)" in UTC which I'd like to offset to the timezone on my server. I'm trying to use the function DBINFO 'utc_to_datetime' to convert to local time, but the second argument is supposed to be an integer, and my values are in datetime. So, how would I convert a datetime to integer?

Answer 1

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.