Re: date/time arithmetic

> Ellen Storz <ellen_at_.pacific.net> writes:
> Is it possible to do date arithmetic with date hours and
> minutes and express the results in hours and minutes?
>
> I need to find the number of hours and minutes between
> two times.
>
> I can do this, but the result is expressed as a decimal:
> select (to_date('01-jan-95 12:20', 'dd-mon-yy hh24:mi'))
> -(to_date('01-jan-95 12:00', 'dd-mon-yy hh24:mi'))
> from dual;

The decimal you receive is expressed in DAYS. It is a simple matter to convert this to whatever you want, using multiplication. For example, multiply it by 24 to get hours, or by 1440 to get minutes, or 86400 to get seconds. What could be simpler?

> I can do this to get the number of minutes
> select (to_number(to_char(stop_time, 'hh24'))
> - to_number(to_char(start_time, 'hh24')))
> * 60
> + to_number(to_char(trunc(stop_time, 'mi'),'mi'))
> - to_number(to_char(trunc(start_time,'mi'),'mi'))
> from <table_name>;
> and then reformat the result myself to print as hours and minutes,
> but I'm hoping there is an easier way.

Your example will not work across a midnight boundary, so I would not recommend it, even if it weren't overcomplicated.

>>>>
Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Sun Jun 25 1995 - 00:00:00 CEST