Group range of dates in SQL query

Question

I have a SQL table in which I save a record that contains a DATE field, in the yyyy-mm-dd format. I'm trying to compute an average over partitions of the table defined by a months range, i.e. I want to group the rows by a window of dates between August and July of the next year, and then compute the average of a field in each partition.

How can I do this automatically, instead of using a
WHERE day BETWEEN ('1995-08-01' AND '1996-07-31' OR '1996-08-01' AND '1997-07-31')etc..,
since the data can grow indefinitely?

Sample data from the table

div date                    hometeam        awayteam    fthg    ftag
E0  2017-04-17 00:00:00.000 Middlesbrough   Arsenal     1       2
E0  2017-04-16 00:00:00.000 Man United      Chelsea     2       0
E0  2017-04-16 00:00:00.000 West Brom       Liverpool   0       1
E0  2017-04-15 00:00:00.000 Crystal Palace  Leicester   2       2
E0  2017-04-15 00:00:00.000 Everton         Burnley     3       1

I want to group the matches by soccer season instead of the year.

Can you please provide sample data from your table?

Sabri Karagönen
– Sabri Karagönen

2020-04-14 08:02:24 +00:00
Commented Apr 14, 2020 at 8:02 — Sabri Karagönen
– Sabri Karagönen, Commented Apr 14, 2020 at 8:02
Added, sorry @SabriKaragönen

Gianclè Monna
– Gianclè Monna

2020-04-14 08:14:09 +00:00
Commented Apr 14, 2020 at 8:14 — Gianclè Monna
– Gianclè Monna, Commented Apr 14, 2020 at 8:14

Sabri Karagönen · Accepted Answer · 2020-04-14 08:47:41Z

1

You can shift the date to the new year, and then extract year from that date.

SELECT EXTRACT(year from DATE_ADD(CAST(dt AS date), interval 5 month)) as season

To understand it better, I prepared a step by step query

SELECT dt, 
  DATE_ADD(CAST(dt AS date), interval 5 month) as shifted_dt, 
  EXTRACT(year from DATE_ADD(CAST(dt AS date), interval 5 month)) as season
FROM UNNEST(['1995-08-01', '1996-07-31', '2017-04-16', '1996-08-01', '1997-07-31']) as dt

answered Apr 14, 2020 at 8:47

Sabri Karagönen

2,3651 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group range of dates in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related