Rails :order by date in Postgres returning incorrect order

Question

I have a model called Story that I'm trying to order by the created_at date. Since I've hosted my app on Heroku, which uses Postgresql, I have the following in my controller:

@stories = Story.find( :all, :order => "DATE(created_at) DESC" , :limit => 11)

I would expect this to give the first 11 of my stories, ordered by the creation date, with the newest story first.

Unfortunately, this doesn't work. Most of the stories return ordered correctly, but the first two are flipped. That is, the latest story appears second in the list.

Why would this be? I have a sneaky suspicion that my results aren't ordered at all or are being ordered on the wrong column (maybe id?) and that until now it just happened to be ordered like I expected when displayed on my index page. How can I get it to order as I expect it to?

In case anyone cares, the index view is simply displaying the stories, in order. That is (HAML):

- @stories.each do |story|
  = render :partial => "event", :locals => { :event => story }

EDIT

I am suspicious that the created_at is a datetime column and the DATE(...) function disregards the time portion. So it returns the elements created on the same date in a random order. Since the first two stories were created on the same day, but several hours apart, which would explain why they seem to be 'reversed'. If this is the case, what would be the correct syntax to order by both date and time?

Answer 1

I believe you want:

@stories = Story.find(:all, :order => "created_at DESC" , :limit => 11)

Update for Rails 3+:

@stories = Story.order(created_at: :desc).limit(11)

Answer 2

If you are using Rails 3, I would also encourage you to use the cool new query syntax which would be:

@stories = Story.order('created_at DESC').limit(11)

See Active Record Query Interface for more information.