0

I want to get the different between two TIMPSTAMP in a function.

Input function: n_LECT_DATE_TIME_START and n_LECT_DATE_TIME_END.

Output function: (n_LECT_DATE_TIME_END - n_LECT_DATE_TIME_START) in hours. (number?)

This code not work... I got 2 errors:

Error(5,5): PL/SQL: Statement ignored and

Error(5,13): PLS-00382: expression is of wrong type.

FUNCTION   check_time(n_LECT_DATE_TIME_START AF_LECTURE.LECT_DATE_TIME_START%TYPE,
                      n_LECT_DATE_TIME_END AF_LECTURE.LECT_DATE_TIME_END%TYPE)
     RETURN number IS 
    BEGIN
    RETURN  n_LECT_DATE_TIME_END -  n_LECT_DATE_TIME_START;
END check_time;

AF_LECTURE.LECT_DATE_TIME_END and AF_LECTURE.LECT_DATE_TIME_START type is TIMESTAMP.

user3868442
  • 117
  • 1
  • 4
  • 12
  • Difference of two timestamps is INTERVAL DAY TO SECOND, not NUMBER. Check [this answer](http://stackoverflow.com/a/11618722/2890093). – Kombajn zbożowy Jul 26 '14 at 19:35
  • @Kombajn zbożowy thanks. It's work. but how I call this types in JAVA? I tried "Types.TIMESTAMP" but I got error. – user3868442 Jul 26 '14 at 20:55

1 Answers1

2

You can extract days, multiply that by 24, and then extract hours and add that # to the (days*24) to get the number of hours between 2 timestamps.

This is an illustration of the # of hours between 7/22/14 at midnight and right now:

http://sqlfiddle.com/#!4/d41d8/33167/0

select extract(day from timediff) * 24 + extract(hour from timediff) as hours_diff
  from (select systimestamp - to_timestamp('2014-07-22', 'yyyy-mm-dd') as timediff
          from dual)
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33