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

Question

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?

How are you defining a two-week interval here? Just as for single year-weeks, a year-2-week may vary from year to year. — Tim Biegeleisen, Oct 28 '19 at 11:26
@TimBiegeleisen I mean the same way as it works with date_trunk(week): yes it will vary, but I have only one-year date, so its not a problem. So if before days 1-7 of a month were Week 1, days 8-14 were Week 2 (if day 1 is Monday), so in the case I'm looking for days 1-14 would be "Week1" — Polly, Oct 28 '19 at 11:42

Tim Biegeleisen · Accepted Answer · 2019-10-28T12:01:56.287

1

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;

edited Oct 28 '19 at 12:01

answered Oct 28 '19 at 11:53

Tim Biegeleisen

502,043
27
286
360

It seems like Vertica doesn't support timestamp / int :( I tried extract(EPOCH FROM(DATE_TRUNC('week', event_time) - 1)) / 2 , but the output is not a date anymore... – Polly Oct 28 '19 at 12:00
@Polly Use `WEEK` to obtain the week year from your event date. I don't use Vertica FYI. – Tim Biegeleisen Oct 28 '19 at 12:02

score 0 · Answer 2 · answered Oct 28 '19 at 11:52

0

You can use case expression:

select (case when mod(week(event_time), 1)
             then date_trunc('week', event_time)
             else date_trunc('week', event_time) - interval '1 week'
        end)

answered Oct 28 '19 at 11:52

Gordon Linoff

1,242,037
58
646
786

but as I see, it does the same as date_trunc('week', event_time) - 7, with I already did :) So actually its the same one week interval, not two as I'm looking for. – Polly Oct 28 '19 at 12:07
@Polly . . . Not at all. This only does the subtract on even-numbered weeks. Do you understand how `case` and `mod` work? – Gordon Linoff Oct 28 '19 at 21:42

score 0 · Answer 3 · answered Oct 28 '19 at 12:58

So my final code actually is (with a help of @Tim Biegeleisen):

select distinct 
            event_date 
          , min(event_date) over (partition by two_week) as two_week
        from (
            select distinct 
                event_time::date as event_date
                , FLOOR(week((DATE_TRUNC('week', event_time) - 1)) / 2) AS two_week
            from MyTable

score 0 · Answer 4 · answered Oct 28 '19 at 13:01

0

Try one of my favourite Vertica-specific functions on date / time: TIME_SLICE() .

SELECT
  dt
, TIME_SLICE(dt,24*7*2,'HOUR') AS hebdo
FROM dt;

The biggest unit is the hour, but you can multiply it ....

answered Oct 28 '19 at 13:01

marcothesane

6,192
1
11
21

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

4 Answers4