Re: UTC, time zone support for datetimes
This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
Send mail to mime@docserver.cac.washington.edu for more info.
---559023410-758783491-918867284=:3031
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Fri, 12 Feb 1999, Andrew Pimlott wrote:
> In article <36C3C021.42D8@earthlink.net>, Jonathan Leffler wrote:
> >> Is there any way of asking the database for (the equivalent of)
> >> the current time in UTC? (It is essential to use the database's
> >> clock to avoid skew among applications using the database.)
> >
> >Not easily. You have to work some magic on the timezone to determine
> >the current offset to UTC, and then manually apply that correction.
>
> You don't mean you can get the offset from the database, do you? I assume
Oops; I said I'd attach and I didn't. Here it is...
Yours,
Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h>
Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn
---559023410-758783491-918867284=:3031
Content-Type: TEXT/PLAIN; charset=US-ASCII; name=x
Content-Transfer-Encoding: BASE64
Content-ID: <Pine.GSO.3.96.990212165444.3031N@osiris>
Content-Description: Notes on undocumented and unsupported features for UTC
Date: Thu, 07 Jan 1999 12:56:06 +0100
From: Anonymous1
To: Anonymous2
Subject: Re: CURRENT & Stored procedures
If you just need the time difference in seconds you can use
the undocumented parameter utc_current of dbinfo:
Hint: you have to run update statistics on the stored procedure
every time you want a new value
Here is an example:
create procedure gettime()
returning int, datetime year to fraction; DEFINE cas int;
DEFINE cur datetime year to fraction;
let cas = dbinfo('utc_current');
let cur = current;
return cas, cur;
end procedure;
create procedure ttt ()
returning int, datetime year to fraction; DEFINE k,j,i INTEGER;
DEFINE cas int;
DEFINE cur datetime year to fraction;
FOR i=1 TO 10
update statistics for procedure gettime; FOR k=1 TO 30000
let j = k * k;
END FOR;
CALL gettime() returning cas, cur;
RETURN cas, cur WITH RESUME;
END FOR;
END PROCEDURE;
execute procedure ttt();
(expression) (expression)
915709920 1999-01-07 12:51:58.161
915709923 1999-01-07 12:51:58.161
915709926 1999-01-07 12:51:58.161
915709930 1999-01-07 12:51:58.161
915709933 1999-01-07 12:51:58.161
915709936 1999-01-07 12:51:58.161
915709939 1999-01-07 12:51:58.161
915709942 1999-01-07 12:51:58.161
915709945 1999-01-07 12:51:58.161
915709950 1999-01-07 12:51:58.161
Anonymous2 wrote:
> I get this info:
>
> ==================
> SQL Syntax vol 2, CURRENT Function listed under Expression (P. 1-681,
> in the 7.2 Guide):
>
> The returned value comes from the system clock and is fixed when any SQL
> statement starts. For example, any calls to CURRENT from an EXECUTE
> PROCEDURE statement return the value when the stored procedure starts.
> ==================
>
> and I also found this solution
> (Anonymous3 recommend me that it is possible :-) )
>
> ====================== SQL script =======================
> CREATE DATABASE test;>
> CREATE TABLE tab (
> t DATETIME YEAR TO SECOND
> );>
> CREATE PROCEDURE t()
> RETURNING datetime year to second;> DEFINE cas datetime year to second;
> DELETE FROM tab WHERE 1=1;> system "... <full path> .../x";
> SELECT t INTO cas FROM tab;
> RETURN cas;
> END PROCEDURE;
>
> CREATE PROCEDURE p()
> RETURNING datetime year to second;> DEFINE i,j INTEGER;
> DEFINE cas datetime year to second;
>
> FOR i=1 TO 10
> LET cas=t();
> RETURN cas WITH RESUME;
> END FOR;
> END PROCEDURE
>
> ============= 'x' Shell Script ===========
> INFORMIXSERVER=......> export INFORMIXSERVER
> INFORMIXSQLHOSTS= ...... (if other than default)
> export INFORMIXSQLHOSTS
> INFORMIXDIR=...........> export INFORMIXDIR
> $INFORMIXDIR/bin/dbaccess test -<<EOF >/dev/null 2>&1
> INSERT INTO tab VALUES(current);> EOF
> =======================================================
>
> At 09:40 21.12.1998 -0600, you wrote:
> >where is this documented?
> >
> >Anonymous4
> >
> >At 11:49 AM 12/21/98 +0100, you wrote:
> >>there is documented and correct behaviour of CURRENT time in Storage
> >>procedure, where the time is set when SP is started and the current
> >>time is not changed during running of SP.
> >>
> >>Please, are there some undocumented setting or WA, how to get the real
> >>current time during SP running ?
Date: Thu, 7 Jan 1999 09:55:09 -0800 (PST)
From: Jonathan Leffler <jleffler@informix.com>
To: Anonymous1
Subject: Re: CURRENT & Stored procedures
On Thu, 7 Jan 1999, Anonymous1 wrote:
> If you just need the time difference in seconds you can use
> the undocumented parameter utc_current of dbinfo:
Intriguing. Obviously, you can convert the value of DBINFO('utc_current')
into a UTC datetime using:
DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + DBINFO('utc_current') UNITS SECOND
As far as I can see, when used in a SELECT statement, it gives the current
time. When used in a stored procedure (either directly or indirectly in
SELECT DBINFO('utc_current') ...), it gives you the time when the
statistics for the stored procedure were last updated. I suspect that for
the SELECT statement, it really gives the time when the SELECT statement
was prepared (or the query plan was created), rather than the time when the
fetch