Informix

 View Only
  • 1.  sql_begintxtime convertion

    Posted Fri July 15, 2022 10:15 AM
    Edited by Sergio Peres Fri July 15, 2022 10:26 AM
    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
    ------------------------------


  • 2.  RE: sql_begintxtime convertion

    InnerCircle
    Posted Fri July 15, 2022 04:06 PM
    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
    ------------------------------



  • 3.  RE: sql_begintxtime convertion

    Posted Sat July 16, 2022 10:02 AM
    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
    ------------------------------



  • 4.  RE: sql_begintxtime convertion

    InnerCircle
    Posted 29 days ago
    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
    ------------------------------