SQLITE3 GROUP BY "anniversary date"

Question

I have data stored in a SQLITE3 database with this format:

id | ts | data ...
475158|2019-11-12 07:50:00 | ...

So I used to group by day, month, year, eg:

SELECT STRFTIME("%Y-%m", ts), SUM(<data in my table>)
FROM ( <my table> )
GROUP BY STRFTIME("%Y-%m", ts)
ORDER BY ts

Now I would like to do the same but based on an anniversary date.

For example if the anniversary date is 2010-11-17, I would like to get a result grouped by year but between 'YYYY'-11-17 and 'YYYY+1'-11-16

Example:

2017 |sum of data between  2016-11-17 & 2017-11-16
2018 |sum of data between  2017-11-17 & 2018-11-16
2019 |sum of data between  2018-11-17 & 2019-11-16

Is it possible easily?

Try this: SELECT STRFTIME("%Y-%m", ts) as avYear, SUM(<data in my table>) FROM ( <my table> ) GROUP BY avYear ORDER BY ts — Micha

forpas forpas · Accepted Answer · 2019-11-12T13:04:42

You can get the year in your case by this expression:

strftime('%Y', ts) + (substr(ts, 6, 5) >= '11-17')

so do this:

select 
  strftime('%Y', ts) + (substr(ts, 6, 5) >= '11-17') year,
  sum(data) total
from tablename  
group by year

See the demo.

SQLITE3 GROUP BY "anniversary date"

1 Answers