1、获取当前系统时间
select now();
select current_timestamp;
结果:2020-11-04 16:09:53.247825+08
2、获取当前日期或时间
select current_date;
结果:2020-11-04
select current_time;
结果:16:14:08.501182+08
3、查询昨天的数据
select
DISTINCT count(id)
from
tablexx
where coalesce(l.join_date,l.sys_createdate) >= current_date - 1;
4、查询本周的数据
SELECT ( DATE '2020-10-23' - INTERVAL '1 day' - ( EXTRACT ( dow FROM ( DATE'2020-10-23' - INTERVAL '1 day' ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE;
SELECT( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE startasy,
( DATE ( now() ) - ( EXTRACT ( dow FROM DATE ( now() ) ) - 1 || ' day' ) :: INTERVAL ) :: DATE + 6 endday
from table d LIMIT 5;
SELECT to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2)+6 ||' days' as interval),'yyyy-mm-dd');
5、获取本月、上月数据
select to_char((SELECT now() as timestamp),'mm');
select * from table where time >= date_trunc( 'month', now() );
select to_char((select now() - interval '1 month'),'mm');
6、获取本年数据
select to_char((SELECT now() as timestamp),'yyyy')
select to_char((select now() - interval '1 years'),'yyyy')