> Gary Andrus <andrusg@aetna.com> asked:
> The gist of what I am trying to do is to timestamp several steps within
> an SP to see where we have bottlenecks in a long running process. I
> have read through this old thread on CDI and this had the best match
> for what I'm trying to do, with several good suggestions.
>
> I have tried using CURRENT, but as was indicated here, it's always the
> same value.
>
> I have tried using utc_current and utc_to_datetime, but am also getting
> the same value (though different than CURRENT).
>
> LET my_utc_time = (select dbinfo( 'utc_current') from systables
> where tabid = 99);
> LET my_utc_datetime = (select dbinfo( 'utc_to_datetime',
> my_utc_time) from systables where tabid = 99);
>
> I have tried using the shell script from this thread, gmt_dtime.sh, but
> receive a "not owner" error (though they are the same user id).
>
> I'm running IDS 9.40.UC7 on Solaris 5.8.
>
> Any other suggestions that you may have?

Dear Gary,

Try this - it seems to work for me. Of course, I had to reboot my IDS
server since I run it with TZ=UTC0 most of the time, but it seemed to be
producing the correct answer when I ran (on a Sun 10 with Solaris 8 and
TZ=US/Pacific) it with IDS 10.00.UC3:

-- @(#)$Id: utc_current.spl,v 1.1 2006/11/03 23:38:49 jleffler Exp $
-- @(#)JLSS SPL Library
-- @(#)Create UTC Stored Procedure - to return UTC time.

-- NB: the seconds component of local time and UTC is always the same in
-- the modern world. Historically, it was not always thus.

CREATE PROCEDURE utc_current() RETURNING DATETIME YEAR TO FRACTION(5);
DEFINE ut DECIMAL(12);
DEFINE dd INTERVAL DAY(9) TO DAY;
DEFINE hh INTERVAL HOUR TO HOUR;
DEFINE mm INTERVAL MINUTE TO MINUTE;
DEFINE ss INTERVAL SECOND TO FRACTION(5);
DEFINE rv DATETIME YEAR TO FRACTION(5);

LET ss = CURRENT SECOND TO FRACTION(5) - DATETIME(0.00000) SECOND TO
FRACTION(5);
LET ut = DBINFO('utc_current');
LET ut = TRUNC(ut / 60);
LET mm = MOD(ut, 60) UNITS MINUTE;
LET ut = TRUNC(ut / 60);
LET hh = MOD(ut, 24) UNITS HOUR;
LET ut = TRUNC(ut / 24);
LET dd = ut UNITS DAY;
LET rv = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
LET rv = rv + dd + hh + mm + ss;
RETURN rv;
END PROCEDURE;

Basically, this exploits the DBINFO('utc_current') to get the number of
seconds since 1970-01-01 00:00:00+00:00 (the Unix epoch). It also
exploits the fact that the seconds and fractional component of CURRENT is
the same in both UTC and local time - all (modern) time zones are a
multiple of 15 minutes off UTC. The value in ss calculates the number of
seconds past the start of the current minute. The 'ut' variable starts
out holding the number of seconds since the epoch; division by 60 gives us
the number of minutes since the epoch. That value modulo 60 is the number
of minutes in the current hour. We then divide ut by 60 - it holds the
number of hours since the epoch. That value modulo 24 is the number of
hours in the day since midnight. We divide ut by 24 to get the number of
days since the epoch. We then compute the epoch plus the correct number
of days, hours, minutes and seconds (including fractional seconds).

I'm not completely happy with this; there should be a variant lurking in
the code that takes a parameter that defaults to something like current
year to fraction(5). However, I don't have the time to work out the
generalization - maybe it takes a DECIMAL(17,5) representing a number of
seconds past the epoch which is to be presented as a UTC value (rather
than server local time value). That then does not need to use
DBINFO('utc_current') and has a modified operation to calculate ss
(possibly a TRUNC(ut, 5) operation if ut contains the reference time
value. It gets a bit messy about that point.


> On Aug 27 2003, 12:34 am, Jonathan Leffler wrote:
> > Michael Mueller wrote:
> > > Just for the sake of completeness:
> > > There also is a dbinfo( 'get_tz') which behaves a bit funny. If the
TZ
> > > environment variable in the client is not set it returns the
server's
> > > string value for TZ. If TZ is set in the client, it returns the
client's
> > > own value.
> > > If your server uses TZ to set thetimezone, you can get it this way.
> > > But many operating systems have other means than TZ to set the
timezone
> > > system wide (but may also support TZ settings). I wasn't aware of
> > > the get_tz option - I live, I learn. I'm not sure
> > > it is all that much use, but that's a separate discussion.
> >
> > The dbinfo('utc_current') information (which I'd forgotten about)
> > points the way to do it in the server, though. There's an SPL
> > function in the IIUG Software Archive with a name like
> > unixtime_to_datetime. It converts an integer number of seconds since
> > the Epoch (1970-01-01 00:00:00 +00:00) into a datetime year to second
> > - in UTC. You can also call CURRENT YEAR TO SECOND to get the
> > server's localtime. The difference - an interval DAY(n) TO SECOND -
> > is thetimezone the server is running in; that's the information we
> > wanted. About the only thing to watch, IIRC, is that the value of
> > dbinfo('utc_current') varies during the execution of a statement, but
> > the value of CURRENT does not. If your statement is long running,
> > that could skew the difference. If you want an INTERVAL HOUR TO
> > MINUTE, then you need to add the subtraction to 'INTERVAL(0:0) HOUR TO
> > MINUTE' - and worry about rounding. The zero interval must be on the
> > LHS of the addition. If you want a different interval (e.g. INTERVAL
> > MINUTES(4) TO MINUTE), change the constant.
> >
> > Thanks for the help, Michael!
> >
> >
> > > Michael Mueller wrote:
> > > > There is an undocumented way of getting the server's utc time in
seconds
> > > > just like th etime()systemcall on the server would return it:
> > > > select dbinfo( 'utc_current') from systables where tabid = 99
> > > > (There also is a dbinfo('utc_to_datetime', <some_utc_int>) to
convert it
> > > > to a string. But unfortunately it uses localtime).
> > > > If you want it in ascii and are willing to use esqlc, you could
do this:
> > > > #include <time.h>
> > > > main()
> > > > {
> > > > struct tm *tm;
> > > > exec sql begin declare section;
> > > > int tim;
> > > > exec sql end declare section;
> > > > exec sql whenever error stop;
> > > > exec sql database sysmaster;
> > > > exec sql select dbinfo( 'utc_current') into :tim
> > > > from systables where tabid = 99;
> > > > printf("utctime%d\n", tim);
> > > > tm = gmtime((time_t *)&tim);
> > > > printf("%s\n", asctime(tm));
> > > > }
> > > > Jonathan Leffler wrote:
> > > >> Pablo wrote:
> > > >>> I am running server under Linux.
> > > >>> Can I get the UTCtimein servers with SQL sentences ?
> > > >>> The 'current' and 'today' options always return localtime, this
> > > >>> server has several databases with differentstimezones and I
need to
> > > >>> obtain UTCtime, set the environment variable TZ=UTC+0 is not
possible
> > > >>> due to I'm not DBA.
> > > >> I've scratched my head on this, and I don't think it can be done
> > > >> trivially. If you have a programming language (eg I4GL) and you
know
> > > >> your owntimezone offset from UTC, then you can do it by
calculating:
> > > >> 1. In your program, find your local currenttime.
> > > >> 2. Given that and yourtimezone offset, calculate the current
UTC.
> > > >> 3. Get the server to tell you what it thinks thetimeis:
> > > >> SELECT CURRENT YEAR TO SECOND FROM SysTables WHERE Tabid =
1;
> > > >> 4. Use that and the current UTC to determine the server's
timezone.
> > > >> With that in place, you can now get the server to calculate the
UTC
> > > >> for you. Remember that the machines may not be synchronized
with NTP
> > > >> or SNTP, so allow for drifting clocks.
> > > >> It's simpler simply to know what the server's timezone is.
> > > >> If you only have DB-Access, then the only way to do it, I think,
is to
> > > >> know what the server'stimezone is -- or know what UTC is on your
> > > >> client-side. Actually, that can be done pretty simply; you can
> > > >> conflate steps 1 and 2 if yourtimezone is (temporarily) UTC; run
> > > >> your program with TZ=UTC0 in the environment. Beware 'spring
forward,
> > > >> fall back', as they say here in the USA.

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025-1013
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"