Pg日期时间类型

一、日期时间类型

-- 时间日期类型
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'; 

二、获取当前日期时间

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

三、日期时间加减运算

-- 日期加法 
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;

四、日期时间比较大小

-- 日期时间变量可直接和字符串比较，系统会自动将字符串转变为日期时间类型 
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 date_part('hour', timestamp '2023-09-26 15:56:08');
select date_trunc('hour', timestamp '2023-09-26 15:56:08');