TIMESTAMP doesn't like that. you should CAST the TIMESTAMP to DATE:
rather than
bla - blu (where bla and blu are TIMESTAMP)
do
CAST (bla as DATE) - CAST (blu as DATE)
and you will get a NUMBER (multiply it by 3600 * 24 and you will turn it into seconds)
BUT
you will lose the millisecond info
Here you have the definition of the TIMESTAMP
In alternative, you should rather EXTRACT each component of the DIFFERENCE (day, hours, minutes, seconds) and do the match with them:
as explained here
http://www.dbforums.com/oracle/1602071-difference-between-two-timestamps.html
CREATE OR REPLACE FUNCTION timestamp_diff ( start_time_in TIMESTAMP , end_time_in TIMESTAMP ) RETURN NUMBER AS l_days NUMBER; l_hours NUMBER; l_minutes NUMBER; l_seconds NUMBER; l_milliseconds NUMBER; BEGIN SELECT extract(DAY FROM end_time_in-start_time_in) , extract(HOUR FROM end_time_in-start_time_in) , extract(MINUTE FROM end_time_in-start_time_in) , extract(SECOND FROM end_time_in-start_time_in) INTO l_days, l_hours, l_minutes, l_seconds FROM dual; l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000; RETURN l_milliseconds; END;
No comments:
Post a Comment