CODEWITHSUNDEEP
sqlsqlite
abhijit
abhijit

Reputation: 6723

How to group by a date column by month

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?

Upvotes: 4

Views: 11637

Answers (2)

Steffen Roller
Steffen Roller

Reputation: 3492

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

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

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.

Upvotes: 8

Related Questions