Re: Using SQL to check time intervals
Date: 1996/04/16
Message-ID: <4l0r8r$bri_at_inet-nntp-gw-1.us.oracle.com>#1/1
hays_at_magicnet.net (Jeff Hays) wrote:
>Hi all,
>I am attempting to do "COUNT(*)"s on time intervals from a DATE field.
>The ROUND and TRUNC functions seem to round to something that is part of
>the format models, but I need something a little more complicated, like
>every 10 minutes or every 15 minutes. I have used substrings to do 10
>minute ranges (by triming off the last digit of the minute), but thats
>pretty non-elegant.
I wouldn't call this 'more' elagant but it is a little more flexible:
trunc(:d) + (15*60)*trunc(to_char(:d,'sssss')/(15*60))/86400
Replace 15*60 (15 minutes at 60 seconds per minute) with any other value to change the increment (eg: 30*60 would give you half hours)
>I also tried a PL/SQL procedure that did the "flooring to the interval",
>but I can't use it in the GROUP BY function.
>Thanks
>Jeff Hays
>hays_at_magicnet.net
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Tue Apr 16 1996 - 00:00:00 CEST