rails order by time with reversed 24 hour periods

Question

I have a model which stores events which each have a start time.

The start time is handled by rails as Time, but stored in Postgres as datetime (I'm assuming) and rails just ignores the date and stores it as 2000-01-01...

The problem I have is ordering it so that events that start after midnight show up afterwards and not before. How can I sort them differently and split events in the 24 hour period so that the first morning 12 hour events show up after the second half of evening events. (if that makes since)

Any ideas?

c=Event.last
c.event_items.order(:time_start)

which does ORDER BY \"event_items\".time_start ASC"

+-----+----------+-----------+-------------------------+---------+-------------------------+-------------------------+
| id  | event_id | artist_id | time_start              | area_id | created_at              | updated_at              |
+-----+----------+-----------+-------------------------+---------+-------------------------+-------------------------+
| 155 | 63       | 111       | 2000-01-01 00:40:00 UTC |         | 2013-08-24 21:21:57 UTC | 2013-08-26 00:07:44 UTC |
| 153 | 63       | 133       | 2000-01-01 01:10:00 UTC |         | 2013-08-24 21:21:57 UTC | 2013-08-26 00:07:44 UTC |
| 152 | 63       | 128       | 2000-01-01 02:00:00 UTC |         | 2013-08-24 21:21:57 UTC | 2013-08-26 00:07:44 UTC |
| 151 | 63       | 148       | 2000-01-01 22:10:00 UTC |         | 2013-08-24 21:21:57 UTC | 2013-08-26 00:07:44 UTC |
| 194 | 63       | 124       | 2000-01-01 23:00:00 UTC |         | 2013-08-26 00:07:44 UTC | 2013-08-26 00:07:44 UTC |
| 154 | 63       | 98        | 2000-01-01 23:50:00 UTC |         | 2013-08-24 21:21:57 UTC | 2013-08-26 00:07:44 UTC |
+-----+----------+-----------+-------------------------+---------+-------------------------+-------------------------+

I would like the dates to from before 12h (24h clock) to come after those 12h+...

eg in this example I would like the order to be 22:10, 23:00, 23:50, 00:40, 01:10, 02:00

Answer 1

There is a date_part function in postgres so I think you could do something like this

Event.order("CAST(date_part('hour', time_start) AS Integer)/12 DESC,
             CAST(date_part('hour', time_start) AS Integer)%12 ASC")

If you want to divide your time in different modular then try with different divider.

UPDATE

I guess I need to elaborate a bit.

Basically I extract the hour part of time_start timestamp and convert (or cast) it to integer as I want to work with integer not time. If we simply divide hour by 12 it will give us an interval, have a look on the link to postgres doc above for more details.

So the first expression CAST(date_part('hour', time_start) AS Integer)/12 give us either 0 or 1; 0 if hour is before 12 and 1 for 12 onwards. This does what you want, put hours after 12 on top of hours after 12. They will be sorted from 23 > 12, then 11 > 0 though, so not exactly what you want.

Hence the second CAST(date_part('hour', time_start) AS Integer)%12. This will give use 23 => 11, 22 => 10 ... 12 => 0 and 11 => 11 ... 0 => 0 etc. So we can sort them in ascending order.

By the way, this doesn't sort minutes so you may want to add time_start ASC as a third sort criteria. I think you could also add CAST(date_part('hour', time_start) AS Integer) to SELECT statement and name it then only refer to that name on ORDER BY.

Hope this help :)

Answer 2

I guess you can do it like that

c = Event.where(:time_start > 12.hours).order(:time_start) << Event.where(:time_start < 12.hours).order(:time_start)

this will concatenate the two results one after the other and give you the desired result.