Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
TD_TIME_RANGEを理解して使う

TD_TIME_RANGEを理解して使う

実行時間 3/6/2017 12:00 PM JST やとして
前日1日分のログが欲しい
 イメージ → 2017-03-05 00:00:00 ~ 2017-03-05 23:59:59

select
  min(TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss','JST')) as min
  , max(TD_TIME_FORMAT(time, 'yyyy-MM-dd  HH:mm:ss','JST')) as max
from logs
where TD_TIME_RANGE(
        time
        , TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d')
        , TD_SCHEDULED_TIME()
        , 'JST'
)

結果 2017-03-05 12:00:00 ~ 2017-03-06 11:59:59
↑ 求めてない。。

select
  min(TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss','JST')) as min
  , max(TD_TIME_FORMAT(time, 'yyyy-MM-dd  HH:mm:ss','JST')) as max
from logs
where TD_TIME_RANGE(
        time
        , TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'), 'yyyy-MM-dd','JST')
        , TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-dd','JST')
        , 'JST'
)

結果 2017-03-05 00:00:00 ~ 2017-03-05 23:59:59
↑ これ!!

まぁ当たり前っちゃ当たり前なんですが
時分秒まで意識しないと意図した範囲じゃない。。。

TD_TIME_RANGEは
TD_TIME_RANGE(time,start,end)
start <= time < end
            ↑ endは含まない!! 注意!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment