sql_begintxtime convertion
Sergio Peres got wrong dates converting sql_begintxtime with a custom Unix-time procedure. Andreas Legner explained it is not a real time but an internal stamp, so it cannot be shown as a datetime, whereas sql_finishtime is Unix time. Sergio could subtract the execution time from the finish time to get the start; Andreas said presumably yes.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------
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 ------------------------------