如何使用datepart选项在postgres中获取不同的日期

Question

如何在PostgreSQL 中获取日期时差

我正在使用以下语法

select id, A_column,B_column, 
(SELECT count(*) AS count_days_no_weekend 
FROM generate_series(B_column ::timestamp , A_column ::timestamp, interval  '1 day') the_day 
WHERE  extract('ISODOW' FROM the_day) < 5) * 24 + DATE_PART('hour', B_column::timestamp-A_column ::timestamp ) as hrs 
FROM table req where id='123'; 

如果A_column=2020-05-20 00:00:00并且B_column=202 0-05-15 00:00:00，我希望得到72(以小时为单位(。

有没有可能跳过第一个周末(周六和周日(，这意味着得到72小时(不包括周末时间(的结果

我得到0

但我需要72小时

如果A_column=2020-08-15 12:00:00和B_column=202 0-08-15 00:00:00，我想得到12(以小时为单位(。

Answer 1

一个选项使用横向连接和generate_series()来枚举两个时间戳之间的每个小时，同时过滤出周末：

select t.a_column, t.b_column, h.count_hours_no_weekend 
from mytable t
cross join lateral (
select count(*) count_hours_no_weekend 
from generate_series(t.b_column::timestamp, t.a_column::timestamp, interval '1 hour') s(col)
where extract('isodow' from s.col) < 5
) h
where id = 123

Answer 2

我会通过计算周末时间来攻击这一点，让数据库处理夏令时。然后，我会从两个日期值之间的差值中减去中间的周末时间。

with weekend_days as (
select *, date_part('isodow', ddate) as dow
from table1
cross join lateral 
generate_series(
date_trunc('day', b_column),
date_trunc('day', a_column),
interval '1 day') as gs(ddate)
where date_part('isodow', ddate) in (6, 7)
), weekend_time as (
select id, 
sum( 
least(ddate + interval '1 day', a_column) -
greatest(ddate, b_column)
) as we_ival
from weekend_days
group by id
)
select t.id, 
a_column - b_column as raw_difference,
coalesce(we_ival, interval '0') as adjustment,
a_column - b_column - 
coalesce(we_ival, interval '0') as adj_difference
from weekend_time w
left join table1 t on t.id = w.id;

工作小提琴。