Pg日期时间类型

该博客聚焦于PostgreSQL数据库,详细介绍了日期时间类型,以及获取当前日期时间、日期时间加减运算、比较大小、与字符串转换和截取等操作,为使用PostgreSQL处理日期时间提供了全面的指导。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、日期时间类型

-- 时间日期类型
timestamp、timestampz、datetime

-- 时间日期常量
'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');
### PostgreSQL 中的时间类型及其应用 #### 时间类型的概述 PostgreSQL 提供了多种用于表示日期和时间的数据类型。`date` 类型仅存储年月日部分,适合记录具体的某一天[^1]。 对于更复杂的时间信息,可以使用 `timestamp` 和 `timestamptz`(即带有时区的 timestamp)。其中 `timestamp(0)` 表示精确到秒而不带有时区的信息;而 `timestamp without timezone` 则明确指出了该时间戳不含时区属性。 ```sql -- 创建包含不同时间类型的表结构 CREATE TABLE events ( id SERIAL PRIMARY KEY, event_date DATE NOT NULL, -- 只有日期的部分 event_timestamp TIMESTAMP WITHOUT TIME ZONE, -- 不含时区的时间戳 event_timestamptz TIMESTAMPTZ -- 含有时区的时间戳,默认当前事务的时区 ); ``` #### 处理特定月份或年度的数据 当需要处理基于年份或月份的操作时,可以通过定义额外列并利用表达式默认值来简化操作: ```sql ALTER TABLE public.t_water_md_run_log_main ADD COLUMN data_year_month CHAR(7); UPDATE t_water_md_run_log_main SET data_year_month = TO_CHAR(data_time, 'YYYY-MM'); ``` 上述命令向现有表格添加了一个新的字段 `data_year_month` 来保存格式化后的年月字符串,并通过更新语句填充已有数据中的这部分信息[^2]。 另外,在创建新表时也可以直接指定这样的计算方式作为某一列的默认值: ```sql CREATE TABLE monthly_events ( ... occurred_at TIMESTAMP WITHOUT TIME ZONE NOT NULL, year_month AS (TO_CHAR(occurred_at, 'YYYY-MM')) STORED ); ``` 这里使用了 SQL 标准中的生成列特性 (`GENERATED ... STORED`) ,使得每次插入或修改 `occurred_at` 字段时都会自动生成对应的 `year_month` 值。 #### 关联查询与分区策略 如果应用程序经常按照一定周期(如每年每月)访问历史数据,则考虑采用分区分片技术以提高性能效率。例如,可以根据业务需求设计按年月分布的不同子表方案,配合 Sharding-JDBC 实现透明化的多表联合读写功能[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值