Catfish wrote:
The only issue there is how do you get the value from the system
command into the database server? There is no way to retrieve the
information directly in the SP, so you would have to insert it into a
table. Since the insertion would be done by a separate process, you
can't use a temp table - it has to be a permanent table. And then you
start running into a variety of concurrency issues. How do you ensure
you get the correct time?

Yes, it can be done that way. I don't think it is all that easy,
though. OTOH, it does stay firmly on the server, so it is probably
easier than what I was outlining.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler (AT) earthlink (DOT) net> wrote

It's not hard. Just how bad do you need this time.
You're correct about using a table to transfer the datetime.
Here's the code. However, embedding the proc in SQL is not for large reads.
For the large reads, store the datetime in a temp table.
================================================== ======
## /opt/informix/scripts/gmt_dtime.sh
#!/bin/sh
GMTdtime=`date -u "+%Y-%m-%d %H:%M:%S"`
dbaccess workdb - <<! > /dev/null 2>&1
set lock mode to wait;
update wk_gmtdtime set gmtdtime = "$GMTdtime";
!
================================================== ======
create table wk_gmtdtime (gmtdtime datetime year to second);

insert into wk_gmtdtime values (current);

create procedure sp_gmtdtime()
returning datetime year to second;
define v_gmtdtime datetime year to second;
system "/opt/informix/scripts/gmt_dtime.sh";
select gmtdtime into v_gmtdtime from wk_gmtdtime;
return v_gmtdtime;
end procedure;

select sp_gmtdtime() gmt from systables where tabid = 1;
================================================== ======

There is an undocumented way of getting the server's utc time in seconds
just like the time() system call 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 local time).

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("utc time %d\n", tim);
tm = gmtime((time_t *)&tim);
printf("%s\n", asctime(tm));
}


Michael



Jonathan Leffler wrote:

--

=== Michael Mueller ==================
Tel. + 49 8171 63600
Fax. + 49 8171 63615
Web: http://www.mm.kay-mueller.de
http://www.planets.kay-mueller.de
======================================

sending to informix-list

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 the time zone, you can get it this way.
But many operating systems have other means than TZ to set the time zone
system wide (but may also support TZ settings).

Michael

Michael Mueller wrote:

--

=== Michael Mueller ==================
Tel. + 49 8171 63600
Fax. + 49 8171 63615
Web: http://www.mm.kay-mueller.de
http://www.planets.kay-mueller.de
======================================

sending to informix-list

Michael Mueller wrote:


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 local time. The difference - an interval DAY(n) TO SECOND -
is the time zone 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!



--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/