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
------------------------------
Original Message:
Sent: Fri July 15, 2022 10:14 AM
From: Sergio Peres
Subject: sql_begintxtime convertion
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