PostgresSQL如何获取指定日期所在月份首尾带指定时间的时间戳?
PostgreSQL 实现月初/月末指定格式时间戳的等效方案
需求说明
需要获取指定日期所在自然月的两个时间节点:
- 当月第一天零点:格式为
YYYY-MM-DD 00:00:00 - 当月最后一天最后一秒:格式为
YYYY-MM-DD 23:59:59
对应MariaDB的参考实现如下:
SELECT CONCAT(DATE_ADD(DATE_ADD(LAST_DAY('2021-02-15 00:00:00'),INTERVAL 1 DAY),INTERVAL -1 MONTH), ' ', '00:00:00') AS first_day_of_the_month, CONCAT(LAST_DAY('2021-02-15 23:59:59'), ' ' ,'23:59:59') AS last_day_of_the_month;
预期输出结果:
first_day_of_the_month | last_day_of_the_month ------------------------|------------------------------- 2021-02-01 00:00:00 | 2021-02-28 23:59:59
原有PostgreSQL实现的问题
现有查询代码如下:
SELECT (date_trunc('MONTH', '2021-02-15 00:00:00'::TIMESTAMP) + INTERVAL '1 MONTH - 1 day')::TIMESTAMP + '1 days'::INTERVAL - '1 months'::INTERVAL AS first_day_of_the_month, (date_trunc('MONTH', '2021-02-15 23:59:59'::TIMESTAMP) + INTERVAL '1 MONTH - 1 day')::TIMESTAMP AS last_day_of_the_month;
存在两个核心问题:
- 月初计算逻辑冗余:
date_trunc('MONTH', 输入时间)本身就会返回当月1号的零点时间,不需要额外的多层日期加减 - 月末计算缺少时间偏移:当前逻辑仅通过日期加减得到当月最后一天的零点时间,没有叠加当天23小时59分59秒的时间偏移,因此
last_day_of_the_month字段返回的是月末当天00:00:00,不符合预期
修正后的实现代码
SELECT -- date_trunc截取到月维度,天然返回当月1号的00:00:00 date_trunc('MONTH', '2021-02-15 00:00:00'::TIMESTAMP) AS first_day_of_the_month, -- 当月1号0点加1个月得到下月1号0点,减1秒即为当月最后一天的23:59:59 (date_trunc('MONTH', '2021-02-15 23:59:59'::TIMESTAMP) + INTERVAL '1 MONTH - 1 second')::TIMESTAMP AS last_day_of_the_month;
代码运行后输出结果与预期完全一致。
备注:
- 如果需要处理带时区的时间类型,只需将代码中的
TIMESTAMP替换为TIMESTAMPTZ即可,逻辑完全通用。- 如果需要返回字符串类型的结果(和MariaDB原实现的返回类型一致),可以使用字符串拼接的方式实现,返回值为文本格式的时间字符串,不适合后续直接做日期运算:
SELECT TO_CHAR(date_trunc('MONTH', '2021-02-15'::TIMESTAMP), 'YYYY-MM-DD') || ' 00:00:00' AS first_day_of_the_month, TO_CHAR((date_trunc('MONTH', '2021-02-15'::TIMESTAMP) + INTERVAL '1 MONTH - 1 day'), 'YYYY-MM-DD') || ' 23:59:59' AS last_day_of_the_month;
内容的提问来源于stack exchange,提问作者Michi
相关产品推荐
相关产品推荐

