Oracle Script to "build" a timeofday dimension(by minute)

[developerpete]

I couldn't find exactly what i was looking for, so I put this script together for creating the rows for a time of day dimension.. Hope this helps someone.

select 1 key
, 'No Time Specified' standard_time_desc
, 'No Time Specified' military_time_desc
, 'No Time Specified' am_or_pm_desc
, 'No Time Specified' Regular_Hours_Indicator
from dual
union
select n+1 key
, to_char(trunc(sysdate) + ((n-1)/1440),'HH:MI') standard_time_desc
, to_char(trunc(sysdate) + ((n-1)/1440),'HH24:MI') military_time_desc
, to_char(trunc(sysdate) + ((n-1)/1440),'AM') am_or_pm_desc
, case when to_char(trunc(sysdate) + ((n-1)/1440),'HH24') < '08' then 'Before Regular Business Hours'
when to_char(trunc(sysdate) + ((n-1)/1440),'HH24:MI') > '17:00' then 'After Regular Business Hours'
else 'During Regular Business Hours' end Regular_Hours_Indicator
from (
SELECT LEVEL N, 1 just_a_column FROM dual CONNECT BY LEVEL <= 1440)