Date and Time dimensions

SELECT
	datum AS Date,
	extract(year FROM datum) AS Year,
	extract(month FROM datum) AS Month,
	-- Localized month name
	to_char(datum, 'TMMonth') AS MonthName,
	extract(day FROM datum) AS Day,
	extract(doy FROM datum) AS DayOfYear,
	-- Localized weekday
	to_char(datum, 'TMDay') AS WeekdayName,
	-- ISO calendar week
	extract(week FROM datum) AS CalendarWeek,
	to_char(datum, 'dd. mm. yyyy') AS FormattedDate,
	'Q' || to_char(datum, 'Q') AS Quartal,
	to_char(datum, 'yyyy/"Q"Q') AS YearQuartal,
	to_char(datum, 'yyyy/mm') AS YearMonth,
	-- ISO calendar year and week
	to_char(datum, 'iyyy/IW') AS YearCalendarWeek,
	-- Weekend
	CASE WHEN extract(isodow FROM datum) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS Weekend,
	-- Fixed holidays 
        -- for America
        CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0704', '1225', '1226')
		THEN 'Holiday' ELSE 'No holiday' END
		AS AmericanHoliday,
        -- for Austria
	CASE WHEN to_char(datum, 'MMDD') IN 
		('0101', '0106', '0501', '0815', '1101', '1208', '1225', '1226') 
		THEN 'Holiday' ELSE 'No holiday' END 
		AS AustrianHoliday,
        -- for Canada
        CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0701', '1225', '1226')
		THEN 'Holiday' ELSE 'No holiday' END 
		AS CanadianHoliday,
	-- Some periods of the year, adjust for your organisation and country
	CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831' THEN 'Summer break'
	     WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225' THEN 'Christmas season'
	     WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106' THEN 'Winter break'
		ELSE 'Normal' END
		AS Period,
	-- ISO start and end of the week of this date
	datum + (1 - extract(isodow FROM datum))::integer AS CWStart,
	datum + (7 - extract(isodow FROM datum))::integer AS CWEnd,
	-- Start and end of the month of this date
	datum + (1 - extract(day FROM datum))::integer AS MonthStart,
	(datum + (1 - extract(day FROM datum))::integer + '1 month'::interval)::date - '1 day'::interval AS MonthEnd
FROM (
	-- There are 3 leap years in this range, so calculate 365 * 10 + 3 records
	SELECT '2000-01-01'::DATE + sequence.day AS datum
	FROM generate_series(0,3652) AS sequence(day)
	GROUP BY sequence.day
     ) DQ
ORDER BY 1

SELECT to_char(minute, 'hh24:mi') AS TimeOfDay,
	-- Hour of the day (0 - 23)
	extract(hour FROM minute) AS Hour, 
	-- Extract and format quarter hours
	to_char(minute - (extract(minute FROM minute)::integer % 15 || 'minutes')::interval, 'hh24:mi') ||
	' – ' ||
	to_char(minute - (extract(minute FROM minute)::integer % 15 || 'minutes')::interval + '14 minutes'::interval, 'hh24:mi')
		AS QuarterHour,
	-- Minute of the day (0 - 1439)
	extract(hour FROM minute)*60 + extract(minute FROM minute) AS minute,
	-- Names of day periods
	case when to_char(minute, 'hh24:mi') BETWEEN '06:00' AND '08:29'
		then 'Morning'
	     when to_char(minute, 'hh24:mi') BETWEEN '08:30' AND '11:59'
		then 'AM'
	     when to_char(minute, 'hh24:mi') BETWEEN '12:00' AND '17:59'
		then 'PM'
	     when to_char(minute, 'hh24:mi') BETWEEN '18:00' AND '22:29'
		then 'Evening'
	     else 'Night'
	end AS DaytimeName,
	-- Indicator of day or night
	case when to_char(minute, 'hh24:mi') BETWEEN '07:00' AND '19:59' then 'Day'
	     else 'Night'
	end AS DayNight
FROM (SELECT '0:00'::time + (sequence.minute || ' minutes')::interval AS minute
	FROM generate_series(0,1439) AS sequence(minute)
	GROUP BY sequence.minute
     ) DQ
ORDER BY 1