PostgreSQL 常用函数（二）

日期时间函数

PostgreSQL提供了以下日期和时间运算的算术运算符。

PostgreSQL还提供了大量用于日期和时间数据处理的函数。

计算时间间隔

age(timestamp, timestamp)函数用于计算两个时间点之间的间隔，age(timestamp)函数用于计算当前日期的凌晨12点到该时间点之间的间隔 

SELECT age(timestamp '2023-03-22', timestamp '2023-03-21'),--0 years 0 mons 1 days 0 hours 0 mins 0.00 secs
       age(timestamp '2023-02-01')--0 years 1 mons 20 days 0 hours 0 mins 0.00 secs

2023年3月22号到2023年3月21号已经过去了1天，今天（2023年3月23号）到2023年2月21号已经过去了1个月右20天；

获取时间中的信息

date_part(text, timestamp)和extract(field FROMtimestamp)函数用于获取日期时间中的某一部分，例如年份、月份、小时等；date_part(text, interval)和extract(field FROMinterval)函数用于获取时间间隔中的某一部分

SELECT date_part('quarter', timestamp '2023-03-23 20:38:40')-- 1 季度，（1 -4）
     , date_part('isoyear', timestamp '2023-03-23 20:38:40')-- 2023 ISO 8601标准中的星期一（1）到星期天（7）
     , date_part('isodow', timestamp '2023-03-23 20:38:40')-- 4 ISO 8601标准中的星期一（1）到星期天（7）
     , date_part('hour', timestamp '2023-03-23 20:38:40')-- 20 小时（1 -23）
     , date_part('epoch', timestamp '2023-03-23 20:38:40')-- 1679603920 对于timestamp WITH time zone，返回1970-01-01 00:00:00 UTC到该时间的秒数；对于date和timestamp，返回本地时间的1970-01-01  00:00:00到该时间的秒数；对于interval，返回以秒数表示的该时间间隔
     , date_part('dow', timestamp '2023-03-23 20:38:40')-- 4 星期天（0）到星期六（6）
     , date_part('doy', timestamp '2023-03-23 20:38:40')-- 82 一年中的第几天，（1 -365/366）
     , date_part('decade', timestamp '2023-03-23 20:38:40')-- 202 --年份除以10
     , date_part('day', timestamp '2023-03-23 20:38:40')-- 23 --月份中第几天
     , date_part('century', timestamp '2023-03-23 20:38:40')-- 21 --世纪
     , date_part('year', timestamp '2023-03-23 20:38:40')   --2023 --年
     , extract(year FROM timestamp '2023-03-23 20:38:40')   --2023 --
     , date_part('month', interval '1 years 5 months')
     , extract(month FROM interval '1 years 5 months'); --5

截断日期/时间

date_trunc(field, source [, time_zone ])函数用于将timestamp、timestamp WITH time zone、date、time或者interval数据截断到指定的精度。

SELECT date_trunc('year', timestamp '2023-03-23 20:38:40'),--2023-01-01 00:00:00.000000
       date_trunc('day', timestamptz '2023-03-23 20:38:40+00', 'Asia/Shanghai'), --2023-03-23 16:00:00.000000
       date_trunc('hour', interval '2 days 3 hours 40 minutes'); --0 years 0 mons 2 days 3 hours 0 mins 0.00 secs

 date_trunc函数支持以下截断精度：

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

 创建日期/时间

make_date(year int, month int, day int)函数用于创建一个日期：

 SELECT make_date(2022, 03, 15);--2022-03-15

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)函数通过指定年、月、日等信息创建一个时间间隔。

SELECT make_interval(days => 1, hours => 5); -- 0 years 0 mons 1 days 5 hours 0 mins 0.00 secs

make_time(hour int, min int, sec double precision)函数通过指定小时、分钟和秒数创建一个时间

SELECT make_time(1, 2, 30.5);--01:02:30

make_timestamp(year int, month int, day int, hour int, min int, sec double precision)函数通过指定年、月、日、时、分、秒创建一个时间戳

SELECT make_timestamp(2020, 3, 15, 8, 20, 23.5);--2020-03-15 08:20:23.500000

make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])函数通过指定年、月、日、时、分、秒创建一个带时区的时间戳。如果没有指定时区，使用当前时区

SELECT make_timestamptz(2020, 3, 15, 8, 20, 23.5); -- 2020-03-15 08:20:23.500000

to_timestamp(double precision)函数将Unix时间戳（自从1970-01-01 00:00:00+00以来的秒数）转换为PostgreSQL时间戳数据。

SELECT to_timestamp(1583152349);--2020-03-02 12:32:29.000000

获取系统时间

PostgreSQL提供了大量用于获取系统当前日期和时间的函数，例如current_date、current_time、current_timestamp、clock_timestamp()、localtimestamp、now()、statement_timestamp()等；同时还支持延迟语句执行的pg_sleep()等函数

时区转换

AT TIME ZONE运算符用于将timestamp without time zone、timestamp WITH time zone以及time WITH time zone转换为指定时区中的时间

SELECT TIMESTAMP '2023-03-23 20:38:40' AT TIME ZONE 'Asia/Shanghai',--2023-03-23 12:38:40.000000
       TIMESTAMP WITH TIME ZONE '2023-03-22  20:38:40-05:00' AT TIME ZONE 'Asia/Shanghai',--2023-03-23 09:38:40.000000
       TIME WITH TIME ZONE '20:38:40-05:00' AT TIME ZONE 'Asia/Shanghai';--09:38:40.000000 +08:00

类型转换函数

CAST 函数

CAST ( expr AS data_type )函数用于将expr转换为data_type数据类型；PostgreSQL类型转换运算符（::）也可以实现相同的功能。例如：

select cast('12' as integer),'3.1715'::numeric ,'3.1715'::decimal,'2023-03-26'::date;
12    3.1715    3.1715    2023-03-26

如果数据无法转换为指定的类型，将会返回错误

select 'a'::int,cast('3.12q' as numeric)
[22P02] 错误: 无效的类型 integer 输入语法: "a" 位置：8

to_date 函数

to_date(string, format)函数用于将字符串string按照format格式转换为日期类型。

SELECT to_date('2023/03/25','YYYY/MM/DD'),to_date('20230326','yyyymmdd');
2023-03-25    2023-03-26

to_timestamp 函数

to_timestamp(string, format)函数用于将字符串string按照format格式转换为timestampWITH time zone类型

SELECT to_timestamp('2023-03-25 19:08:00.678', 'YYYY-MM-DD HH24:MI:SS.MS'), to_timestamp('2023-03-25', 'YYYY-MM-DD HH24:MI:SS.MS')
2023-03-25 19:08:00.678000    2023-03-25 00:00:00.000000

to_char 函数

to_char(expre, format)函数用于将timestamp、interval、integer、double precision或者numeric类型的值转换为指定格式的字符串

SELECT to_char(current_timestamp, 'HH24:MI:SS'),to_char(interval '5h 12m 30s', 'HH12:MI:SS'),to_char(-125.8, '999D99');
14:46:11    05:12:30    -125.80

to_number 函数

to_number(string, format)函数用于将字符串转换为数字

SELECT to_number('￥125.8', 'L999D9');
125.8