Order by Time problem

[nico7799]

Hi,

Im have a Time field that im using to sort the records chronologically. Im storing the 12pm-24hr time as 00hr, since there isnt a real time of 24hr.
So, the problem is that when i Order the info by the time field the 00hr records appear at the start of the list instead that at the end (after the 11:59pm). I want them at the end because im using them as the End of the day, not the start of one.

Im ordering the field with this:

ORDER BY coalesce(time, 24)

What can i do to fix this?
Should i be storing the 12pm as 24hr? (i tried that, but the order by showed another value for the 24hr time)

Thanks!
Nico

[longneck]

what datatype is the time column? int?

[nico7799]

Datatype is: Time (HH:MM:SS).

[r937]

let me ask you: do you also consider january 1st as the end of december?

if not, then i suggest you "go with the flow" and consider 00:00:00 as the start of the new day

[nico7799]

I agree with you, 00:00:00 is the start of a new day, no questions about that. But there also moments when you refer to 00:00:00 hr as 24hr just to make it obvious that you are talking about the end of the day. Like in some bars that are open from 7 to 24 or something. We know that 24hr doesn't exist really and it's just another way of refering to 00:00.

Based on your comment i guess im not going to be able to do what i want. So maybe i should store the last hr of the day as 23:59 instead of 00:00 or 24:00 ?

[Dan Grossman]

Based on your comment i guess im not going to be able to do what i want. So maybe i should store the last hr of the day as 23:59 instead of 00:00 or 24:00 ?

Sure you can do what you want. Store it correctly (00:00 as the start of the day, and 23:59 as the end) and adjust your display when you pull it out. Nobody sees raw result sets when they use your app