LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: michaelslowey

Is there anyway of replicating the timeSubtract functionality over a date AND time timestamp?

I need to find out how many seconds between two times that straddle midnight...

When I extract the time and use timesubtract, I get negative seconds.

Thanks in advance

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: leekennett

Hi,

What I've done is pull the date and time from the database in as a string.
Parse the string to convert the string into a timestamp ex:
dtrecievedts = timestamp(date(substr('FOX.DTRECEIVED',0,10),"YYYY/MM/DD"),time(substr('FOX.DTRECEIVED',11),"H:M:S AM"))
dtsubmittedts = timestamp(date(substr('OD.DT Notification Submitted',0,10),"YYYY/MM/DD"),time(substr('OD.DT Notification Submitted',11),"H:M:S AM"))

Then the dateSubstract function will use the timestamps and you can set it to return seconds.
dateSubtract(dtsubmittedts,dtrecievedts,"seconds")

Not sure if there's a better way, but this definately worked for me.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: michaelslowey

Thanks for that Lee, worked perfectly!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: adjrock

thanks genius