Monday, February 27, 2006
SQL - Compute duration between two TIMESTAMPs
Do you want to compute the time between two SQL TIMESTAMP objects? Try the following, replacing TS1 and TS2 with column containing the TIMESTAMP value.
(
EXTRACT (DAY FROM TS2-TS1) * 24 * 60 * 60
+ EXTRACT (HOUR FROM TS2-TS1) * 60 * 60
+ EXTRACT (MINUTE FROM TS2-TS1) * 60
+ EXTRACT (SECOND FROM TS2-TS1)
) AS SEC_DURATION
I'm not 100% sure that it's not specific to Oracle, but I think it should work with most databases.
(
EXTRACT (DAY FROM TS2-TS1) * 24 * 60 * 60
+ EXTRACT (HOUR FROM TS2-TS1) * 60 * 60
+ EXTRACT (MINUTE FROM TS2-TS1) * 60
+ EXTRACT (SECOND FROM TS2-TS1)
) AS SEC_DURATION
I'm not 100% sure that it's not specific to Oracle, but I think it should work with most databases.
Comments:
Post a Comment
