如何在postgres中创建ISO

本文介绍了如何在postgres中创建ISO-8601公历日期表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在寻找在postgres数据库中创建日期表的方法。 样本数据应如下所示：

I'm looking to create a date table in a postgres database. The sample data is expected to look like this:

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表？如果可能，我希望自动生成此表。

Is there a function or SQL that I can get help with to create a date gregorian ISO-8601 table? I am looking to auto generate this if possible. Any help in this direction would be appreciated.

推荐答案

请参见下面的示例

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

您可以使用 generat e_series（）获取一年中的所有日期，例如： 2015

You can use generate_series() to get all date in a year for ex: 2015

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

这会产生从 2015年1月1日至2015年12月31日的日期，并使用此 select 而不是在给定的示例中选择now（）:: DATE

this will produce date from 1/1/2015 - 31/12/2015, and use this select instead of SELECT now()::DATE in the given example

如果您要创建2015年的表格，则可以使用以下查询

If you want to create table for year 2015 then you can use the following query

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提取函数从日期中提取部分

The PostgreSQL extract function extracts parts from a date

语法： extract（从日期开始的单位）

date是要从中提取日期部分的日期，时间戳记，时间或间隔值。

date is a date, timestamp, time, or interval value from which the date part is to be extracted.

unit是间隔的单位类型，例如日，月，分钟，小时等等

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

注意：提取功能适用于8.4及以上版本的PostgreSQL

日期/时间函数和运算符 generate_series（）