Generate a Time Table

Every mature database needs a date/time table in order to properly manage dates and times. It is way better to have a preloaded table with all possible date formats you might possibly need. See (and feel free to use) the script below.

This script will generate details to the minute.

/*
CREATE TABLE d_date_time 
PARTITION BY RANGE(date_time)
INTERVAL (NumToYmInterlal (1, 'MONTH') -- 43800 records (minutes) in a month
(
  PARTITION p_init VALUES LESS THEN (DATE '3000-01-01')
)
AS
*/
SELECT time_key as date_time
, to_char(time_key, 'YYYY/MM/DD HH24:MI:SS') as time_key
, (time_key - to_date('01/01/1970', 'MM/DD/YYYY')) * 60 * 60 * 24 as unix_timestamp
-- HOURS
, to_char(time_key, 'HH24') as hour_24
, to_char(time_key, 'HH') as hour_12
, to_char(time_key, 'PM') as am_pm
, CASE
    WHEN to_char(time_key, 'MI') BETWEEN 0 AND 29 THEN '1'
    WHEN to_char(time_key, 'MI') BETWEEN 30 AND 59 THEN '2'
    ELSE '0'
  END as hour_half
, CASE
   WHEN to_char(time_key, 'MI') BETWEEN 0 AND 29 THEN '0-29'
   WHEN to_char(time_key, 'MI') BETWEEN 30 AND 59 THEN '30-59'
   ELSE '0'
  END as hour_half_range
, CASE
   WHEN to_char(time_key, 'MI') BETWEEN 0 AND 14 THEN '1'
   WHEN to_char(time_key, 'MI') BETWEEN 15 AND 29 THEN '2'
   WHEN to_char(time_key, 'MI') BETWEEN 30 AND 44 THEN '3'
   WHEN to_char(time_key, 'MI') BETWEEN 45 AND 59 THEN '4'
   ELSE '0'
  END as hour_quarter
, CASE
   WHEN to_char(time_key, 'MI') BETWEEN 0 AND 14 THEN '0-14'
   WHEN to_char(time_key, 'MI') BETWEEN 15 AND 29 THEN '15-29'
   WHEN to_char(time_key, 'MI') BETWEEN 30 AND 44 THEN '30-44'
   WHEN to_char(time_key, 'MI') BETWEEN 45 AND 59 THEN '45-59'
   ELSE '0'
  END as hour_quarter_range
-- MINUTES
, to_char(time_key, 'MI') as min
, CASE
   WHEN to_char(time_key, 'SS') BETWEEN 0 AND 29 THEN '1'
   WHEN to_char(time_key, 'SS') BETWEEN 30 AND 59 THEN '2'
   ELSE '0'
  END as min_half
, CASE
   WHEN to_char(time_key, 'SS') BETWEEN 0 AND 29 THEN '0-29'
   WHEN to_char(time_key, 'SS') BETWEEN 30 AND 59 THEN '30-59'
   ELSE '0'
  END as min_half_range
-- DAYS
, trunc(time_key) as day
, to_char(time_key, 'YYYY/MM/DD') as date_key
, to_char(time_key, 'DD') as daynum
, to_char(time_key-1, 'D') as daynum_in_week
, to_char(time_key, 'DDD') as daynum_in_year
, to_char(time_key, 'DAY') as day_name
-- WEEKS
, CASE
   WHEN to_char(time_key-1, 'D') IN (6,7) THEN 1
   ELSE 0
  END as is_weekend
, to_char(time_key, 'iW') as week_number
, next_day(time_key-7, 'MONDAY') as week_start
, next_day(time_key-1, 'SUNDAY') as week_end
, to_char(next_day(time_key-7, 'MONDAY'), 'MON DD') || ' - ' || to_char(next_day(time_key-1, 'SUNDAY'), 'MON DD') as week_range
-- MONTHS
, to_char(time_key, 'YYYY/MM') as year_month
, to_char(time_key, 'MM') as month_number
, to_char(time_key, 'MON') as month_name
-- QUARTERS
, CASE
   WHEN to_char(time_key, 'MM') IN (1,2,3) THEN '1'
   WHEN to_char(time_key, 'MM') IN (4,5,6) THEN '2'
   WHEN to_char(time_key, 'MM') IN (7,8,9) THEN '3'
   WHEN to_char(time_key, 'MM') IN (10,11,12) THEN '4'
   ELSE 'N/A'
  END as quarter
, CASE
   WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
   WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
   WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
   WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
   ELSE 'N/A'
  END as quarter_str
, to_char(time_key, 'YYYY') || '/' || CASE
                                          WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
                                          WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
                                          WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
                                          WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
                                          ELSE 'N/A'
                                      END as year_quarter
-- YEAR
, to_char(time_key, 'YYYY') as year
, DECODE(to_char(last_day(to_date( '01-FEB-'|| to_char(time_key, 'YYYY'))), 'DD'),'29', 1,0) as leap_year_flag
FROM (
      SELECT to_date('10/1/2013 00:00:00', -- start datetime
                     'MM/DD/YYYY HH24:MI:SS')-1/24/60 + level/24/60 as time_key
      FROM dual
      CONNECT BY level <= 1440 -- minute interval 
     )
WHERE 1=1
;

Or just a simple day table

--CREATE TABLE d_date AS
SELECT -- DAYS
  trunc(time_key) as day
, to_char(time_key, 'YYYY/MM/DD') as date_key
, to_char(time_key, 'DD') as daynum
, to_char(time_key-1, 'D') as daynum_in_week
, to_char(time_key, 'DDD') as daynum_in_year
, to_char(time_key, 'DAY') as day_name
-- WEEKS
, CASE
    WHEN to_char(time_key-1, 'D') IN (6,7) THEN 1
    ELSE 0
  END as is_weekend
, to_char(time_key, 'iW') as week_number
, next_day(time_key-7, 'MONDAY') as week_start 
, next_day(time_key-1, 'SUNDAY') as week_end
, to_char(next_day(time_key-7, 'MONDAY'), 'MON DD') || ' - ' || to_char(next_day(time_key-1, 'SUNDAY'), 'MON DD') as week_range
-- MONTHS
, to_char(time_key, 'YYYY/MM') as year_month
, to_char(time_key, 'MM') as month_number
, to_char(time_key, 'MON') as month_name
-- QUARTERS
, CASE
    WHEN to_char(time_key, 'MM') IN (1,2,3) THEN '1'
    WHEN to_char(time_key, 'MM') IN (4,5,6) THEN '2'
    WHEN to_char(time_key, 'MM') IN (7,8,9) THEN '3'
    WHEN to_char(time_key, 'MM') IN (10,11,12) THEN '4'
    ELSE 'N/A'
  END as quarter
, CASE
    WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
    WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
    WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
    WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
    ELSE 'N/A'
  END as quarter_str 
, to_char(time_key, 'YYYY') || '/' || CASE
                                          WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
                                          WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
                                          WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
                                          WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
                                          ELSE 'N/A'
                                       END as year_quarter 
-- YEAR 
, to_char(time_key, 'YYYY') as year 
, DECODE(to_char(last_day(to_date( '01-FEB-'|| to_char(time_key, 'YYYY'))), 'DD'),'29', 1, 0) as leap_year_flag
FROM (
       SELECT to_date('10/1/2013', -- start date
                      'MM/DD/YYYY') + level - 1 as time_key
       FROM dual
       CONNECT BY level <= 200 -- number of days 
      )
WHERE 1=1
;


Instead of the┬ánumber of days (so you don’t need to compute it), you can use a date difference between two days.