PostgreSql日期时间类型及操作

本文探讨了不同数据类型在传递参数时的格式规范，包括日期(date)、时间(time)、时间戳(timestamp)、带时区的时间戳(timestamptz)和时间间隔(interval)。详细解析了各种格式的构成和应用场景，对于理解和处理时间数据具有指导意义。

 一、时间类型

数据类型           传递参数                                                  参数格式
date                  2022-08-16                                              年月日
time                  13:06:54                                                   时分秒
timestamp        2022-08-10 13:07:04                               年月日时分秒
timestamptz     2022-08-10 13:07:08+00                         年月日时分秒时区
timetz               17:07:02+00                                            时分秒时区    
interval             30:30:30   -100:30:30   100:10:10   15    时间间隔

一、日期时间类型 

-- 时间日期类型
timestamp、timestampz、date、time

-- 时间日期常量
'2024-01-22 00:02:48'::timestamp;

'2024-01-22 00:02:48'::time;

'2024-01-22 00:02:48'::date; 

timestamp'2024-01-22 00:02:48';

time'2024-01-22 00:02:48';

date'2024-01-22 00:02:48'; 

--- 提取7天前的日期
SELECT (current_date - interval '7 day')::date as start_date

-- 明天的日期
SELECT (current_date + interval '1 day')::date as end_date

-- 当年的第一天
SELECT DATE_TRUNC('YEAR', CURRENT_DATE)::date
-- 2023-01-01

-- 当月的第一天
SELECT DATE_TRUNC('MONTH', CURRENT_DATE)::date
SELECT DATE_TRUNC('MONTH', current_timestamp)::date
-- 2023-12-01

-- 当年的最后一天
SELECT (DATE_TRUNC('YEAR', CURRENT_DATE+ INTERVAL '1 year') - interval '1 day')::date
-- 2023-12-31

-- 提取当前时间小时
select EXTRACT(HOUR FROM CURRENT_TIME)

 -- 时间戳转字符串再截取
SELECT now()::date,now()::timestamp,substring(''||now()::timestamp from 1 for 19);

二、获取当前日期时间 

-- 获取当前日期时间(精确到小数点后6位)
select now();
select current_timestamp;

select  clock_timestamp();

select localtimestamp: 返回当前的日期和时间，与时区有关。

now()：获取当前完整时间

current_timestamp：当前事务开始时的时间戳，同 now() 函数等效

current_date：获取当前日期

current_time：获取当前时间

localtime：当前时间

localtimestamp：当前事务开始时的时间戳

-- 获取当前日期时间(精确到小数点后0位)
select now()::timestamp(0);
select  current_timestamp::timestamp(0);

-- 获取当前日期
select current_date;

select  current_timestamp::date;

select date(current_timestamp)

-- 获取当前时间
select current_time; 

select  current_timestamp::time;

select  current_timestamp::time(0);--时间精确到小数点后0位

-- 获取当前完整时间
select now(), current_timestamp, localtimestamp, clock_timestamp(),transaction_timestamp()
-- 2023-12-28 09:42:01.188 +0800, 2023-12-28 09:42:01.188 +0800, 2023-12-28 09:42:01.188, 2023-12-28 09:42:01.188 +0800

-- 获取当前日期或时间
select current_date, current_time, localtime;
-- 2023-12-28, 09:43:47 +0800, 09:43:47
 

三、日期时间加减运算 

 select now() +'2 years':: interval ;
select now() + '2 day'::interval ; 

select now() + interval '2 years';
select now() + interval '2 year'; 
select now() + interval '2 y';
select now() + interval '2 Y';
select now() + interval '2Y';

-- 同理可得月周日时分秒： months, weeks, days, hours, minutes, seconds
SELECT
    now( ) + INTERVAL '2 years',--两年后
    now( ) + INTERVAL '1 month',--一个月后
    now( ) - INTERVAL '3 week',--三周前
    now( ) + '10 min';--十分钟后
 

-- 日期加法 
select now()::timestamp(0)+'1year'   as nextyear ;
select now()::timestamp(0)+'1month'  as nextmonth ;
select now()::timestamp(0)+'1day'    as nextday ;
select now()::timestamp(0)+'1hour'   as nexthour ;
select now()::timestamp(0)+'1minute' as nextminute ;
select now()::timestamp(0)+'1second' as nextsecond ;

-- 日期减法
select now()::timestamp(0)+'-1year'   as lastyear ;
select now()::timestamp(0)+'-1month'  as lastmonth ;
select now()::timestamp(0)+'-1day'    as lastday ;
select now()::timestamp(0)+'-1hour'   as lasthour ;
select now()::timestamp(0)+'-1minute' as lastminute ;
select now()::timestamp(0)+'-1second' as lastsecond ;

-- 日期混合运算
select now()::timestamp(0)+'1year 1month 1day 1hour 1minute' as next;
select now()::timestamp(0)+'-1year 1month 1day 1hour 1minute' as last;

-- date类型：

select current_date + integer '7' as r;

-- date类型：

select current_date + interval '1 hour' as r;

四、日期时间比较大小 

-- 日期时间变量可直接和字符串比较，系统会自动将字符串转变为日期时间类型 
select now() > '2023-09-26'  as result;
-- 日期时间变量可直接与字符串拼接，系统会自动将时间变量转换为字符串类型
select concat('当前时间：', now())  as result;

五、格式转换

-- 日期时间与字符串互换
select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');
select to_timestamp('2023-09-26 15:56:08', 'YYYY-MM-DD HH24:MI:SS');
select to_timestamp('26 Sep 2023', 'DD Mon YYYY');
select to_date('2023-09-26 15:56:08', 'YYYY-MM-DD HH24:MI:SS');

-- 时间转字符串
select to_char(now(), 'YYYY-MM-DD HH-MI:SS')
-- 2023-12-28 09-56:23

-- 字符串转日期
select to_date('05 Dec 2000', 'DD Mon YYYY')
-- 2000-12-05

-- 字符串转时间
select to_timestamp('05 Dec 2000', 'DD Mon YYYY')
-- 2000-12-05 00:00:00.000 +0800

-- Unix时间戳转时间
select to_timestamp(1703728867)
-- 2023-12-28 10:01:07.000 +0800
 

日期和时间字符串转换常用格式:

HH    一天的小时数(01-12)
HH12    一天的小时数(01-12)
HH24    一天的小时数(00-23)
MI    分钟(00-59)
SS    秒(00-59)
MS    毫秒(000-999)
US    微秒(000000-999999)
AM    正午标识(大写)
Y,YYY    带逗号的年(4和更多位)
YYYY    年(4和更多位)
YYY    年的后三位
YY    年的后两位
Y    年的最后一位
MONTH    全长大写月份名(空白填充为9字符)
Month    全长混合大小写月份名(空白填充为9字符)
month    全长小写月份名(空白填充为9字符)
MON    大写缩写月份名(3字符)
Mon    缩写混合大小写月份名(3字符)
mon    小写缩写月份名(3字符)
MM    月份号(01-12)
DAY    全长大写日期名(空白填充为9字符)
Day    全长混合大小写日期名(空白填充为9字符)
day    全长小写日期名(空白填充为9字符)
DY    缩写大写日期名(3字符)
Dy    缩写混合大小写日期名(3字符)
dy    缩写小写日期名(3字符)
DDD    一年里的日子(001-366)
DD    一个月里的日子(01-31)
D    一周里的日子(1-7；周日是1)
W    一个月里的周数(1-5)(第一周从该月第一天开始)
WW    一年里的周数(1-53)(第一周从该年的第一天开始) 

六、日期时间截取 

函数	说明
extract(field from timestamp)	获取时间戳字段
extract(field from date)	获取date字段
extract(field from interval)	从interval获取时间字段

-- 日期时间截取 

EXTRACT(field FROM source): 从日期时间中提取部分，例如年、月、日等。

SELECT EXTRACT(YEAR FROM CURRENT_DATE);

SELECT EXTRACT(MONTH FROM CURRENT_DATE);

SELECT EXTRACT(DAY FROM CURRENT_DATE);

DATE_PART(field, source): 与 EXTRACT 类似，但更灵活。

select date_part('day', timestamp '2023-09-26 15:56:08');
select date_part('hour', timestamp '2023-09-26 15:56:08');

select date_part('year', timestamp '2023-09-26 15:56:08');

DATE_TRUNC(field, source): 将日期时间截断到指定的精度。

select date_trunc('sec', timestamp '2023-09-26 15:56:08');

select date_trunc('min', timestamp '2023-09-26 15:56:08');
select date_trunc('hour', timestamp '2023-09-26 15:56:08');

select date_trunc('day', timestamp '2023-09-26 15:56:08');

select date_trunc('month', timestamp '2023-09-26 15:56:08');

select date_trunc('year', timestamp '2023-09-26 15:56:08');

函数to_timestamp

函数用于将字符串String按照format格式转换为timestamp with time zone类型（时间戳格式）

SELECT to_timestamp('2023-05-10 19:08:00.678', 'YYYY-MM-DD HH24:MI:SS.MS');
-- 2023-05-10 19:08:00.678+08
-- HH24表示24小时制的小时，MI表示分钟，SS表示秒数，MS表示毫秒数

函数	说明
to_timestamp(text, text)	把字串转换成时间戳
to_timestamp(double)	把数字转时间戳

数	说明
to_date(text, text)	字串转日期，第1个参数是时间，第2个参数是格式化字符串
date(timestamp)	时间戳转日期
date(text)	字符串转日期

 函数to_char

to_char（expre，format）函数用于将timestamp，inteval，integer，double precision，或者numeric类型的值转换为指定格式的字符串。

timeofday()

字符串，当前日期和时间

select to_char(interval '10h 36m 30s', 'HH12:MI:SS')
-- 10:36:30
select to_char(CURRENT_TIMESTAMP ,'HH24:MI:SS' )
-- 10:37:32

日期时间类型转换

CAST(expression AS type): 将一个值转换为指定的类型，例如将文本转换为日期。

SELECT CAST('2023-04-01' AS DATE);

时间间隔

select age('2023-11-11', '2023-11-1') -- 10 days

日期时间格式化字符串:

模式	描述
YYYY	4位年
MM	月份号(01-12)
DD	一个月里的日子(01-31)
HH24	一天的小时数(00-23)
MI	分钟(00-59)
SS	秒(00-59)
MS	毫秒(000-999)

模式    描述
HH    一天的小时数(01-12)
HH12    一天的小时数(01-12)
US    微秒(000000-999999)
AM    正午标识(大写)
Y,YYY    带逗号的年(4和更多位)
YYY    年的后三位
YY    年的后两位
Y    年的最后一位
MONTH    全长大写月份名(空白填充为9字符)
Month    全长混合大小写月份名(空白填充为9字符)
month    全长小写月份名(空白填充为9字符)
MON    大写缩写月份名(3字符)
Mon    缩写混合大小写月份名(3字符)
mon    小写缩写月份名(3字符)
DAY    全长大写日期名(空白填充为9字符)
Day    全长混合大小写日期名(空白填充为9字符)
day    全长小写日期名(空白填充为9字符)
DY    缩写大写日期名(3字符)
Dy    缩写混合大小写日期名(3字符)
dy    缩写小写日期名(3字符)
DDD    一年里的日子(001-366)
D    一周里的日子(1-7；周日是1)
W    一个月里的周数(1-5)(第一周从该月第一天开始)
WW    一年里的周数(1-53)(第一周从该年的第一天开始)