Automic Workload Automation

 View Only
  • 1.  Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 23, 2022 11:33 AM
    I'd like to calculate the difference between two timestamps in YYYY-MM-DD HH:MM:SS format - the sort returned by GET_STATISTIC_DETAIL.

    DIFF_DATE will calculate the difference between two dates, and DIFF_TIME will do the same for times. Is there a function to do this for timestamps that combine a date and time?


  • 2.  RE: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 23, 2022 03:43 PM
    Edited by Michael A. Lowry Nov 24, 2022 03:05 AM
    Obviously the AE server must calculate differences between timestamps all the time. There's surely an internal that's used for this purpose but I wasn't able to find anything that's documented.

    I tried to find a way to accomplish what I want using DIFF_DATE and DIFF_TIME, but ran into a problem almost immediately. Both functions always subtract the smaller value from the larger one, effectively returning an absolute value of the difference and offering no clue of which of the provided arguments was earlier and which was later.



  • 3.  RE: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 24, 2022 04:13 AM
    Hi Michael

    There isn't anything (I am aware of). For my purpose of comparing dates, I always used this format:

    YYYYMMDDHHMMSS

    Regards

    Sandro

    ------------------------------
    Cloud Engineer
    Switzerland
    ------------------------------



  • 4.  RE: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 24, 2022 09:07 AM
    Thanks for the tip. Indeed the YYYYMMDDHHMMSS format makes it possible to perform comparisons as though the timestamps were integers. This was the missing piece that enabled proper date calculations.
    ! Subtract TIMESTAMP2 from TIMESTAMP1.
    ! If TIMESTAMP2 is later than TIMESTAMP1, the result will be negative.
    !:SET &TIMESTAMP1# = "&TIMESTAMP1#"
    !:SET &TIMESTAMP2# = "&TIMESTAMP2#"
    :SET &TIMESTAMP2# = "2022-11-20 22:00:00"
    :SET &TIMESTAMP1# = "2022-11-23 01:23:45"
    :SET &DATE_FORMAT# = "YYYY-MM-DD"
    :SET &TIME_FORMAT# = "HH:MM:SS"
    :DEFINE &TIMESTAMP_DIFF_INT#, signed
    :DEFINE &TIME_DIFF_INT#, signed
    :DEFINE &TIMESTAMP_DIFF_SEC#, signed
    :SET &DATE1# = STR_CUT(&TIMESTAMP1#,1,10)
    :SET &DATE2# = STR_CUT(&TIMESTAMP2#,1,10)
    :SET &TIME1# = STR_CUT(&TIMESTAMP1#,12,8)
    :SET &TIME2# = STR_CUT(&TIMESTAMP2#,12,8)
    :PRINT "   Date       Time"
    :PRINT "1. &DATE1# &TIME1# -"
    :PRINT "2. &DATE2# &TIME2#"
    
    :SET &DATE1_NUM# = CONV_DATE("&DATE_FORMAT#:&DATE1#","YYYYMMDD")
    :SET &DATE2_NUM# = CONV_DATE("&DATE_FORMAT#:&DATE2#","YYYYMMDD")
    :SET &TIME1_NUM# = DIFF_TIME("&TIME_FORMAT#;&TIME1#","&TIME_FORMAT#;00:00:00","HHMMSS")
    :SET &TIME2_NUM# = DIFF_TIME("&TIME_FORMAT#;&TIME2#","&TIME_FORMAT#;00:00:00","HHMMSS")
    :SET &TIMESTAMP1_INT# = CONVERT(signed,"&DATE1_NUM#&TIME1_NUM#")
    :SET &TIMESTAMP2_INT# = CONVERT(signed,"&DATE2_NUM#&TIME2_NUM#")
    !:PRINT "TIMESTAMP1_INT: &TIMESTAMP1_INT#"
    !:PRINT "TIMESTAMP2_INT: &TIMESTAMP2_INT#"
    
    :SET &TIMESTAMP_DIFF_INT# = &TIMESTAMP1_INT# - &TIMESTAMP2_INT#
    :IF &TIMESTAMP_DIFF_INT# < 0
    :  SET &TIMESTAMP2_LATER# = "TRUE"
    !:  PRINT "TIMESTAMP2 is after TIMESTAMP1."
    :ELSE
    :  SET &TIMESTAMP2_LATER# = "FALSE"
    !:  PRINT "TIMESTAMP2 is before TIMESTAMP1."
    :ENDIF
    
    :SET &TIME1_INT# = CONVERT(signed,"&TIME1_NUM#")
    :SET &TIME2_INT# = CONVERT(signed,"&TIME2_NUM#")
    !:PRINT "TIME1_INT: &TIME1_INT#"
    !:PRINT "TIME2_INT: &TIME2_INT#"
    
    :SET &TIME_DIFF_INT# = &TIME1_INT# - &TIME2_INT#
    :IF &TIME_DIFF_INT# < 0
    :  SET &TIME2_LATER# = "TRUE"
    !:  PRINT "TIME2 occurs later in the day than TIME1."
    :ELSE
    :  SET &TIME2_LATER# = "FALSE"
    !:  PRINT "TIME2 occurs earlier in the day than TIME1."
    :ENDIF
    
    :SET &ADJUSTMENT_NEEDED# = "FALSE"
    :IF &TIMESTAMP2_LATER# = "TRUE"
    :  IF &TIME2_LATER# = "FALSE"
    :    SET &ADJUSTMENT_NEEDED# = "TRUE"
    :  ENDIF
    :ELSE
    :  IF &TIME2_LATER# = "TRUE"
    :    SET &ADJUSTMENT_NEEDED# = "TRUE"
    :  ENDIF
    :ENDIF
    
    :SET &DATE_DIFF_DAYS# = DIFF_DATE("&DATE_FORMAT#:&DATE2#", "&DATE_FORMAT#;&DATE1#")
    !:PRINT "Initial uncorrected DATE_DIFF: &DATE_DIFF_DAYS#"
    :SET &TIME_DIFF# = DIFF_TIME("HHMMSS;&TIME2_NUM#", "HHMMSS;&TIME1_NUM#", "HHMMSS")
    !:PRINT "Initial uncorrected TIME_DIFF: &TIME_DIFF#"
    :IF &ADJUSTMENT_NEEDED# = "TRUE"
    !:  PRINT "Subtracting TIME_DIFF from 24:00:00."
    :  SET &TIME_DIFF# = SUB_TIME("HHMMSS;235959", "HHMMSS;&TIME_DIFF#", "HHMMSS")
    :  SET &TIME_DIFF# = ADD_TIME("HHMMSS;000001", "HHMMSS;&TIME_DIFF#", "HHMMSS")
    !:  PRINT "Subtracting one day from DATE_DIFF."
    :  SET &DATE_DIFF_DAYS# = &DATE_DIFF_DAYS# - 1
    :ENDIF
    
    :SET &DATE_DIFF_DAYS# = FORMAT(&DATE_DIFF_DAYS#)
    
    :SET &TIME_DIFF_HHMMSS# = DIFF_TIME("HHMMSS;&TIME_DIFF#","&TIME_FORMAT#;00:00:00","&TIME_FORMAT#")
    :SET &TIME_DIFF_SECONDS# = DIFF_TIME("HHMMSS;&TIME_DIFF#","&TIME_FORMAT#;00:00:00","SS")
    :SET &TIME_DIFF_SECONDS# = FORMAT(&TIME_DIFF_SECONDS#)
    :SET &TIMESTAMP_DIFF_SEC# = &DATE_DIFF_DAYS# * 60 * 60 * 24 + &TIME_DIFF_SECONDS#
    :IF &TIMESTAMP2_LATER# = "TRUE"
    :  SET &TIMESTAMP_DIFF_SEC# = 0 - &TIMESTAMP_DIFF_SEC#
    :  SET &TIMESTAMP_DIFF_DDHHMMSS# = "- &DATE_DIFF_DAYS#d &TIME_DIFF_HHMMSS#"
    :ELSE
    :  SET &TIMESTAMP_DIFF_DDHHMMSS# = "+ &DATE_DIFF_DAYS#d &TIME_DIFF_HHMMSS#"
    :ENDIF
    
    !:PRINT "Date difference (days)     : &DATE_DIFF_DAYS#"
    !:PRINT "Time difference (HH:MM:SS) : &TIME_DIFF_HHMMSS#"
    :PRINT "Timestamp diff. (days + HH:MM:SS) : &TIMESTAMP_DIFF_DDHHMMSS#"
    :PRINT "Timestamp difference (seconds)    : &TIMESTAMP_DIFF_SEC#"

    This is probably much more complicated that it needs to be, but it works.

    During the course I writing this, I learned some quirks of the AE scripting functions for working with Dates & Times.

    Similarly named functions do not always work in similar ways.  For example, CONV_DATE converts from one date format to another, but CONV_TIMESTAMP converts from one time zone to another.

    There is no CONV_TIME function for converting from one time format to another, but DIFF_TIME can be used for this purpose. (Just subtract the time value 00:00:00.)

    To use arithmetic functions that might return negative results, it's necessary to declare the output variable first as a signed integer, using the :DEFINE scripting statement.

    There appears to be no function for converting a timestamp to something more directly usable such as epoch time (seconds since 1 January 1970).


  • 5.  RE: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 24, 2022 04:22 AM
    or you can use SQLI and Oracle date functions...

    ------------------------------
    Cloud Engineer
    Switzerland
    ------------------------------



  • 6.  RE: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format

    Posted Nov 24, 2022 08:19 AM
    Without better date & time functions, the AE makes it quite cumbersome. It's straightforward in Oracle.
    SELECT
    (CAST(TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS') AS DATE) -
     CAST(TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS') AS DATE)) * 86400
    AS "Timestamp difference in seconds"
    FROM DUAL;

    Thanks for the suggestion, @Sandro Bucher.