CODEWITHSUNDEEP
oracle-databaseoracle11g
Gopichandar
Gopichandar

Reputation: 2832

How to find the time difference with respect to date in Oracle?

I want to find the time difference between the value in the column of type Date and the fixed time of that particular date.

Consider,

value in column - 4/16/2011 4:00:19 PM

Fixed time as - 3:00:00 PM

I am expecting the answer as 1hr 0min 19sec ago. Whatever the type I'm ok with it.

Thanks

Upvotes: 1

Views: 56

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

Since you don't care what data type is returned, I'd probably cast to a timestamp so that you can get an interval day to second returned.

SQL> select cast( sysdate as timestamp ) from dual
  2  ;

CAST(SYSDATEASTIMESTAMP)
---------------------------------------------------------------------------
15-MAR-15 04.05.46.000000 PM

SQL> ed
Wrote file afiedt.buf

  1  select cast( sysdate as timestamp ) -
  2            cast( trunc(sysdate) + interval '15' hour as timestamp )
  3*   from dual
  4  /

CAST(SYSDATEASTIMESTAMP)-CAST(TRUNC(SYSDATE)+INTERVAL'15'HOURASTIMESTAMP)
---------------------------------------------------------------------------
+000000000 01:06:18.000000

If you want to return a string rather than an interval, you can use extract to extract data from the interval

SQL> ed
Wrote file afiedt.buf

  1  select extract( hour from delta ) || ' hours, ' ||
  2         extract( minute from delta ) || ' minutes, ' ||
  3         extract( second from delta ) || ' seconds ago'
  4    from (
  5  select cast( sysdate as timestamp ) -
  6            cast( trunc(sysdate) + interval '15' hour as timestamp ) delta
  7    from dual
  8* )
SQL> /

EXTRACT(HOURFROMDELTA)||'HOURS,'||EXTRACT(MINUTEFROMDELTA)||'MINUTES,'||EXTRACT(
--------------------------------------------------------------------------------
1 hours, 10 minutes, 46 seconds ago

Upvotes: 2

Related Questions