PostgreSQL日期函数整理

日期处理

• 字段创建
  • timestamp
  • 当前时间
• 日期函数
  • date_trunc(‘hour’, TIMESTAMP ‘2001-02-16 20:38:40’)
  • to_char(‘2017-08-01’::timestamp, ‘YYYYMMDD’)
  • to_date(‘20130403’, ‘yyyymmdd’)
  • extract(quarter from timestamp ‘2018-07-10 10:01:01’ )
  • generate_series

中文文档 时间/日期函数和操作符
中文文档 数据类型格式化函数

字段创建

timestamp

0表示**毫秒**级别保留位数

create table orders(
	purchase_date timestamp(0)
)

当前时间

localtimestamp,to_timestamp(1284352323)::timestamp(0)

SELECT CURRENT_TIME;
结果：14:39:53.662522-05

SELECT CURRENT_DATE;
结果：2001-12-23

SELECT CURRENT_TIMESTAMP;
结果：2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
结果：2001-12-23 14:39:53.66-05

select current_timestamp::timestamp(0);
结果：2018-12-12 13:45:38

select now()::timestamp(0);
结果：2018-12-12 13:45:38

select localtimestamp(0);
结果：2018-12-12 14:20:27

日期函数

• date_trunc

格式：

date_trunc('field', source)

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-02-16 20:00:00

source是类型timestamp或interval的值表达式（类型date和 time的值都分别被自动转换成timestamp或者interval）

field选择对输入值选用什么样的精度进行截断

field 有效值

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

date_trunc 按天分类汇总

SELECT
	count(*),
    date_trunc('day', field_name) as d
FROM
	tbl_name
group by  d

结果
1	2017-10-03 00:00:00
1	2017-10-09 00:00:00
3	2018-03-19 00:00:00

• to_char

select to_char('2017-08-01'::timestamp, 'YYYYMMDD')
select to_char(timestamp '2017-08-01', 'YYYYMMDD')

结果
20170801

to_char 按天分类汇总

SELECT
	count(*),
    to_char(field_name, 'YYYY-MM-DD') as d
FROM
	tbl_name
group by  d

结果
1	2017-10-03
1	2017-10-09
3	2018-03-19

• to_date

select to_date('20130403', 'yyyymmdd');

结果
2013-04-03

• extract

select extract(quarter from timestamp '2018-07-10 10:01:01' )
结果
3

field 有效值

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

• generate_series

基本用法

select generate_series(to_date('20130403', 'yyyymmdd'), to_date('20130603', 'yyyymmdd'), '3 days');  

select generate_series(timestamp '2013-04-03', timestamp '2013-06-03', '3 days');  

模拟千万级别数据, ||用作字符串连接符

select n as id, n || '_batch' as title from generate_series(1, 100000000) n
结果
1	1_batch
2	2_batch
3	3_batch
...