CODEWITHSUNDEEP
sqloracle-database
Scuba_Steve
Scuba_Steve

Reputation: 91

difference between two timestamps (in days) in oracle

SELECT MIN (snap_id) AS FIRST_SNAP,
     MAX (snap_id) AS LAST_SNAP,
     MIN (BEGIN_INTERVAL_TIME) AS FIRST_QUERY,
     MAX (END_INTERVAL_TIME) AS LAST_QUERY,
     max(end_interval_time) - min(begin_interval_time) as "TIME_ELAPSED"
FROM dba_hist_snapshot
ORDER BY snap_id;

2931    3103    5/28/2012 6:00:11.065 AM    6/4/2012 11:00:40.967 AM    +07 05:00:29.902000

I would like the last columns output to be 7 (for the days). I have tried trunc and extract like some other posts mentioned but can't seem to get the syntax right. Any ideas?

Upvotes: 2

Views: 10848

Answers (1)

Andomar
Andomar

Reputation: 238048

Judging from your comment, you're using timestamp columns, not datetime. You could use extract to retrieve the hour difference, and then trunc(.../24) to get the whole number of days:

trunc(extract(hour from max(end_interval_time) - min(begin_interval_time))/24)

Or you could cast the timestamp to a date:

trunc(cast(max(end_interval_time) as date) -
    cast(min(begin_interval_time) as date))

Upvotes: 4

Related Questions