PostgreSql 日期类型处理
1. 查询天数据 2. 查询月数据 3. 查询年数据 4.类型转换
1. 查询天数据
查询当天数据
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 ;
2. 查询月数据
查询当月数据
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 )
3. 查询年数据
查询当年数据
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 )
4.类型转换
查询某天:datetime类型的,需要转换为 date 类型,如果你要查询的字段已经是 date 类型则不需要进行转换 select t_create from table where t_create::date = to_date(‘2023-02-08’, ‘YYYY-MM-DD’); string转timestamp类型,按范围查询 select * from table where create_date >= ‘2023-01-08’::timestamp and create_date < ‘2023-02-08’::timestamp; 时间戳Long转Timestamp select TO_TIMESTAMP(1512490630) string转data,只能得到年月日,得不到时分秒 select to_date(‘2023-01-28 12:55:05’) 当前日期 select current_date 带时区的时分秒值 select current_time;也可以使用current_time(precision),将结果在四分之一秒的范围内四舍五入到位数,比如select current_time(2);对应没有时区的值:select localtime; 带时区的年月日时分秒值 select current_timestamp; 对应没有时区的值:select localtimestamp;