查询时间间隔

查询时间间隔天数

方法一

• 建表加数据

create temp table test_date ( start_time varchar, end_time varchar);
insert into test_date values('2019-01-01 12:12:12', '2019-02-12 23:11:11');
insert into test_date values('2019-01-02 12:12:12', '2019-03-12 03:11:11');
select to_date(end_time, 'yyyy-mm-dd')- to_date(start_time, 'yyyy-mm-dd') as interval_days from test_date;

• 查看结果

postgres=# select to_date(end_time, 'yyyy-mm-dd')- to_date(start_time, 'yyyy-mm-dd') as interval_days from test_date;
 interval_days
---------------
            42
            69
(2 rows)

方法二

postgres=# select extract(day from ( timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'));
 date_part
-----------
         1
(1 row)

查询时间间隔中的年月日

select age('2010-04-01', '2012-03-05'),
       date_part('year',age('2010-04-01', '2012-03-05')),
       date_part('month',age('2010-04-01', '2012-03-05')),
       date_part('day',age('2010-04-01', '2012-03-05'));

结果：

计算时间差秒数

select extract(epoch FROM (now() - (now()-interval '1 day') ));

计算时间差分钟数

 select extract(epoch FROM (now() - (now()-interval '1 day') ))/60.0;

结果：