ORDER BY - datetime

[starfarm]

Hello experts

I am trying to a list of "events" from a field which contains datetime. I am ordering it by date, but I also want it to have a sorting-order that does NOT start from:00:00:00 to 23:59:59 but instead sorts the "events" from eg. 05:00:00 to 04:59:59. Can anybody help me?

 

[piti]

so you want to have day events between 00:00:00 to 05:00:00 placed after those in the interval 05:00:00 to 23:59:59?

 

[starfarm]

Yes! That is exactly what I want to do. I am displaying concerts for each day. The problem is that the concerts taking place after midnight is displayed first in my listing.

So you are absolutely right. Can you help me?

 

[piti]

so you just want to have concerts having the "start date" tomorow between 00:00 and 05:00 to be displayed in the today's listing?

 

[starfarm]

To help the users...I would rather show todays concerts between 00:00 and 05:00 after 23:59 the same day. If that is possible.

 

[r937]

To help the users...I would rather show todays concerts between 00:00 and 05:00 after 23:59 the same day. If that is possible.

speaking as a user, that would confuse the hell out of me

r937.com | rudy.ca

 

[piti]

so when selecting the concerts for the specific date use someting like this:

where date(date_sub(datetimefield, interval 5 hour)) = '2007-06-19' order by datetimefield

this way you will select everything between 2007-06-19 05:00:00 and 2007-06-20 04:59:59

 

[starfarm]

You might be right. Do you have a possible solution like the one you suggested yourself?

Like this:
so you just want to have concerts having the "start date" tomorow between 00:00 and 05:00 to be displayed in the today's listing?

 

[starfarm]

That sounds like a super solution! Let me just try it and I'll get back to it


thanks so far