Selecting distinct datetimes

I'm currently learning how to use Ruby on Rails with The Odin Project and right now I'm tackling making forms. I'm trying to pull unique dates from my database to populate a dropdown options box for a project. I'm using form_for in my view and I am using the select function which takes an array of arrays i.e. [["option one", 1], ["option two", 2]] etc. The idea is that only unique dates that are in the database should show up in the options. My problem is duplicate dates keep showing up e.g.

03/03/2016
03/03/2016
04/03/2016
05/03/2016
06/03/2016
06/03/2016

Using find_by_sql I tried

SELECT DISTINCT start FROM flights

to no avail. After a quick Google search I tried

SELECT start FROM flights GROUP BY start

but I'm still getting duplicates. I'm not the best with SQL so any help would be appreciated.

edit:

My datatime column does have a time component so that's probably the issue

From the db schema here is my flights table if it's any help

create_table "flights", force: :cascade do |t|
  t.integer  "from_airport_id"
  t.integer  "to_airport_id"
  t.datetime "start"
  t.integer  "flight_duration"
  t.datetime "created_at",      null: false
  t.datetime "updated_at",      null: false
end

Answers


If selecting distinct dates is still getting duplicates, it is most likely that the date format has time, and the dates have different times.

You should cast the column start to date CAST(start AS DATE) to convert the column to date, and only then use the group by.


After some more searching online I found a much easier way to get the result I wanted. Using the :pluck method as a short cut to pull just the attributes I wanted from the database, calling :to_date on the datetime to remove the time component, then calling :uniq to just get the unique dates like I required.

Flight.pluck(:start).map {|a| [a.strftime("%d/%m/%Y"), a.to_date]}.uniq

Need Your Help

PHP IDE--Want to sync local storage with FTP

php ide tortoisesvn notepad++ aptana

I currently use Notepad++ for most of my development. I have been checking out other, more full-featured options and would like to switch (I'm in particular a fan of Aptana so far) but there is one

Wrong mouse position in drag and drop inside grid class

c# wpf .net-4.0 mousemove mouse-position

I'm basically writing a simple floating panel whose location can be changed via dragging its title bar (which is a grid itself). But I can't get it working! It seems MouseEventArgs.GetPosition retu...