CODEWITHSUNDEEP
sqlitedatetimedate
Username
Username

Reputation: 3663

How to group by quarter/3-month period (or any custom time period) in SQLite?

Here is my table in comma-delimited form:

date, number
2010-09-02, 2
2010-10-01, 3
2011-01-01, 4
2011-02-01, 5
2011-03-01, 6
2011-05-05, 7

There doesn't seem to be a 'quarter' datetime function in SQLite's syntax, so I was wondering if there's a workaround for this.

Upvotes: 3

Views: 5596

Answers (4)

Sam
Sam

Reputation: 11

How about using some modulus to solve it.

(((3-(month%3))%3)+month)/3

Where month is a column of numbers between 1-12

Basically I'm just rounding things up to the closest 3, then diving it by 3.

Upvotes: -1

Sam
Sam

Reputation: 11

Sorry, as an example.

select month, (((3-(month%3))%3)+month)/3 as 'qtr' from months

will give us:

1   1
2   1
3   1
4   2
5   2
6   2
7   3
8   3
9   3
10  4
11  4
12  4

Upvotes: 1

Michiel de Mare
Michiel de Mare

Reputation: 42440

(cast(strftime('%m', date) as integer) + 2) / 3 as quarter

Upvotes: 6

Jon
Jon

Reputation: 3255

CASE 
  WHEN cast(strftime('%m', date) as integer) BETWEEN 1 AND 3 THEN 1
  WHEN cast(strftime('%m', date) as integer) BETWEEN 4 and 6 THEN 2
  WHEN cast(strftime('%m', date) as integer) BETWEEN 7 and 9 THEN 3
  ELSE 4 END as Quarter

Upvotes: 4

Related Questions