sql – 如何在postgres中创建ISO-8601格里高利日期表

我想在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

POSTGRESQL：提取功能

PostgreSQL提取函数从日期中提取部分

语法：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

注意：Extract函数适用于PostgreSQL版本8.4及更高版本

Date/Time Functions and Operators
generate_series()

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!