sql - 如何在 postgres 中创建 ISO-8601 公历日期表

Question

我想在 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 表？
如果可能，我希望自动生成它。在这方面的任何帮助将不胜感激。

Answer 1

看下面的例子

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

select generate_series(0,364) + date'1/1/2015'

这将产生从 1/1/2015 - 31/12/2015 的日期，并使用此 select 而不是 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

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 )

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, 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                                                                                                             

注意:Extract功能适用于PostgreSQL 8.4及以上版本

Date/Time Functions and Operators
generate_series()