Truncate date in units other than default choices using date_trunc (Postgres 9.5.1)

postgresql

Solution

I believe there is no way of doing this using only one built-in function. However, there are ways of doing this using a combination of them.

To output the same result you are requesting you could use the following query:

SELECT TO_DATE(
               CONCAT(
                  DATE_PART('YEAR', date),
                  (DATE_PART('WEEK', date)::INTEGER / 2) * 2), 
       'iyyyiw') AS "date",
   SUM(dollars) AS dollars
FROM my_table
GROUP BY 1
ORDER BY 1

Let me explain. `DATE_PART` will extract the week number from your date column (dates from `2016-10-03` to `2016-10-09` will have a value of `40`, dates from `2016-10-10` to `2016-10-16` will have a value of `41`, etc.). Casting it to `INTEGER` and then dividing it by `2` will force an integer division, truncating values (`20` will remain as `20`, `20.5` will truncate to `20`, etc.). It is not useful to leave this truncated number, so I multiply it by two to return the week number by which you wish to group by.

I'm using `DATE_PART` again to retrieve the year from your `date` column, then I concatenate the year and week number using the `CONCAT` function, and finally use the `TO_DATE` function to transform the string containing the year and week number to a date format (using ISO standards).

Using this new column will group your data as desired.

Nevertheless, the solution above will work as expected only because your data starts from the specific date `2016-10-03`, which has a week number of `40`. Let me work another solution that will generalize better for other date ranges.

Let's suppose that you wish to group not by the bi-weeks that start from `2016-10-03` but by the bi-weeks that start from `2016-09-26` (one week before). You could use the same solution as the above, but you'll have to add a slight modification.

SELECT TO_DATE(
               CONCAT(
                  DATE_PART('YEAR', date),
                  ((DATE_PART('WEEK', date) + 1)::INTEGER / 2) * 2 - 1), 
       'iyyyiw') AS "date",
   SUM(dollars) AS dollars
FROM my_table
GROUP BY 1
ORDER BY 1

Adding a `+1` right after using the function `DATE_PART` when extracting the week, will 'move' all the weeks to the next 'bin' (week `40` moves to `41`, week `41` moves to `42`, etc.). Of course, you must then take away the `+1` to return to the 'original' bin. However, this will cause the division by `2` to start truncating everything differently and your query will now start to group your data by bi-weeks that start from `2016-09-26`.

To answer your question

Or what if I want to use some other custom date range?

The solution above can generalize well over other cases, such as grouping by tri-weeks, bi-months, etc.

To start grouping by a different unit (day, week, months, etc.) just change your `DATE_PART('WEEK',...)` function to something like `DATE_PART('MONTH',...)` and change the parameter `iyyyiw` to `iyyyim` (this is described in the documentation). You could use the same trick of using the `+1` and `-1` to start grouping bi-months starting from January or February.

If you wish to group by a tri-week, for example, you must change the numbers dividing and multiplying by `2` to `3`. Here, if you wish to change the first tri-week period on which to group by, you need to use the same trick of adding `+1` and `-1` but you may now need to use `+2` and `-2`.

Problem

Original source