Search code examples
sqltimestampverticasql-timestamp

How to make two weeks date_trunk in SQL (Vertica)?

I need to turn each timestamp to its date_trunk with two weeks interval. Say, same as date_trunk('week', event_time), but it would be date_trunk('2 weeks', event_time). So I'd have a timestamp column, and its two-weeks date_trunk column as following.

I tried going with date_trunc('week', event_time) + '1 week'::interval or date_trunc('week', event_time) +7 but it just makes an offset from my event_date.

Does anyone know how to fix it?

Solution

  • This answer assumes that ISO week #1 and #2 should map to 2-week #1, weeks 3 and 4 map to 2-week #2 etc. We can try using floor and division here:

    SELECT
    event_time,
    FLOOR((WEEK(event_time) + 1) / 2) AS two_week
FROM yourTable;