我想在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,'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()获取ex：2015中一年中的所有日期
select generate_series(0,364) + date'1/1/2015'
这将生成2015年1月1日至2015年12月31日的日期,并在给定的示例中使用此选择而不是SELECT now():: DATE
如果要为2015年创建表,则可以使用以下查询
CREATE TABLE mycal_2015 AS
SELECT row_number() OVER () date_key,mydate calendar_date,'day') dayofweek_name
FROM (
SELECT generate_series(0,364) + DATE '1/1/2015' mydate
) t
并且该表看起来像mycal_2015中的select *
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
语法：extract(来自日期的单位)
date is a date,timestamp,time,or interval value from which the date
part is to be extracted.unit is the unit type of the interval such as day,month,minute,
hour,and so on
它可以是以下之一：
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,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