Re: Time arithmetic

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 22 Jul 1999 15:57:46 GMT
Message-ID: <7n7f1q$hj$1@nntp.csufresno.edu>

In article <3789ABC2.DEE7D85F_at_bitoek.uni-bayreuth.de>, Helmut Hahn <helmut.hahn_at_bitoek.uni-bayreuth.de> wrote:
>I have a timeseries with a DATE column. I must group all values of a day
>from 7:10 P.M to 7:00 P.M. the next day.
>
>This means I nedd a query like
>
>select to_char(mw_von - to_date('7:10','HH24:MI'),'YYYY.DDD')
>"Day",sum(mw_wert) "Value"
>from messwerte
>where ...
>group by to_char(mw_von - to_date('7:10','HH24:MI'),'YYYY.DDD');
>
>but I am confused on time arithmetic. I know operations like sysdate +1
>work fine but what to do if I need add some hours?

I am not sure of the question: Your example uses 7:10 with 24-hour time, but your question uses 7:10 pm, which would be 19:10 in 24-hour time.

You can use (total_minutes / 1440) in date arithmetic. Assuming you want 7:10 AM, this would be 430/1440 (or 0.29861). Your 'group by' would be something like:
group by to_char(trunc(mw_von - .29861),'YYYY.DDD')

There is an Elapsed Time calculator in the programming tips page on my web site where you can see an example of hours/minutes/seconds date arithmetic: http://members.aol.com/stevec5088

Steve Cosner Received on Thu Jul 22 1999 - 10:57:46 CDT