前天我们通过一段代码展示了如何通过Python的Pandas包生成一个日历表，如果各位感兴趣可以参考：通过Pandas生成日历表；

不得不说在 DB Engine（
https://db-engines.com/en/ranking）排行榜中，PostgreSQL最近几年一直都很稳定，并且在各个云平台中都有非常成熟的PaaS产品供各个企业应用，那么我们今天的目标就是演示一下如何通过#postgresql#来生成类似的日历表。

本文使用的PostgreSQL版本为14.1，Windows Server 2016环境下运行。

C语言日历代码（日历代码实现）-风君小屋帮我吧

DB Engine Ranking

一些重要的功能

GENERATE_SERIES

在完成解决方案之前，我们先了解PostgreSQL的一个函数：“GENERATE_SERIES”，通过字面意思应该能大致猜出来，这个函数可以生成一个序列的数据，直白讲就是生成单列的一个数据表。

我们先通过psql命令得到对于此函数的描述，如下图所示：

C语言日历代码（日历代码实现）-风君小屋帮我吧

GENERATE_SERIES Description in psql

此函数主要有几个特点：

  1. 可以返回整型（bigint,int），浮点类型（numeric）序列；
  2. 可以返回带时区的时间戳类型（timestamp）序列；
  3. 可以返回不带时区的时间戳类型（timestamp）序列；

简单举个例子：

1.1 返回整型序列

  • 当指定起点，终点参数
SELECT GENERATE_SERIES(1, 10);

结果如下所示：

C语言日历代码（日历代码实现）-风君小屋帮我吧

返回整型序列

  • 当指定起点，终点，步长参数

步长可以理解为跳跃值，从1开始，如果步长为2，那么下一个数字应该是1 + 2 = 3，以此类推；

SELECT GENERATE_SERIES(1, 10, 2);

C语言日历代码（日历代码实现）-风君小屋帮我吧

返回整型序列，指定步长

1.2 返回浮点类型序列

此功能类似于返回整型序列，不同点是传入的可以是带小数位的浮点类型数据，如下图所示：

SELECT GENERATE_SERIES(1.1, 10.9, 0.5);

C语言日历代码（日历代码实现）-风君小屋帮我吧

返回浮点类型序列，指定步长

1.3 返回时间戳（timestamp）序列

此功能可以通过指定起点，终点和步长三个参数，返回一段时间戳的序列数据，如下图所示：

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

C语言日历代码（日历代码实现）-风君小屋帮我吧

返回时间戳序列

需要注意的是：

  • 必须同时指定三个参数，起点，终点，步长；
  • 起点和终点参数，必须是时间戳类型（timestamp），如果传入的是日期类型，需要显示转换；
  • 步长可以是小时，分钟，秒，天，星期，年等；

日期类型数据操作

我们需要记住这一个操作：日期 + 整数 = 日期，如下面例子所示：

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

C语言日历代码（日历代码实现）-风君小屋帮我吧

Case 1: 当月计算

C语言日历代码（日历代码实现）-风君小屋帮我吧

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);

结果如下图所示：

C语言日历代码（日历代码实现）-风君小屋帮我吧

通过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);

C语言日历代码（日历代码实现）-风君小屋帮我吧

自定义函数让代码更加灵活

完整代码实现

最终，我们将通过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)

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

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

C语言日历代码（日历代码实现）-风君小屋帮我吧

2022 Calendar Table

通过文本编辑器观察结果，如下所示：

C语言日历代码（日历代码实现）-风君小屋帮我吧

Calendar data in csv

通过Excel观察结果，如下所示：

C语言日历代码（日历代码实现）-风君小屋帮我吧

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吧~