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).
Original Message:
Sent: Nov 24, 2022 04:13 AM
From: Sandro Bucher
Subject: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format
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
Original Message:
Sent: Nov 23, 2022 03:42 PM
From: Michael Lowry
Subject: Calculate difference between two timestamps in YYYY-MM-DD HH:MM:SS format
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.
Original Message:
Do the right thing. It will gratify some people and astonish the rest