10-318 最も近い30分単位の時間に変換

SQLパズル

TimeRoundテーブル
dt
-------------------
2009/12/28 11:08:19
2009/12/28 11:18:19
2009/12/28 11:15:00
2009/12/28 11:38:19
2009/12/28 11:45:00
2009/12/28 11:48:19

最も近い30分単位の時間に変換する。

出力結果
DT                   NEW
-------------------  -------------------
2009-12-28 11:08:19  2009-12-28 11:00:00
2009-12-28 11:15:00  2009-12-28 11:30:00
2009-12-28 11:18:19  2009-12-28 11:30:00
2009-12-28 11:38:19  2009-12-28 11:30:00
2009-12-28 11:45:00  2009-12-28 12:00:00
2009-12-28 11:48:19  2009-12-28 12:00:00

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table TimeRound(dt) as
select to_date('2009/12/28 11:08:19','yyyy/mm/dd hh24:mi:ss') from dual union
select to_date('2009/12/28 11:18:19','yyyy/mm/dd hh24:mi:ss') from dual union
select to_date('2009/12/28 11:15:00','yyyy/mm/dd hh24:mi:ss') from dual union
select to_date('2009/12/28 11:38:19','yyyy/mm/dd hh24:mi:ss') from dual union
select to_date('2009/12/28 11:45:00','yyyy/mm/dd hh24:mi:ss') from dual union
select to_date('2009/12/28 11:48:19','yyyy/mm/dd hh24:mi:ss') from dual;

SQL

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

--■■■case式を使う方法1■■■
select dt,
case when to_char(dt,'mi') between '00' and '14'
     then trunc(dt,'HH24')
     when to_char(dt,'mi') between '15' and '44'
     then trunc(dt,'HH24') + interVal '30' minute
     when to_char(dt,'mi') between '45' and '59'
     then trunc(dt,'HH24') + interVal '60' minute
     end as new
from TimeRound;

--■■■case式を使う方法2■■■
select dt,
case when to_char(dt,'mi') between '15' and '44'
     then trunc(dt,'hh24') + interVal '30' minute
     else round(dt,'hh24') end as new
from TimeRound;

解説

TimeStamp型に対するextract関数は、yearやmonthやdayやhourやminuteやsecondを引数にできますが、
date型に対するextract関数は、yearとmonthとdayしか引数にできないので、to_charで分を取得してます。

マニュアル --- EXTRACT（日時）

---