PS Query & SQL

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!

Hey Mark,

  I am assuming you're working in Oracle SQL (since PS query would reject the AS keyword).  In that case, you could use an inline view like

SELECT    EXTRACT(DAY FROM DIFF)*86400 + EXTRACT(HOUR FROM DIFF)*3600 + EXTRACT(MINUTE FROM DIFF)*60 + EXTRACT(SECOND FROM DIFF) "TIME_IN_SEC"FROM(    SELECT        (A.ENDDTTM - A.BEGINDTTM) "DIFF"    FROM YOUR_RECORD A    WHERE       YOUR_WHERE_CLAUSE = TRUE)

In a query expression you could use the same EXTRACT logic, you'd just have to subtract the timestamps in each one.

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!

Hey Mark,

  I am assuming you're working in Oracle SQL (since PS query would reject the AS keyword).  In that case, you could use an inline view like

SELECT    EXTRACT(DAY FROM DIFF)*86400 + EXTRACT(HOUR FROM DIFF)*3600 + EXTRACT(MINUTE FROM DIFF)*60 + EXTRACT(SECOND FROM DIFF) "TIME_IN_SEC"FROM(    SELECT        (A.ENDDTTM - A.BEGINDTTM) "DIFF"    FROM YOUR_RECORD A    WHERE       YOUR_WHERE_CLAUSE = TRUE)

In a query expression you could use the same EXTRACT logic, you'd just have to subtract the timestamps in each one.

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!

Sorry, I am fully backward.  AS is allowed in Expressions but FROM isn't, so only the SQL part of my answer is relevant.

Hey Mark,

  I am assuming you're working in Oracle SQL (since PS query would reject the AS keyword).  In that case, you could use an inline view like

SELECT    EXTRACT(DAY FROM DIFF)*86400 + EXTRACT(HOUR FROM DIFF)*3600 + EXTRACT(MINUTE FROM DIFF)*60 + EXTRACT(SECOND FROM DIFF) "TIME_IN_SEC"FROM(    SELECT        (A.ENDDTTM - A.BEGINDTTM) "DIFF"    FROM YOUR_RECORD A    WHERE       YOUR_WHERE_CLAUSE = TRUE)

In a query expression you could use the same EXTRACT logic, you'd just have to subtract the timestamps in each one.

Hi folks!

I'm trying to determine elapsed time using the difference between two timestamps. This is the current expression I am using:

(TO_TIMESTAMP(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') -  TO_TIMESTAMP(CAST((A.BEGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) * 86400

It works most of the time, but does fail on occasion, especially if the difference is too large.

Was curious to know if anyone has anything better that works more reliably. Google has not been my friend, and AI has been equally unhelpful, so I figured I would consult RI (real intelligence).

Thanks in advance!