[Lightning talk] Group by time interval in SQL

The other day I needed to write a query that grouped entries by time intervals (week, month, etc.)

I thought of PSQL's generate_series first as it is capable of creating series of numeric values with precise step. So my first attempt would be to select start and end dates of a series of one-week intervals, starting from the first case creation date.


It was not easy as it seems. I went through a hell of type casting. Then the easy step would be to group entries whose created date fails into these intervals.


In fact, I didn't even get to this step. I, in fact, went to the wrong direction, given the context of the feature I was implementing, it made more sense to group entries by calendar's week, not any random 7-day interval.


In order to achieve that, the most critical step was to be able to trace back the beginning of the week from any given date. Thank god, that was easy.


The price I had to pay for going the wrong way was high, but for this case, I guess I would eat it