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

Question

I am wondering if it's possible to truncate dates other than using the default choices using date_trunc. For example, if I have a table that looks like this

date            dollars
2016-10-03        1
2016-10-05        1
2016-10-10        1
2016-10-17        2
2016-10-24        2

and say I want to truncate and group by each "biweekly" period (so in this example, two time periods, one starting 2016-10-03 and the other starting 2016-10-17).

I would like the result to be

date            dollars
2016-10-03        3
2016-10-17        4

How can I do this? I know with date_trunc I can do something like date_trunc('week', date) but what if I want to do something biweekly? Or what if I want to use some other custom date range?

Answer 1

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.

Answer 2

If you're okay with showing the "biweekly period number", rather than the date that begins the period, you can do something like this:

SELECT
    FLOOR(EXTRACT(WEEK FROM dt) / 2) AS period,
    SUM(dollars) AS dollars
FROM t
GROUP BY FLOOR(EXTRACT(WEEK FROM dt) / 2)
ORDER BY FLOOR(EXTRACT(WEEK FROM dt) / 2)

Which would give you these results:

period  dollars
20      3
21      4

In this case, the "period" is just the period's starting week's ISO week number divided by 2. You could do something similar for other ranges. The issue with this approach is it's not relative to the first date, but rather to the ISO week number.