Rails - hourly order

Question

@events = Event.all(:order => "date DESC") 

to sort my events by date, datetime column.

Is it possible to order them not by date, but only by the hour? (I sued sqlite3 database)

Thanks!

Answer 1

For SQLite,

 @events = Event.all(:order => "time(date) DESC") 

Use this with caution because it ignores the date. (And see below “Moments later.”)

 CREATE TABLE test (date datetime primary key); INSERT INTO "test" VALUES('2011-01-01 08:00:00'); INSERT INTO "test" VALUES('2011-01-01 08:13:00'); INSERT INTO "test" VALUES('2011-01-01 09:23:00'); INSERT INTO "test" VALUES('2011-01-02 09:15:00'); 

Only one of the dates is January 2.

 sqlite> select * from test order by time(date) desc; 2011-01-01 09:23:00 2011-01-02 09:15:00 2011-01-01 08:13:00 2011-01-01 08:00:00 

Moments later.,.

I realized that you want to sort by hours, not by time. This dubious requirement takes a different expression and is sorted differently.

 @events = Event.all(:order => "strftime('%H', date) DESC") sqlite> select date from test order by strftime('%H', date) desc; 2011-01-01 09:23:00 2011-01-02 09:15:00 2011-01-01 08:00:00 2011-01-01 08:13:00 

The last two lines are sorted correctly by the clock, wrong by time.

Still later.,.

OP is deployed to Heroku, which does not support SQLite. To sort clockwise, the OP probably needs something like

 @events = Event.all(:order => "extract (hour from date) DESC") 

And stop using one development platform and another deployment platform.

Answer 2

Since Heroku uses PostgreSQL, you can use:

 @events = Event.all(:order => "date_part(hour, date) DESC") 

You will have problems using SQLite3 locally and PostgreSQL for deployment. SQL is incompatible between platforms.