前天我們通過一段代碼展示了如何通過Python的Pandas包生成一個日曆表，如果各位感興趣可以參考：通過Pandas生成日曆表；

不得不說在 DB Engine（
https://db-engines.com/en/ranking）排行榜中，PostgreSQL最近幾年一直都很穩定，並且在各個雲平台中都有非常成熟的PaaS產品供各個企業應用，那麼我們今天的目標就是演示一下如何通過#postgresql#來生成類似的日曆表。

本文使用的PostgreSQL版本為14.1，Windows Server 2016環境下運行。

DB Engine Ranking

一些重要的功能

GENERATE_SERIES

在完成解決方案之前，我們先了解PostgreSQL的一個函數：「GENERATE_SERIES」，通過字面意思應該能大致猜出來，這個函數可以生成一個序列的數據，直白講就是生成單列的一個數據表。

我們先通過psql命令得到對於此函數的描述，如下圖所示：

GENERATE_SERIES Description in psql

此函數主要有幾個特點：

1. 可以返回整型（bigint,int），浮點類型（numeric）序列；
2. 可以返回帶時區的時間戳類型（timestamp）序列；
3. 可以返回不帶時區的時間戳類型（timestamp）序列；

簡單舉個例子：

1.1 返回整型序列

• 當指定起點，終點參數

SELECT GENERATE_SERIES(1, 10);

結果如下所示：

返回整型序列

• 當指定起點，終點，步長參數

步長可以理解為跳躍值，從1開始，如果步長為2，那麼下一個數字應該是1 + 2 = 3，以此類推；

SELECT GENERATE_SERIES(1, 10, 2);

返回整型序列，指定步長

1.2 返回浮點類型序列

此功能類似於返回整型序列，不同點是傳入的可以是帶小數位的浮點類型數據，如下圖所示：

SELECT GENERATE_SERIES(1.1, 10.9, 0.5);

返回浮點類型序列，指定步長

1.3 返回時間戳（timestamp）序列

此功能可以通過指定起點，終點和步長三個參數，返回一段時間戳的序列數據，如下圖所示：

SELECT GENERATE_SERIES('2022-01-01'::TIMESTAMP, '2022-01-31'::TIMESTAMP, '1 DAY') AS datum;

返回時間戳序列

需要注意的是：

• 必須同時指定三個參數，起點，終點，步長；
• 起點和終點參數，必須是時間戳類型（timestamp），如果傳入的是日期類型，需要顯示轉換；
• 步長可以是小時，分鐘，秒，天，星期，年等；

日期類型數據操作

我們需要記住這一個操作：日期 + 整數 = 日期，如下面例子所示：

date + integer → date
Add a number of days to a date
date '2022-01-09' + 5 → 2022-01-14

Case 1: 當月計算

Case 2: 跨月計算

下面我們結合GENERATE_SERIES 函數實現如何得到一個日期類型的序列；

起點：2022-01-01， 終點：2022-01-31；這兩個時間點中間間隔了30天，通過代碼實現如下：

SELECT '2022-01-01'::DATE + s.a AS datum
FROM GENERATE_SERIES(0, 30) AS s(a);

結果如下圖所示：

通過GENERATE_SERIES函數返回日期類型序列

這種方案的優勢是：

1. 返回日期類型序列，原函數僅支持傳入時間戳類型數據；
2. 不需要按日期時間的Interval指定時間間隔，將時間間隔默認為1天。

自定義函數

通過上面的練習，我們已經能夠通過傳入開始日期和間隔天數得到我們想要的結果。可是如果業務上經常變換開始日期和時間間隔，我們還需要不斷的重寫SQL語句。為了避免重新改寫SQL語句，我們將定義一個函數「get_calendar」，並將「開始日期」（start_dt）和「時間間隔」（days）做為參數傳入，從而使我們的結果和語句更加靈活。

代碼如下所示：

CREATE OR REPLACE FUNCTION public.get_calendar(
	start_dt date,
	days integer)
    RETURNS TABLE(datum date) 
    LANGUAGE 'sql'
	
AS $BODY$

SELECT start_dt + s.a AS datum
FROM GENERATE_SERIES(0, days) AS s(a)
GROUP BY s.a
ORDER BY 1;

$BODY$;

簡單測試一下，依然將『2022-01-01』作為開始日期，時間間隔設置為30天：

SELECT * FROM get_calendar('2022-01-01', 30);

自定義函數讓代碼更加靈活

完整代碼實現

最終，我們將通過PostgreSQL的大量日期和字元串轉換函數，擴展我們的自定義函數「get_calendar」，得到一個完整的日曆表，具體代碼如下所示。

/* Author: Derek Zhu
Date: 2022-01-08
Purpose: Calendar table practice in PostgreSQL 14.1
Description:
    Start date: 2022-01-01
    Set days length in 2nd argument of 'Genarate_series' function */

-- FUNCTION: public.get_calendar(date, integer)

-- DROP FUNCTION IF EXISTS public.get_calendar(date, integer);

CREATE OR REPLACE FUNCTION public.get_calendar(
	start_dt date,
	days integer)
    RETURNS TABLE(datum date, year numeric, month numeric, day_of_month numeric, week_of_year numeric, iso_day_of_week numeric, year_calendar_week text, day_of_year numeric, quarter_of_year numeric, quartal text, year_quartal text, day_name text, month_name text, year_month text, year_half integer, leap_year boolean, weekend text, cw_start date, cw_end date, month_start date, month_end date) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

SELECT
  datum,
  EXTRACT(YEAR FROM datum) AS "year",
  EXTRACT(MONTH FROM datum) AS "month",
  EXTRACT(DAY FROM datum) AS day_of_month,
  EXTRACT(WEEK FROM datum) AS week_of_year,
  -- ISO 8601 day of the week numbering, The day of the week as Monday (1) to Sunday (7)
  EXTRACT(ISODOW FROM datum) AS iso_day_of_week,
  -- Standard Gregorian day of the week numbering, The day of the week as Sunday (0) to Saturday (6)
  -- EXTRACT(DOW FROM datum) AS day_of_week,
  -- ISO calendar year and week
  TO_CHAR(datum, 'iyyy/IW') AS year_calendar_week,
  EXTRACT(DOY FROM datum) AS day_of_year,
  EXTRACT(QUARTER FROM datum) AS quarter_of_year,
  'Q' || TO_CHAR(datum, 'Q') AS quartal,
  TO_CHAR(datum, 'yyyy/"Q"Q') AS year_quartal,
  TO_CHAR(datum, 'TMDay') AS day_name,
  TO_CHAR(datum, 'TMMonth') AS month_name,
  TO_CHAR(datum, 'yyyy/mm') AS year_month,
  -- Half year
  CASE WHEN EXTRACT(MONTH FROM datum) < 7 THEN 1 ELSE 2 END AS year_half,
  -- Leap year
  CASE WHEN EXTRACT(YEAR FROM datum) % 4 = 0 THEN TRUE ELSE FALSE END AS leap_year,
  -- Weekend
  CASE WHEN EXTRACT(ISODOW FROM datum) in (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS weekend,
  -- ISO start and end of the week of this date
  datum + (1 - EXTRACT(ISODOW FROM datum))::integer AS cw_start,
  datum + (7 - EXTRACT(ISODOW FROM datum))::integer AS cw_end,
  -- Start and end of the month of this date
  datum + (1 - EXTRACT(DAY FROM datum))::integer AS month_start,
  ((datum + (1 - EXTRACT(DAY FROM datum))::integer + '1 month'::interval)::date - '1 day'::interval)::DATE AS month_end
FROM (
	SELECT start_dt + s.a AS datum
FROM GENERATE_SERIES(0, days) AS s(a)
GROUP BY s.a
) AS calendar
ORDER BY 1;

$BODY$;

ALTER FUNCTION public.get_calendar(date, integer)
    OWNER TO postgres;

得到2022年全年日曆，如下所示：

SELECT * FROM get_calendar('2022-01-01', 364);

2022 Calendar Table

通過文本編輯器觀察結果，如下所示：

Calendar data in csv

通過Excel觀察結果，如下所示：

Calendar data in excel

至此，我們已經完成了所有功能；

總結

通過PostgreSQL生成日曆表主要有下面幾個注意點：

1. ISO8601標準中，一個星期的天數為：Monday (1) ~ Sunday (7)；
2. 理解並靈活應用GENERATE_SERIES函數生成日期序列；
3. 養成模塊化思維習慣，將通用的數據操作抽象為函數或方法，能夠擴展應用範圍；

與前日通過Python Pandas的案例一樣，我們最終也將解決方案抽象為一個函數，供後期靈活調用，雖然傳入的參數和最終的結果不完全一致，但是整體思路是類似的。

想對自己說的話

PostgreSQL 目前在很多企業都在大量使用，通過PG集群搭建數據倉庫平台也是很多企業近些年在努力做的實現，去IOE早已執行多年，使用開源軟體替換商用軟體也是大勢所趨，PG應該被重視起來，對於PG的一些常見和重要的操作，也要應該熟記於心。

至於MySQL和PG選哪個這種神仙打架的事情，真沒時間想那麼多，純開源，還是PG吧~