postgresql 日期函数使用场景

postgresql 日期函数

一、本月第一天

在报表抽取时，常常需要获取本月第一天。然后作为报表抽取的查询条件。第一天常常有两种格式，一种是日期，另外一种是时间戳。

-- 本月。如2019-02
SELECT to_char(now(),'YYYY-MM')
-- 本月第一天，日期格式 。 例如 2019-02-01
SELECT to_char(now(),'YYYY-MM-01')
-- 本月第一天。时间戳格式。 例如 2019-02-01 00:00:00
SELECT to_char(now(),'YYYY-MM-01 00:00:00')

还可以使用 date_trunc() 函数

-- 本月第一天： 2019-02-01 00:00:00+08
select date_trunc('month',now())
-- 本月第一天12点 ： 2019-02-01 12:00:00+08
select date_trunc('month',now()) +interval '12 h';

---

二、 下月第一天或者上月第一天

报表抽取时，抽取上月数据的情况也是很常见。时间小于下月第一天，可以做为这个月结束。或者两个时间点/日期 有必要。

-- 时间戳下个月开始 2019-03-01 00:00:00+08
SELECT to_timestamp((to_char(( now() + interval '1 month'),'YYYY-MM-01')),'YYYY-MM-DD 00:00:00')

-- 时间戳：上个月开始 2019-01-01 00:00:00+08
SELECT to_timestamp((to_char(( now() + interval '-1 month'),'YYYY-MM-01')),'YYYY-MM-DD 00:00:00')

-- 日期：下个月第一天 2019-03-01
SELECT to_date((to_char(( now() + interval '1 month'),'YYYY-MM-01')),'YYYY-MM-DD')

-- 日期：上个月第一天 2019-01-01
SELECT to_date((to_char(( now() + interval '-1 month'),'YYYY-MM-01')),'YYYY-MM-DD')

三、上一季度、本季度、下一季度

-- 上一季度第一天：2018-10-01 00:00:00+08
select date_trunc('quarter',now() + interval '-3 month')
-- 本季度第一天： 2019-01-01 00:00:00+08
select date_trunc('quarter',now())
-- 下一季度第一天：2019-04-01 00:00:00+08
select date_trunc('quarter',now() + interval '3 month')

---

四、 日期序列。

生成2019-01-01 ~2019-01-31 的日期序列

-- 生成时间戳的格式
SELECT * FROM generate_series('2019-01-01'::DATE,'2019-01-31'::DATE,'1 DAY');

-- 生成日期的格式
SELECT date(d) FROM generate_series('2019-01-01'::DATE,'2019-01-31'::DATE,'1 DAY') AS _date(d);

generate_series(start,stop,step) – 开始、结束、步长。

---

五、 提取日期字段的年、月、日

-- 提取日期字段的年、月、日
-- 年 2019
SELECT EXTRACT(YEAR FROM now()) 
-- 月 2
SELECT EXTRACT(MONTH FROM now()) 
-- 日 26
SELECT EXTRACT(DAY FROM now())

---

六、 今天是星期几

-- 今天星期：2
select CASE WHEN extract(DOW FROM now()) = 0 THEN 7 else extract(DOW FROM now()) END AS "星期"
-- 扩展，如果周一到周六为工作日，周日为休息日。
select CASE WHEN EXTRACT (DOW FROM now()) = 0 THEN '0' ELSE '1' END AS isworkdate

---

参考

关于date_trunc() 函数，参考这篇文章PostgreSQL date_trunc() 截断日期函数,完成定时时间语法.

日期函数 https://www.postgresql.org/docs/9.2/functions-datetime.html