這個在弄一個每次倒數計時的活動，想到的@@~
就是每5天自動算出下一個第5天的日期
例如基準日是5/21
假設日期是5/23
得到第5天的日期是5/26
但若假設日期是5/28
得到下一個第5天的日期是5/31
這個算是數學計算+SQL應用吧，看各位大大會如何解~
我是已經解好的..
--這是我初期設定
declare @basedate date = '2019/5/21'
declare @Setdate date = '2019/5/28'
declare @n int = 5
補~我原先的SQL
declare @basedate date = '2019/5/21'
declare @Setdate date = '2019/5/28'
declare @n int = 5
select (
case when DayNum % @n = 0
then dateadd(d,Convert(int,DayNum / @n) * @n,@basedate)
else dateadd(d,Convert(int,DayNum / @n) * @n + @n,@basedate)
end
) as CheckDate
from (
select datediff(d,@basedate,@Setdate) as DayNum
) as k
已邀請的邦友 {{ invite_list.length }}/5
我直覺寫法使用datediff + mod + dateadd來解決
--這是我初期設定
declare @basedate date = '2019/5/21';
declare @Setdate date = '2019/5/28';
declare @n int = 5;
declare @addDay int = @n - (datediff(day,@basedate,@Setdate)) % ( @n ); --藉由日期間隔 - (輸入日期與基本日期天數差 Mod 日期間隔) 得出與下一個日期天數差
select @addDay diffDay,dateadd(day,@addDay,@Setdate) as CheckDay --輸入日期加上前面的天數差得到結果
假設日期是5/23
得到第5天的日期是5/26
但若假設日期是5/28
得到下一個第5天的日期是5/31
這怎麼算5天的??
其實SQL本身就有函式幫你計算日期,
你不用自己處理.
他有說基準日5/21呀
日期在 5/21 到 5/26 => 5/26
日期在 5/26 到 5/31 => 5/31
dragonH
對唷~是自己自訂一個日期開始~
喔喔, 懂了, 是從5/21開始算就是了...
-- 使用 Postgresql rangetype 及其運算子,函數.
-- 產生 daterange 型態 ; 含起始日,不含終止日
-- 舉第一個range
-- [2019-05-21,2019-05-26) <== 含 0521, 不含 0526 ;
-- 從 2019-05-21 00:00:00 含此時刻 及之後
-- 2019-05-26 00:00:00 <- 此一時刻不含,之前均有包含.
with t1 as (
select n+1 as "dt_id"
, (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
, (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
, st_dt
, daterange(st_dt, ed_dt, '[)') as "days_range"
from t1
)
select *
from t2
;
+-------+------------+-------------------------+
| dt_id | st_dt | days_range |
+-------+------------+-------------------------+
| 1 | 2019-05-21 | [2019-05-21,2019-05-26) |
| 2 | 2019-05-26 | [2019-05-26,2019-05-31) |
| 3 | 2019-05-31 | [2019-05-31,2019-06-05) |
| 4 | 2019-06-05 | [2019-06-05,2019-06-10) |
| 5 | 2019-06-10 | [2019-06-10,2019-06-15) |
| 6 | 2019-06-15 | [2019-06-15,2019-06-20) |
+-------+------------+-------------------------+
(6 rows)
-------------
-- 使用 <@ 包含於 運算子
-- 計算 date '2019-05-23' 包含於 何 daterange
with t1 as (
select n+1 as "dt_id"
, (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
, (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
, st_dt
, daterange(st_dt, ed_dt, '[)') as "days_range"
from t1
)
select *
, date '2019-05-23' <@ days_range as "is_include"
from t2;
+-------+------------+-------------------------+------------+
| dt_id | st_dt | days_range | is_include |
+-------+------------+-------------------------+------------+
| 1 | 2019-05-21 | [2019-05-21,2019-05-26) | t |
| 2 | 2019-05-26 | [2019-05-26,2019-05-31) | f |
| 3 | 2019-05-31 | [2019-05-31,2019-06-05) | f |
| 4 | 2019-06-05 | [2019-06-05,2019-06-10) | f |
| 5 | 2019-06-10 | [2019-06-10,2019-06-15) | f |
| 6 | 2019-06-15 | [2019-06-15,2019-06-20) | f |
+-------+------------+-------------------------+------------+
(6 rows)
----------
-- 使用 upper() 函數 , 找出 upper bound of range
with t1 as (
select n+1 as "dt_id"
, (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
, (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
, st_dt
, daterange(st_dt, ed_dt, '[)') as "days_range"
from t1
)
select *
, upper(days_range)
from t2
where date '2019-05-23' <@ days_range;
+-------+------------+-------------------------+------------+
| dt_id | st_dt | days_range | upper |
+-------+------------+-------------------------+------------+
| 1 | 2019-05-21 | [2019-05-21,2019-05-26) | 2019-05-26 |
+-------+------------+-------------------------+------------+
(1 row)
----------
-- check date '2019-05-28'
with t1 as (
select n+1 as "dt_id"
, (date '2019-05-21' + interval '5 day' * n)::date as "st_dt"
, (date '2019-05-21' + interval '5 day' * (n+1))::date as "ed_dt"
from generate_series(0, 5) as g(n)
), t2 as (
select dt_id
, st_dt
, daterange(st_dt, ed_dt, '[)') as "days_range"
from t1
)
select *
, upper(days_range)
from t2
where date '2019-05-28' <@ days_range;
+-------+------------+-------------------------+------------+
| dt_id | st_dt | days_range | upper |
+-------+------------+-------------------------+------------+
| 2 | 2019-05-26 | [2019-05-26,2019-05-31) | 2019-05-31 |
+-------+------------+-------------------------+------------+
(1 row)
iThome鐵人賽
看影片追技術