postgresql时间计算

1. postgresql 字符串转时间戳写法

select to_char(x.log_time,'yyyy-MM-dd hh24:mi:ss.MS') 
		from mn_xiaoyu_log x    where x.log_time >to_timestamp('2019-04-28','yyyy-MM-dd') limit 10;

2. postgresql 时间计算

select interval_value, date_part('day', interval_value) * 24 * 60 + date_part('minute', interval_value) as minutes
, date_part('day', interval_value) * 24 * 60*60 + date_part('minute', interval_value)*60 +date_part('second', interval_value) as second
from (
select (current_timestamp - to_timestamp('2013-08-21 13:23', 'yyyy-mm-dd hh24:mi')) as interval_value
) s;

3. 按日期范围查询

-- 按日期范围查询

Timestamp without timezone
-- 方法一：

select * from user_info where create_date >= '2015-07-01' and create_date < '2015-08-15';
-- 方法二：为啥字符串可以按日期格式比较大小

select * from user_info where create_date between '2015-07-01' and '2015-08-15';
-- 方法三：

select * from user_info where create_date >= '2015-07-01'::timestamp and create_date < '2015-08-15'::timestamp;
-- 方法四：

select * from user_info where create_date between to_date('2015-07-01','YYYY-MM-DD') and to_date('2015-08-15','YYYY-MM-DD');
-- 设置变量查询
set var.startTime= '2019-04-14';
set var.endTime= '2019-05-15';
-- 方法五
select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and to_char(t.date_created, 'YYYY-MM-DD') >= current_setting('var.startTime')
									and to_char(t.date_created, 'YYYY-MM-DD') <= current_setting('var.endTime')
 ORDER BY t.date_created DESC
;

set var.startTime= '2019-04-14';
set var.endTime= '2019-05-16';

-- 设置变量需要转换为时间戳::timestamp(6)，不设置可以直接比较
select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and  t.date_created  >= current_setting('var.startTime')::timestamp(6)
									and  t.date_created  <= current_setting('var.endTime')::timestamp(6)
 ORDER BY t.date_created DESC
;
-- EXPLAIN ANALYZE 效率最高
select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and  t.date_created  BETWEEN current_setting('var.startTime')::timestamp(6)
									and    current_setting('var.endTime')::timestamp(6)
ORDER BY t.date_created DESC
;
select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and  t.date_created  >='2019-05-14' and t.date_created  <='2019-05-15'
ORDER BY t.date_created DESC;

select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and  t.date_created  
between to_date('2019-04-14','YYYY-MM-DD') and to_date('2019-05-15','YYYY-MM-DD')
ORDER BY t.date_created DESC;

select  t.date_created,t.*  from meeting_sended_times_info t 
                            where t.start_meeting is not null and t.callkit_meeting is not null
									and  t.date_created  
between to_date(current_setting('var.startTime'),'YYYY-MM-DD') and to_date(current_setting('var.endTime'),'YYYY-MM-DD')
ORDER BY t.date_created DESC;