trueCamelType trueCamelType - 1 month ago 9
Ruby Question

How can I select distinct dates from a database, when it's formatted as DateTime

I have a database setup similarly to this

The output of

sqlite> PRAGMA table_info(mytable);


is

0|id|INTEGER
1|mydatetime|text


And it looks like this

|__id__|_____mydatetime_____|
| 0 | 2016-10-11 12:10:22|
| 1 | 2016-10-11 12:11:22|
| 2 | 2016-10-12 10:45:45|
| 3 | 2016-10-12 11:12:12|


In Ruby on Rails, I'd like to select all of the rows with the same date (ignoring time). And I'm looping through them, to do something for every date. For example:

If I had the same database, and instead of
DateTime
it was formatted as just the
Date
I would do something similar to below:

distinctDate = MyTable.select(:mydatetime).distinct.to_a

distinctDate.each do |x|
put x
end


But how can I write the select with the distinct, and it also ignore the time?

Joe Joe
Answer
MyTable.pluck("distinct date(updated_at)") 

That will give you back an array of distinct dates, then you can process them however you need to in the application.