PostgreSql日期类型处理详细实例

目录

• 1. 查询天数据
• 2. 查询月数据
• 3. 查询年数据
• 4.类型转换
• 补充：时区转换
• 总结

select * from table1 as n
where n.created_time>=current_date;

select * from table1 as n
where n.created_time>=current_date-1 and n.created_time <current_date ;

select *
from table1 as n
WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now())
and extract(MONTH FROM created_time) = extract(MONTH FROM now()) 

select *
from table1 as n
where created_time >= date_trunc('month',current_date - interval '1' month)
and created_time < date_trunc('month',current_date)

select *
from table1 as n
WHERE extract(YEAR FROM created_time) = extract(YEAR FROM now()) ORDER BY created_time

select *
from table1 as n
where created_time >= date_trunc('year',current_date - interval '1' year)
and created_time < date_trunc('year',current_date)

select t_create
from table
where t_create::date = to_date(‘2023-02-08', ‘YYYY-MM-DD');

select * from table where create_date >= ‘2023-01-08'::timestamp and create_date < ‘2023-02-08'::timestamp;

select TO_TIMESTAMP(1512490630)

select to_date(‘2023-01-28 12:55:05')

postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
       now
-------------------------------
 2013-08-26 12:39:39.122218+02
postgres=# SELECT now() AT TIME ZONE 'GMT';
     timezone
----------------------------
 2013-08-26 10:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'GMT+1';
     timezone
----------------------------
 2013-08-26 09:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'PST';
     timezone
----------------------------
 2013-08-26 02:39:39.122218