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

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? Received on Tue Jun 17 2003 - 06:21:10 CDT