datetime to UTC

[Larry Kemmerling]

> Sorry for the late answer.

No problem. Thank you for responding.

> In my programs, I calculate the UTC by myself. This is not elegant but
> very faster.
> This functions are in Informix-4GL and can convert round about 500
> datetime-values per
> second.

I'll try what you have suggested, perhaps with a separate function
to determine the timezone offset. I agree that the SPL I posted is
slow. I should also mention that in testing my function I believe
I came upon a bug in 7.31. Informix tech support is trying to verify
this (case 881343). The problem I was seeing was that the value that
was returned via the CURRENT statement embedded in the SPL was distinct
for a given DATABASE statement only. So if I had something like:

DATABASE foo;

SELECT datetime2utc(CURRENT) UTC, DBINFO("utc_current") true_utc,
CURRENT
FROM systables
WHERE TabId = 99;
SELECT datetime2utc(CURRENT) UTC, DBINFO("utc_current") true_utc,
CURRENT
FROM systables
WHERE TabId = 99;
SELECT datetime2utc(CURRENT) UTC, DBINFO("utc_current") true_utc,
CURRENT
FROM systables
WHERE TabId = 99;
*
*
and so on

What I see is that the value of "UTC" is constant while "true_utc"
and "CURRENT" are both changing. If I place a DATABASE statement
between each SELECT then the value of "UTC" changes each time.

My understanding of the CURRENT function w.r.t. procedures was
that CURRENT was distinct for each call to a procedure. In the
SQL Guide, Vol. 2, pg 4-51 it talks about distinct values returned
by the CURRENT function stating that the value is fixed when the
sql statement starts. Since I'm only making a single call to
CURRENT in my procedure, I don't see why there's a problem.

If anyone has an explanation for this, I'd appreciate it if you
could enlighten me. I've only got 7.31 in my environment so I
haven't been able to test to see if this has existed in previous
releases.

TIA.

Larry Kemmerling
Product Systems Engineer
AT&T Wireless Services
Aviation Communications Division

> FUNCTION dt2utc(zeit)
> DEFINE jahr,monat,tag,stunde,minut,sekunde INTEGER,
> zeit DATETIME YEAR TO SECOND,
> wert INTEGER,
> mtag ARRAY[12] OF INTEGER,
> zeitchar CHAR(20)
> LET mtag[ 1] = 0 LET mtag[ 2] = 31 LET mtag[ 3] = 59 LET mtag[ 4] = 90
> LET mtag[ 5] =120 LET mtag[ 6] =151 LET mtag[ 7] =181 LET mtag[ 8] =212
> LET mtag[ 9] =243 LET mtag[10] =273 LET mtag[11] =304 LET mtag[12] =334
> # mtag is the count of day in a year for the first day in month
> LET jahr=YEAR(zeit) - 1970
> LET monat=MONTH(zeit)
> LET tag=DAY(zeit)
> LET zeitchar = zeit
> # I split the datetime in several parts, because this is faster.
> LET stunde= zeitchar[12,13] IF stunde IS NULL THEN LET stunde = 0 END IF
> LET minut=zeitchar[15,16] IF minut IS NULL THEN LET minut = 0 END IF
> LET sekunde=zeitchar[18,19] IF sekunde IS NULL THEN LET sekunde = 0 END
> IF
> # The hour, minute and second in string, because
> # I can use this function also for date-values
> LET wert = jahr * 365 + (jahr+2) / 4
> IF (jahr) mod 4 = 2 AND (monat = 1 OR (monat=2 AND tag<=29)) THEN
> LET wert=wert-1
> END IF
> LET wert = wert + mtag[monat] + tag - 1
> LET wert = wert * 24 + stunde
> LET wert = wert * 60 + minut
> LET wert = wert * 60 + sekunde
> RETURN wert
> END FUNCTION
>
>
> The same for the other direction
>
> FUNCTION utc2dt(zeit)
> DEFINE zeit,tage,stunden,minuten INTEGER,
> zeit2 DATETIME YEAR TO SECOND
>
> #LET zeit2 = DATETIME(70-01-01 00:00:00) + zeit UNITS SECOND #not used,
> because this is very slow
> LET minuten = zeit / 60
> LET zeit = zeit MOD 60
> LET stunden = minuten / 60
> LET minuten = minuten MOD 60
> LET tage = stunden / 24
> LET stunden = stunden MOD 24
> LET zeit2 = DATETIME(70-01-01 00:00:00) YEAR TO SECOND
> LET zeit2 = zeit2 + zeit UNITS SECOND
> LET zeit2 = zeit2 + minuten UNITS MINUTE
> LET zeit2 = zeit2 + stunden UNITS HOUR
> LET zeit2 = zeit2 + tage UNITS DAY
> RETURN zeit2
> END FUNCTION
>
>
> This functions calculate correct from 1970-01-01 00:00:00 to 2038-01-19
> 03:14:07
>
> I hope this helps you
>
> Bernd Fuchs