CODEWITHSUNDEEP
sqlpostgresqldatetimestamp
Eduarda Oliveira
Eduarda Oliveira

Reputation: 41

How to use the date_trunc function for biweekly grouping

I need to use the date_trunc function to group biweekly.

The function, however, only have the options day, week and month. Does anyone know how to do it?

Above the code I have for week:

(date_trunc('week', CAST((CAST("Account"."createdAt" AS timestamp) + INTERVAL '1 day') AS timestamp)) - INTERVAL '1 day')

Upvotes: 3

Views: 3342

Answers (1)

S-Man
S-Man

Reputation: 23686

demo:db<>fiddle

SELECT
    date_part('week', value)::int / 2,
    array_agg(value)
FROM
    mydates
GROUP BY 1

You can take the week number and integer divide it by 2. For week 20: 20 / 2 == 10; for week 21: 21 / 2 == 10 as well. With this you can group every two weeks.

Of course, if there are several years in your table, you need to take the year part in account too...

demo:db<>fiddle

SELECT
    date_part('year', value) as year,
    date_part('week', value)::int / 2,
    array_agg(value)
FROM
    mydates
GROUP BY 1,2

Upvotes: 2

Related Questions