我正在寻找在postgres数据库中创建日期表。样本数据应如下所示：
date key = 00001 calendar_date= 1/1/2015 week_num= 1 month_num= 1 month_name= Jan quarter_num= 1 calendar_year= 2015 iso_dayofweek= 4 dayofweek_name= Thursday
是否有我可以获取帮助的函数或SQL创建日期公制ISO-8601表？ 我正在寻找自动生成如果可能的话。在这个方向上的任何帮助将不胜感激。
见下面的例子
SELECT mydate calendar_date ,EXTRACT(WEEK FROM mydate) week_num ,EXTRACT(month FROM mydate) month_num ,to_char(mydate,'Mon') month_name ,EXTRACT(Quarter FROM mydate) quarter_num ,EXTRACT(year FROM mydate) calendar_year ,EXTRACT(DOW FROM mydate) iso_dayofweek ,to_char(mydate, 'day') dayofweek_name FROM ( SELECT now()::DATE mydate ) t
结果：
calendar_date week_num month_num month_name quarter_num calendar_year iso_dayofweek dayofweek_name ------------- -------- --------- ---------- ----------- ------------- ------------- -------------- 2015/04/24 17 4 Apr 2 2015 5 friday
您可以generate_series()用来获取一年中所有日期，例如：2015
generate_series()
2015
select generate_series(0,364) + date'1/1/2015'
这将产生来自的日期1/1/2015 - 31/12/2015，并使用此 select 代替SELECT now()::DATE给定的示例
1/1/2015 - 31/12/2015
SELECT now()::DATE
如果要创建年份表，2015则可以使用以下查询
CREATE TABLE mycal_2015 AS SELECT row_number() OVER () date_key ,mydate calendar_date ,EXTRACT(WEEK FROM mydate) week_num ,EXTRACT(month FROM mydate) month_num ,to_char(mydate,'Mon') month_name ,EXTRACT(Quarter FROM mydate) quarter_num ,EXTRACT(year FROM mydate) calendar_year ,EXTRACT(DOW FROM mydate) iso_dayofweek ,to_char(mydate, 'day') dayofweek_name FROM ( SELECT generate_series(0, 364) + DATE '1/1/2015' mydate ) t
桌子看起来像 select * from mycal_2015
select * from mycal_2015
date_key calendar_date week_num month_num month_name quarter_num calendar_year iso_dayofweek dayofweek_name -------- ------------- -------- --------- ---------- ----------- ------------- ------------- -------------- 1 2015/01/01 1 1 Jan 1 2015 4 thursday 2 2015/01/02 1 1 Jan 1 2015 5 friday 3 2015/01/03 1 1 Jan 1 2015 6 saturday 4 2015/01/04 1 1 Jan 1 2015 0 sunday 5 2015/01/05 2 1 Jan 1 2015 1 monday 6 2015/01/06 2 1 Jan 1 2015 2 tuesday ... . . . 364 2015/12/30 53 12 Dec 4 2015 3 wednesday 365 2015/12/31 53 12 Dec 4 2015 4 thursday
POSTGRESQL：提取功能
PostgreSQL提取函数从日期中提取部分
句法 ： extract( unit from date )
extract( unit from date )
date是要从中提取日期部分的日期，时间戳记，时间或间隔值。 unit是间隔的单位类型，例如日，月，分钟，小时等。
date是要从中提取日期部分的日期，时间戳记，时间或间隔值。
unit是间隔的单位类型，例如日，月，分钟，小时等。
可以是以下之一：
unit description --------------- ----------------------------------------------------------------------------------------------------------------------------- century Uses the Gregorian calendar where the first century starts at '0001-01-01 00:00:00 AD' day Day of the month (1 to 31) decade Year divided by 10 dow Day of the week (0=Sunday, 1=Monday, 2=Tuesday, ... 6=Saturday) doy Day of the year (1=first day of year, 365/366=last day of the year, depending if it is a leap year) epoch Number of seconds since '1970-01-01 00:00:00 UTC', if date value. Number of seconds in an interval, if interval value hour Hour (0 to 23) isodow Day of the week (1=Monday, 2=Tuesday, 3=Wednesday, ... 7=Sunday) isoyear ISO 8601 year value (where the year begins on the Monday of the week that contains January 4th) microseconds Seconds (and fractional seconds) multiplied by 1,000,000 millennium Millennium value milliseconds Seconds (and fractional seconds) multiplied by 1,000 minute Minute (0 to 59) month Number for the month (1 to 12), if date value. Number of months (0 to 11), if interval value quarter Quarter (1 to 4) second Seconds (and fractional seconds) timezone Time zone offset from UTC, expressed in seconds timezone_hour Hour portion of the time zone offset from UTC timezone_minute Minute portion of the time zone offset from UTC week Number of the week of the year based on ISO 8601 (where the year begins on the Monday of the week that contains January 4th) year Year as 4-digits
注意：提取功能适用于PostgreSQL 8.4及更高版本
日期/时间函数和运算符 generate_series（）
