RE: Time in seconds
I hate Word! How do you wrap lines with hard newlines at 75 columns on
Win95?
Talk about an uncivilized environment! Ugh!!!
Sorry about the long lines in this message...
----------
>Dennis Pimple wrote:
>Warning: a much-too-long explanation follows[...]
>
>Lee Wan Ling wrote:
>> I'm trying to get the current date in seconds format. Anyone
>> knows how I can do that?
>Seconds from what? The beginning of time? The start of the year? The start
of
>the day?
Important question. On Unix/POSIX, since 1970-01-01 00:00:00 UTC (aka
GMT).
>Assuming [...example omitted...]
>BUT ... trying this out on the engine at my immediate disposal (OnLine
>7.24.UC1) gives me a lot of syntax errors when trying to convert INTERVAL
with
>a lot of parentheses. [...]
>So, if you still need to know, let me know:
>1) What "second #0" is (anything before the start of the decade would be
too
>far in the past to deal with in INTEGER or INTERVAL sense)
>2) Language of choice (I may be able to get a stored procedure to work,
>although they are cumbersome). A 4GL or NewEra calculation is likely
possible,
>the results of which could be stored in the database.
At IWUC-97, I showed how to do this with a stored procedure similar to this
(untested code, rewritten from memory):
CREATE PROCEDURE unix_time(moment DATETIME YEAR TO SECOND DEFAULT CURRENT)
RETURNING INT; DEFINE v INTERVAL SECOND(9) TO SECOND;
DEFINE s CHAR(11);
LET v = moment - DATETIME(1970-01-01 00:00:00) YEAR TO SECOND;
LET s = v;
RETURN v;
END PROCEDURE;
The double assignment is necessary as there is no implicit conversion from
INTERVAL to number and you must take explicit control of the type of the
INTERVAL created by the difference. There is an implicit conversion from
string to INT. This runs into problems when the number of seconds since
1970 is larger than 1,000,000,000 (because then the INTERVAL SECOND(9) TO
SECOND type is not big enough to take the difference) and that happens on
8th September 2001 (or with times after that). I didn't get an opportunity
to point this out at IWUC-97. I have ESQL/C routines which deal with this
problem; it is much harder to manage in SPL. You'd probably need to
calculate something like (more untested code ahead!):
DEFINE v1 INTERVAL DAY(9) TO DAY
DEFINE v2 INTERVAL HOUR TO SECOND;
DEFINE a DECIMAL(16,0);
LET v1 = moment - DATETIME(1970-01-01) YEAR TO DAY;
LET v2 = moment - EXTEND(moment, YEAR TO DAY);
LET s = v1;
LET a = s * (24 * 60 * 60);
LET s = v2;
LET a = a + s;
RETURN a;
This is (ab)using the type-conversion facilities built into Informix's
dialect of SQL. Alternatively, you could use an INTERVAL DAY(9) TO SECOND
to receive the difference and then analyze and assemble the result from the
composite interval.
Also note that all the code above is prone to time-zone problems - there
isn't a way to disambiguate the 1970-01-01 and separate it from the local
time-zone. Life could be really tough if your PC in Saudi Arabia is
accessing a host computer in Malaysia which is connected to a database
server in Buenos Aires. However, you aren't any worse off with this code
than you were anyway!
Yours,
Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>
PS: I think I posted material like this before - sometime in the second
half of last year. Maybe even some ESQL/C. Look in DejaNews?