gpt4 book ai didi

sql - 修剪时间超出范围

转载 作者:行者123 更新时间:2023-11-29 11:39:27 25 4
gpt4 key购买 nike

我使用 PostgreSQL,如果日期超出范围,我很乐意修剪它。

首先我想过滤范围外的记录。下面的查询并不难。 (筛选2017/3/1 ~ 2017/3/31)

select * from subscriptions where current_period_start_at <= date '2017/03/01' AND current_period_end_at >= date '2017/03/31'

但是,如果日期超出范围(2017-03-1 ~ 2017-03-31),我很乐意将其截断。current_period_start_at: 2017/02/25 => 2017/03/01current_period_end_at: 2017/04/02 => 2017/03/31

这里是一些例子。

enter image description here

target 2016/08/05 ~ 2016/09/15 
range①  2016/07/01 ~ 2016/08/31 => 2016/08/05 ~ 2016/08/31
range②  2016/08/10 ~ 2016/09/20 => 2016/08/10 ~ 2016/09/15
range③  2016/09/15 ~ 2016/10/10 => x out of the scope
range④  2016/08/01 ~ 2016/09/30 => 2016/08/05 ~ 2016/09/15

最佳答案

您可以使用 least() 函数获取较低的值:

select  greatest(current_period_start_at, date '2016-08-05') as range_start, 
least(current_period_end_at, date '2016-09-15') as range_end
from subscriptions
where (current_period_start_at,current_period_end_at)
overlaps (date '2016-08-05', date '2016-09-15');

如果不想重复日期值,可以使用普通表表达式:

with range (r_start, r_end) as (
values (date '2016-08-05', date '2016-09-15')
)
select greatest(current_period_start_at, r_start) as range_start,
least(current_period_end_at, r_end) as range_end
from subscriptions, range
where (current_period_start_at,current_period_end_at)
overlaps (r_start, r_end);

关于sql - 修剪时间超出范围,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42548625/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com