How to group by a date column by month

Question

I have a table with a date column where date is stored in this format:

2012-08-01 16:39:17.601455+0530

How do I group or group_and_count on this column by month?

Answer 1

Your biggest problem is that SQLite won't directly recognize your dates as dates.

CREATE TABLE YOURTABLE (DateColumn date);
INSERT INTO "YOURTABLE" VALUES('2012-01-01');
INSERT INTO "YOURTABLE" VALUES('2012-08-01 16:39:17.601455+0530');

If you try to use strftime() to get the month . . .

sqlite> select strftime('%m', DateColumn) from yourtable;
01

. . . it picks up the month from the first row, but not from the second.

If you can reformat your existing data as valid timestamps (as far a SQLite is concerned), you can use this relatively simple query to group by year and month. (You almost certainly don't want to group by month alone.)

select strftime('%Y-%m', DateColumn) yr_mon, count(*) num_dates 
from yourtable 
group by yr_mon;

If you can't do that, you'll need to do some string parsing. Here's the simplest expression of this idea.

select substr(DateColumn, 1, 7) yr_mon, count(*) num_dates 
from yourtable 
group by yr_mon;

But that might not quite work for you. Since you have timezone information, it's sure to change the month for some values. To get a fully general solution, I think you'll need to correct for timezone, extract the year and month, and so on. The simpler approach would be to look hard at this data, declare "I'm not interested in accounting for those edge cases", and use the simpler query immediately above.

Answer 2

It took me a while to find the correct expression using Sequel. What I did was this:

Assuming a table like:

CREATE TABLE acct (date_time datetime, reward integer)

Then you can access the aggregated data as follows:

ds = DS[:acct]
ds.select_group(Sequel.function(:strftime, '%Y-%m', :date_time))
   .select_append{sum(:reward)}.each do |row|
  p row
end