I have a problem with ordering a collection by hour. But first things first.
Project details:
Rails version 5.1.3
Ruby version 2.4.1-p111 (x86_64-linux)
Database adapter postgresql
This is how my collection looks like:
#<DeliveryTimeslot:0x00562dd1ad2690
id: 1,
start: Sun, 02 Jan 2000 01:00:00 +03 +03:00,
stop: Sun, 02 Jan 2000 02:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:39 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:25 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abcf98
id: 2,
start: Sun, 02 Jan 2000 02:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 03:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:39 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:25 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ad23c0
id: 3,
start: Sat, 01 Jan 2000 03:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 04:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:39 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ad1e70
id: 4,
start: Sat, 01 Jan 2000 04:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 05:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ad1470
id: 5,
start: Sat, 01 Jan 2000 05:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 06:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ad0bd8
id: 6,
start: Sat, 01 Jan 2000 06:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 07:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acfdc8
id: 7,
start: Sat, 01 Jan 2000 07:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 08:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acf7b0
id: 8,
start: Sat, 01 Jan 2000 08:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 09:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acf198
id: 9,
start: Sat, 01 Jan 2000 09:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 10:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acee00
id: 10,
start: Sat, 01 Jan 2000 10:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 11:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1aceb80
id: 11,
start: Sat, 01 Jan 2000 11:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 12:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ace838
id: 12,
start: Sat, 01 Jan 2000 12:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 13:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1ace4c8
id: 13,
start: Sat, 01 Jan 2000 13:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 14:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acdde8
id: 14,
start: Sat, 01 Jan 2000 14:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 15:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acd758
id: 15,
start: Sat, 01 Jan 2000 15:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 16:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acd168
id: 16,
start: Sat, 01 Jan 2000 16:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 17:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:26 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acce20
id: 17,
start: Sat, 01 Jan 2000 17:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 18:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1acc678
id: 18,
start: Sat, 01 Jan 2000 18:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 19:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abfb30
id: 19,
start: Sat, 01 Jan 2000 19:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 20:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abf220
id: 20,
start: Sat, 01 Jan 2000 20:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 21:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abea78
id: 21,
start: Sat, 01 Jan 2000 21:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 22:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abe500
id: 22,
start: Sat, 01 Jan 2000 22:00:00 +03 +03:00,
stop: Sat, 01 Jan 2000 23:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abdf10
id: 23,
start: Sat, 01 Jan 2000 23:00:00 +03 +03:00,
stop: Sun, 02 Jan 2000 00:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>,
#<DeliveryTimeslot:0x00562dd1abd3f8
id: 24,
start: Sun, 02 Jan 2000 00:00:00 +03 +03:00,
stop: Sun, 02 Jan 2000 01:00:00 +03 +03:00,
created_at: Wed, 05 Oct 2016 17:57:40 +03 +03:00,
updated_at: Thu, 16 Mar 2017 12:40:27 +03 +03:00>]
Both start and stop columns are t.time in schema (so records are ActiveSupport::TimeWithZone).
What I want to do is to order them by hour and I have to do it using SQL statements.
So I have tried this:
DeliveryTimeslot.all.order("EXTRACT (HOUR FROM start) DESC").map(&:id)
and I expected to get array like this:
[23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 24]
but instead I've got this:
[2, 1, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3]
My thought was that this is because three records (with ids 1, 2 and 24) have a different start date (2nd of january while rest of them have 1st of january). Isn't the query I've wrote above supposed to extract hour from time type record? Is it really because of date? IDK why this is not working and basically I can't change anything in DB. Any hints what I am doing wrong? Any hints for make it without any changes in DB?
Found the solution. It worked with my existing application
Try it
DeliveryTimeslot.all.order('start DESC').sort_by {|item| item.start.to_date}.map{|item| item.id}