上周日在Vertica SQL？
上周日在Vertica SQL？
提问于 2022-03-21 10:28:15
检查vsql没有dateadd函数。
来自有datetime.dt的熊猫(year=x，week=y.)
在这里输入自定义日期的替代方法是什么，这样就可以实现以下目标
GETDATE()+datediff(day=1, week=WEEK_ISO(GETDATE()) - 1)
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-03-22 13:21:19
我刚刚有了一个测试程序，我根据ISO和标准计算一周的第一天，并计算月份的第一天，以及ISO和“标准”一周的最后一天的一系列日期。
下面是完整的脚本及其结果--它还展示了使用TIMESERIES创建日期列表的过程：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71556065
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号