Re: Question about datetime math
Eric Wimer wrote:
> I am having trouble generating the number of seconds (since the epoch)
> from a datetime variable within a stored procedure.
>
> I found a post describing how to generate a datetime from the number
> of seconds courtesy of jleffler:
>
> > SELECT DATETIME(1970-01-01 00:00:00) YEAR TO SECOND +
> > unixtimecol UNITS SECOND FROM WhichEverTable;
>
> However, I can't seem to figure out how to reverse this. Any
> suggestions?
You really did need to be at the IWUC 97 conference where I covered
this too. It is harder; much harder. The way to do it involves using
a stored procedure and some trickery:
-- Untested code!
CREATE PROCEDURE dt2unix(dt DATETIME YEAR TO SECOND) RETURNING INT; DEFINE iv INTERVAL SECOND(9) TO SECOND;
DEFINE st CHAR(10);
LET iv = dt - DATETIME(1970-01-01 00:00:00) YEAR TO SECOND;
LET st = iv;
RETURN st;
END PROCEDURE;
SELECT dt2unix(SomeTable.SomeColumn) ...
I haven't put this code past a database, so there's probably one or
more typos in it.
The key point is controlling the type of interval calculated from the
subtraction. The conversion to string gets around the restrictions on
assigning intervals to numeric types; returning the string exploits
the conversion abilities of Informix.
Beware: this code fails once the date being converted is larger than
999,999,999 seconds from 1970-01-01 00:00:00, which occurs early in the
years 2000+ (2001 or 2002, I think). To handle that properly is more
complex. You are probably best off calculating the number of days,
multiplying by the number of seconds in the day, and then adding the
residue:
-- More untested code!
CREATE PROCEDURE dt2unix_2(dt DATETIME YEAR TO SECOND) RETURNINGDECIMAL(12);
DEFINE i1 INTERVAL DAY(9) TO DAY;
DEFINE i2 INTERVAL SECOND(5) TO SECOND;
DEFINE d1 DATETIME YEAR TO DAY;
DEFINE d2 DATETIME HOUR TO SECOND;
DEFINE st CHAR(13);
DEFINE dec1 DECIMAL(12);
LET d1 = dt;
LET i1 = d1 - DATETIME(1970-01-01) YEAR TO DAY;
LET st = i1;
LET dec1 = st * 24 * 60 * 60;
LET i2 = dt - d1;
LET st = i2;
LET dec1 = dec1 + st;
RETURN dec1;
END PROCEDURE;
I think this is proof against overflow for all valid datetimes
(0001-01-01 .. 9999-12-31), but I haven't verified it at all, let alone
on dates long before 1970. I could also believe that the calculations
with arithmetic on a string variable might fail (to compile), in which
case, I'd do a simple conversion to a decimal (which would work),
followed by some arithmetic.
Yours,
Jonathan Leffler (j.leffler@acm.org) #include <many-aliases.h>