Re: [Fwd: Re: Access to UTC time with servers running in local time - a server-only answer!]

> 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 timevalue. 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 > > > >>> obtai