PostgreSQL (Hologres) 日期生成

最新推荐文章于 2024-04-30 20:30:42 发布

myqijin

最新推荐文章于 2024-04-30 20:30:42 发布

阅读量421

点赞数

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/myqijin/article/details/134549805

版权

PostgreSQL 生成指定日期下一个月的日期

（在Hologres中，不支持递归查询）

SELECT
    to_char(T, 'YYYYMMDD')::int4 AS date_int,
    date(T) AS date_str,
    date_part('year', T)::int4 AS year_int,
    date_part('month', T)::int4 AS month_int,
    date_part('day', T)::int4 AS day_in_month,
    date_part('doy', T)::int4 AS day_in_year,
    date_part('week', T)::int4 AS week_int,
    date_part('quarter', T)::int4 AS querter_int,
    T - (date_part('isodow', T) - 1 || ' days')::interval AS week_start,
    T + (7 - date_part('isodow', T) + 1 || ' days')::interval - INTERVAL '1 seconds' AS week_end,
    date_trunc('month', T) AS month_start,
    date_trunc('month', T + '1 months') - interval '1 seconds' AS month_end,
    now()
FROM
    generate_series(('${dd}')::date  + INTERVAL '1 days', (date_trunc('month', '${dd}'::date + INTERVAL '2 months'))::date - INTERVAL '1 days', '1 days') AS T;

输入参数'2023-10-31'，结果如下