Creating Date and Time dimensions for your data warehouse

This would give you a starting date dimension from 2000-01-01 to 2009-12-31 with useful fields. (Just adjust the starting date and the count in the SELECT at the end of the statement to change the date range.)

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 recordsSELECT'2000-01-01'::DATE + sequence.day AS datum
FROM generate_series(0,3652)AS sequence(day)GROUPBY sequence.day
) DQ
ORDERBY1

You can use this to get a time of day dimension:

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)GROUPBY sequence.minute
) DQ
ORDERBY1