Re: How does Oracle handle hour ending - hour starting times Need help asap

Originally posted by Hourman
> we get a file every hour 24x7of the loads for the previous hour.
> it comes
> with a timestamp. The files are supposed to be time ending
> format. That is
> the time is for the hour ending at that point.
>
> as we understand it a file with a time of hour ending indicates that
> the info
> in it is for the hour ending with the timestamp. A file with hour
> starting
> indicated it is for the hour starting with that time. So a fiel
> with hour
> ending 5 is for data from 4 am to 5 am. A file with hour starting
> 5 is for
> data for 5 am to 6 am. since we get data that has just occurred we
> will have
> hour ending
>
> for example at 1 am we get a file with a time of 01:00 that file is
> for hour
> ending 1
>
> time hour ending
> 01 1
> 02 2
> 13 13
> 22 22
> 23 23 (10:59 plus 1 sec)
>
> The problem is that at minute past midnight we get a file with hour
> ending 00.
> so it would be
>
> 22 22
> 23 23
> 24 00
>
> with hour from 11 pm to midnight being hour 24
>
> However, Oracle's hh24 hour format goes from 00-23. there is no
> hour 24 (as
> in DB2). Hour 00 is considered the 1st hour of the day not the
> last. But the
> file we get has hour ending 1 its for the hour from 00 to 01.
> Users need it
> in that format, they see hour 1 and know it means hour ending 1 that
> is for 00
> to 1 am.
>
> how do we get hour 24 into Oracle? we can't make it hour 00
> becuase that is
> wrong its the last hour not the first. And we can't reassign all
> the hours
> since users expect it a certain way. (as you can guess this is
> replacing a
> DB2 system with hours 1-24).
>
> any ideas?

If you want to see 24 rather than 00 for midnight, then either you will have to format the time yourself, perhaps in a view, or store it in a NUMBER or VARCHAR2 column, not a DATE.

You could format it yourself like this:
SELECT DECODE(TO_CHAR(timeval,'HH24'),'00','24',TO_CHAR(timeval,- 'HH24')) FROM ...

--
Posted via http://dbforums.com