这里接着上一篇,还是想在这次继续深入聊聊 GENERATE_SERIES
这个函数以及其他用法,由于最近一直忙于工作,也没有时间来深入学习、使用、总结这个函数,每次都潦草学习,终不得要领,所以得闲的时候就来写一写,学习一下。
近期在做一些数据处理的工作,工作中使用其他项目组平台来做数据开发的比较多,在数据开发过程中,使用了PostgreSQL的一个内置函数 GENERATE_SERIES。在使用过程中遇到问题,一般都是站在巨人的肩膀学习即可,也无需动脑子,慢慢的成了「拿来主义者」。
闲话家常里短这里就不拉了,继续上一篇的学习,下面开始吧。
【函数释义】:数据集函数,按照一定参数规则返回数据集。主要用于生成示例数据或一些有规律的记录,generate_series允许您生成一组从某个点开始,到另一个点结束的数据,并可选择设置递增值。
实验环境很重要,当前测试(实验)环境为PostgreSQL 16.2,所有操作均以这个版本进行测试(这个后续会有说明)。
上一篇也说过,在PostgreSQL的官网有如下三种的用法和描述(integer
、bigint
、numeric
),可以借鉴下上一篇,这里说下日期类型参数。
【语法结构】
generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp
generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval [, timezone text ] ) → setof timestamp with time zone
【参数】
在处理日期类型时,可以将日期转换为整数类型计算,也可以按照日期类型来计算。
【应用场景】
PostgreSQL中的generate_series函数是一个非常强大且灵活的工具,它可以在多种应用场景中生成连续的序列。
在当前日期上按照一定的数值序列进行加减法,例如当前时间为 2024-07-02
,从 0 (当前日期基数)开始,按照步长 1 向上增加(具体的逻辑可以参考下上一篇,逻辑相同,这里主要是借助了generate_series函数自增的特性,如果从 1 开始,那么就不算当前日期)。
2024-07-02
2024-07-03
2024-07-04
2024-07-05
2024-07-06
2024-07-07,不符合在数据集内,所以舍去,那么这个时间结果集就为上述所示。
【代码示例】
-- 在当前日期上增加数值
SELECT current_date + gs.serial_num AS dates FROM generate_series(0,4,1) AS gs(serial_num);
其他例子也挺好,可以根据自身需求来设置或制作可以使用的参数,具体可以参考如下:
-- 在参数日期上增加数值(如果是在Navicat中可以执行,其他工具酌情处理修改后执行)
SELECT (CAST([${year_time}] as date) + gs.serial_num) AS dates FROM generate_series(0,4,1) AS gs(serial_num);
-- 可以在查询时直接应用数据集
SELECT generate_series('2024-07-02'::date, '2024-12-31'::date, '1 month'::interval);
-- 写法不限制,怎么写都是可以的,例如下面的方式
SELECT generate_series(CURRENT_DATE, CURRENT_DATE + INTERVAL '6 days', INTERVAL '1 day');
时间戳上数值加减法,这里利用了日期的周期性。例如从开始时间戳到结束时间戳,按照某一个周期进行加减法。从 2024-07-02 00:00
到 2024-07-10 00:00
,按照步长 1 day
的周期进行增加后的结果如下:
从 2024-07-02 00:00:00 开始,判断在数据集内,罗列结果:
2024-07-02 00:00:00
2024-07-03 00:00:00
2024-07-04 00:00:00
2024-07-05 00:00:00
2024-07-06 00:00:00
2024-07-07 00:00:00
2024-07-08 00:00:00
2024-07-09 00:00:00
2024-07-10 00:00:00
2024-07-11 00:00:00 ,不符合在数据集内,所以舍去,那么这个时间结果集就为上述所示。
-- 时间戳上数值加减法(这里按照天计算),这里利用了日期的周期性
SELECT * FROM generate_series('2024-07-02 00:00'::timestamp, '2024-07-10 00:00', '1 day');
-- 时间戳上数值加减法(这里按照月计算)
SELECT * FROM generate_series('2024-07-02 00:00'::timestamp, '2024-10-10 00:00', '1 MONTH');
当然可以根据时间周期性计算 年、月、日、时、分、秒
,关键字分别为:year、month、day、hour、minute、seconds
,如果没有特别设置,不区分大小写,虽然偶尔写,有些时间也难记住这些单词,这里就不再赘述。
生成从开始到停止的一系列值,步长为步长。在时区感知形式中,根据时区参数命名的时区计算一天中的时间和夏令时调整,如果省略,则根据当前的时区设置计算。一般情形下,这种使用方法很少,除非这是一些跨国业务需要处理。
SELECT * FROM generate_series('2024-07-02 08:00 -10:00'::timestamptz,
'2024-07-08 10:00 -1:00'::timestamptz,
'1 day'::interval, 'Asia/Chungking');
注意⚠️:
1、如果不知道如何获取时区,可以查询系统中已有的时区列表,默认情况下是使用UTC。
SELECT name FROM pg_timezone_names ORDER BY name;
-- 部分数据如下
……
-- 重庆
Asia/Chongqing
Asia/Chungking
-- 香港
Asia/Hong_Kong
-- 澳门
Asia/Macau
-- 上海
Asia/Shanghai
-- 台北
Asia/Taipei
……
2、在时间区间中使用的是 12H
制度,也就是我们常说的AP和PM。
遇事千万不要慌,多看看官网,多找找原因,实在不行多翻一翻其他很好的的博文,总会找到有能帮助你的那一篇。下一篇总结下其他参数的使用方法。
[引用]
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。