Menu
I am trying to write a query to run on Oracle database. The table ActionTable contains actionStartTime and actionEndTime columns. I need to find out which action took longer than 1 hour to complete.
actionStartTime and actionEndTime are of timestamp type
I have a query which gives me the time taken for each action:
select (actionEndTime - actionStartTime) actionDuration from ActionTable
What would be my where clause that would return only actions that took longer than 1 hour to finish?
689
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
Use the @DATEDIFF function to calculate the difference between two dates or datetimes, in days or seconds. The difference between the specified dates. Valid values can be: DD , which computes the difference in days.
TIMESTAMP is the same as DATE , except it has added fractional seconds precision. The biggest difference: DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds.
Subtracting two timestamps returns an interval. So you'd want something like
SELECT (actionEndTime - actionStartTime) actionDuration
FROM ActionTable
WHERE actionEndTime - actionStartTime > interval '1' hour
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
