[SOLVED] Last Sunday in Vertica SQL?

Issue

checked that vsql doesn’t have dateadd function.

Coming from pandas where there’s datetime.dt(year=x, week=y…)

What’s the alternative to input a custom date here so that the below can be achieved

GETDATE()+datediff(day=1, week=WEEK_ISO(GETDATE()) - 1)

Solution

I just had a test program up my sleeve, where I calculate the first day of the week according to ISO and according to standard, and of the month, and also the last day of the ISO and of the "standard" week for a series of dates.

Here’s the full script and its result – it also shows the use of TIMESERIES to create a list of dates out of nothing:

WITH dtlimits(dt) AS (
            SELECT DATE '2021-12-20'
  UNION ALL SELECT DATE '2022-01-10'
)
,
dtlist AS (
  SELECT
    tsd::DATE AS dt
  FROM dtlimits
  TIMESERIES tsd AS '1 DAY' OVER(ORDER BY dt::TIMESTAMP)
)
SELECT
  dt
, TO_CHAR(dt,'Dy') AS wkday
, dayofweek(dt)
, dayofweek_iso(dt)
, YEAR(dt)*100+WEEK(dt) AS yw
, YEAR(dt)*100+WEEK_ISO(dt)  AS ywiso
, (dt - dayofweek_iso(dt) + 1)                         AS firstdowiso
, (dt - dayofweek(dt) + 1    )                         AS firstdow
, ((dt - dayofweek(dt) + 6    ) + (86399/86400))::DATE AS lastdow
, ((dt - dayofweek_iso(dt) + 6) + (86399/86400))::DATE AS lastdowiso
, dayofmonth(dt)             AS dom
FROM dtlist;
-- out      dt     | wkday | dayofweek | dayofweek_iso |   yw   | ywiso  | firstdowiso |  firstdow  |  lastdow   | lastdowiso | dom 
-- out ------------+-------+-----------+---------------+--------+--------+-------------+------------+------------+------------+-----
-- out  2021-12-20 | Mon   |         2 |             1 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  20
-- out  2021-12-21 | Tue   |         3 |             2 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  21
-- out  2021-12-22 | Wed   |         4 |             3 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  22
-- out  2021-12-23 | Thu   |         5 |             4 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  23
-- out  2021-12-24 | Fri   |         6 |             5 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  24
-- out  2021-12-25 | Sat   |         7 |             6 | 202152 | 202151 | 2021-12-20  | 2021-12-19 | 2021-12-24 | 2021-12-25 |  25
-- out  2021-12-26 | Sun   |         1 |             7 | 202153 | 202151 | 2021-12-20  | 2021-12-26 | 2021-12-31 | 2021-12-25 |  26
-- out  2021-12-27 | Mon   |         2 |             1 | 202153 | 202152 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |  27
-- out  2021-12-28 | Tue   |         3 |             2 | 202153 | 202152 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |  28
-- out  2021-12-29 | Wed   |         4 |             3 | 202153 | 202152 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |  29
-- out  2021-12-30 | Thu   |         5 |             4 | 202153 | 202152 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |  30
-- out  2021-12-31 | Fri   |         6 |             5 | 202153 | 202152 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |  31
-- out  2022-01-01 | Sat   |         7 |             6 | 202201 | 202252 | 2021-12-27  | 2021-12-26 | 2021-12-31 | 2022-01-01 |   1
-- out  2022-01-02 | Sun   |         1 |             7 | 202202 | 202252 | 2021-12-27  | 2022-01-02 | 2022-01-07 | 2022-01-01 |   2
-- out  2022-01-03 | Mon   |         2 |             1 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   3
-- out  2022-01-04 | Tue   |         3 |             2 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   4
-- out  2022-01-05 | Wed   |         4 |             3 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   5
-- out  2022-01-06 | Thu   |         5 |             4 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   6
-- out  2022-01-07 | Fri   |         6 |             5 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   7
-- out  2022-01-08 | Sat   |         7 |             6 | 202202 | 202201 | 2022-01-03  | 2022-01-02 | 2022-01-07 | 2022-01-08 |   8
-- out  2022-01-09 | Sun   |         1 |             7 | 202203 | 202201 | 2022-01-03  | 2022-01-09 | 2022-01-14 | 2022-01-08 |   9
-- out  2022-01-10 | Mon   |         2 |             1 | 202203 | 202202 | 2022-01-10  | 2022-01-09 | 2022-01-14 | 2022-01-15 |  10

Answered By – marcothesane

Answer Checked By – Gilberto Lyons (BugsFixing Admin)

Leave a Reply

Your email address will not be published.