Hi,

I am trying to use dbinfo to convert sql_begintxtime on syssqltrace and get wrong values.
I am using the same process used for DBINFO('utc_to_datetime',sql_finishtime).
I have done some research and found information about one procedure to do it, I have tested and get wrong values...

CREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
RETURNING DATETIME YEAR TO FRACTION(5);
DEFINE n DATETIME YEAR TO FRACTION(5);
DEFINE i1 INTEGER;
DEFINE i2 DECIMAL(11,5);
LET i1 = v / (24 * 60 * 60);
LET i2 = v - (i1 * 24 * 60 * 60);
LET n = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
LET n = n + i1 UNITS DAY;
LET n = n + i2 UNITS FRACTION(5);
RETURN n;
END PROCEDURE;

SELECT FIRST 10
from_unix_time(sql_begintxtime / 1000),
DBINFO('utc_to_datetime',sql_finishtime) AS sql_finishtime,

Return
(expression) 1970-01-05 16:43:30.66500
sql_finishtime 2022-07-15 14:35:51


Thanks for any help,

SP

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------
#Informix

Matter of fact: though this is documented as "Time this transaction started", that's not what it is and it not even is a real time, but rather reflecting the server internal 'stamp' that you'd also find e.g. on pages and that's evolving, in circular fashion, quite independently from time.

So trying to display this as a time, or datetime, cannot actually work.

The sql_finishtime is different and is Unix time in seconds, so dbinfo('utr_to_datetime', ...) is applicable here.

BR,
 Andreas

------------------------------
Andreas Legner
------------------------------

Thanks for the reply Andreas,

As I pretend to know the sql execution time, the only way to know the start time is to subtract the execution time from the sql_finishtime?

Best regards,

Sergio

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------

Original Message:
Sent: Fri July 15, 2022 04:06 PM
From: Andreas Legner
Subject: sql_begintxtime convertion

Matter of fact: though this is documented as "Time this transaction started", that's not what it is and it not even is a real time, but rather reflecting the server internal 'stamp' that you'd also find e.g. on pages and that's evolving, in circular fashion, quite independently from time.

So trying to display this as a time, or datetime, cannot actually work.

The sql_finishtime is different and is Unix time in seconds, so dbinfo('utr_to_datetime', ...) is applicable here.

BR,
 Andreas

------------------------------
Andreas Legner
------------------------------

Presumably, yes.

One more aspect: while 'sql_finishtime' probably is applicable to all kinds of SQL, incl. read-only queries, sql_begintxtime's name already would indicate 'transaction', so some sort of (potentially) modifying activity, only.

BR,
 Andreas

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Sat July 16, 2022 10:02 AM
From: Sergio Peres
Subject: sql_begintxtime convertion

Thanks for the reply Andreas,

As I pretend to know the sql execution time, the only way to know the start time is to subtract the execution time from the sql_finishtime?

Best regards,

Sergio

------------------------------
Sergio Peres
AIRC
Coimbra
------------------------------


Original Message:
Sent: Fri July 15, 2022 04:06 PM
From: Andreas Legner
Subject: sql_begintxtime convertion

Matter of fact: though this is documented as "Time this transaction started", that's not what it is and it not even is a real time, but rather reflecting the server internal 'stamp' that you'd also find e.g. on pages and that's evolving, in circular fashion, quite independently from time.

So trying to display this as a time, or datetime, cannot actually work.

The sql_finishtime is different and is Unix time in seconds, so dbinfo('utr_to_datetime', ...) is applicable here.

BR,
 Andreas

------------------------------
Andreas Legner