SQL使用技巧(4.2)Greenplum和PostgreSQL日期时间函数

已于 2023-04-09 00:41:37 修改
阅读量2.4k 收藏 13
点赞数 1
分类专栏： SQL 大数据 文章标签： sql 数据库
于 2023-04-09 00:39:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/LMTX069/article/details/130028098
版权

专题：SQL使用技巧——实践是检验SQL函数的唯一标准

常用的格式化(format)标识符：

yyyy：四位年份
mm：月份，不足两位补0
dd：日期，不足两位补0
hh24：小时，24小时制，不足两位补0
hh12：小时，12小时制，不足两位补0，后面带 am pm
mi：分钟，不足两位补0
ss：秒，不足两位补0

示例：2019/11/21 05:00:00 pm  -- 格式 'yyyy/mm/dd hh12:mi:ss am'
2019-11-21 17:00:00  -- 格式  'yyyy-mm-dd hh24:mi:ss'

一.常规计算函数说明

本章节每一行代码后都有运算说明和执行结果样例，例如 - - 返回当前系统日期 yyyy-mm-dd， 【2023-04-08】

1.1系统当前日期时间

select 
	current_date -- 返回当前系统日期，格式为yyyy-mm-dd 【2023-04-08】
	,current_time -- 返回当前系统时间，格式为hh24:mi:ss 【14:48:32】
	,current_timestamp -- 返回当前系统时间戳，格式为yyyy-mm-dd hh24:mi:ss 【2023-04-08 14:48:32】
	,now() -- 返回当前系统时间戳，格式为yyyy-mm-dd hh24:mi:ss 【2023-04-08 14:48:32】
	,localtimestamp -- 返回当前系统时间戳，格式为yyyy-mm-dd hh24:mi:ss 【2023-04-08 14:48:32】
	,localtime -- 返回当前系统时间戳，格式为yyyy-mm-dd hh24:mi:ss 【14:48:32】
	;

在这里插入图片描述

1.2日期时间格式转换

to_char()和to_timestamp()函数的使用对于日期的结果变换和统一非常重要，使用频率非常高的函数。

select 
   	to_char(current_date,'yyyymmdd') -- 返回当前系统日期，格式为yyyy-mm-dd 【20230408】
    ,to_date('2023-04-26', 'yyyy-mm-dd') -- 将字符串转换成 date 类型的值 【2023-04-26】
    ,date('2023-04-26') -- 将字符串转换成 date 类型的值 【2023-04-26】看字符类型
    ,to_timestamp('2023-04-26', 'yyyy-mm-dd') -- 将字符串转换成 timestamp 类型的值 【2023-04-26 00:00:00】
    ,to_timestamp('2023-04-26 12:30:45','yyyy-mm-dd hh24:mi:ss') -- 将字符串转换成 timestamp 类型的值 【2023-04-26 12:30:45】
    ,to_timestamp('20230426123045','yyyymmddhh24miss')  -- 将字符串转换成 timestamp 类型的值 【2023-04-26 12:30:45】
    ,to_timestamp('2023042612'||'3045','yyyymmddhh24miss')  -- 将字符串转换成 timestamp 类型的值    【2023-04-26 12:30:45】
	,'2023-04-26 16:30:45'::date  		-- 将字符串转换成 date 类型的值  【2023-04-26】
	,'2023-04-26 16:30:45'::time		-- 将字符串转换成  time 类型的值  【16:30:45】
	,'2023-04-26 16:30:45'::timestamp 	-- 将字符串转换成  timestamp 类型的值  【2023-04-26 16:30:45】
;

在这里插入图片描述

转换函数的嵌套使用，这种嵌套在实际工作中非常常用且很实用。

select 
   	to_char(to_date('2023-04-26','yyyy-mm-dd'), 'yyyy年mm月dd日') -- 将日期/时间类型的值转换成字符串类型  【2023年04月26日】
   	,to_char(to_timestamp('2023-04-26 16:30:45','yyyy-mm-dd hh24:mi:ss'),'yyyy/mm/dd hh24时mi分ss秒 ') -- 将字符串转换成 TIMESTAMP 类型的值  【2023/04/26 16时30分45秒】
	,to_char(to_timestamp('2023-04-26 16:30:45','yyyy-mm-dd hh24:mi:ss'),'hh12miss am') -- 将字符串转换成 TIMESTAMP 类型的值   【043045 pm】
;

在这里插入图片描述

1.3日期加减计算函数

GreenPlum和PostgreSQL中常用的日期间隔计算函数为age()，或者两个日期直接相减（第2行代码），age()的计算结果是 interval 格式，两个日期直接相减是间隔天数。

select 
	-- 减法
   	interval '2 years 3 months 10 days'   -- 创建一个时间间隔 【2 years 3 mons 10 days】
   	,'2023-04-26'::date - '2021-04-26'::date  -- 两个日期直接相减计算天数 【730】
   	,age('2023-04-26', '2021-12-25')  -- 计算两个日期之间的时间差 【1 year 4 mons 1 day】
   	,age('2023-04-26'::date,'2021-12-25'::date)  -- 计算两个日期之间的时间差 【1 year 4 mons 1 day】
   	,age(timestamp '2023-04-26', timestamp '1950-08-01') -- 计算两个日期之间的时间差 【72 years 8 mons 25 days】
   	,age(timestamp '1950-08-01')   -- 从当前系统日期current_date(2023-04-08) 减去参数后的结果 【72 years 8 mons 7 days】
   	-- 加法
   	,'2023-01-19 09:12:00'::timestamp + interval '1 day'  -- 【2023-01-20 09:12:00】
   	,'2023-01-20 09:12:00'::timestamp + interval '4 week' -- 【2023-02-17 09:12:00】
   	,'2023-01-20 09:12:00'::date + interval '3 month' -- 【2023-04-20 00:00:00】
   	,'2023-01-20 09:12:00'::date + interval '1 year'  -- 【2024-01-20 00:00:00】
   	,'2023-01-20 09:12:00'::date + interval '100 year'  -- 【2123-01-20 00:00:00】
;

减法结果
在这里插入图片描述
加法结果
在这里插入图片描述

1.4日期时间部分提取

函数1：date_part() 从日期/时间字符串中取出指定部分的值
本人比较习惯于使用date_part()函数做日期时间指定值的提取，简单齐全。

select 
   	date_part('year', date '2023-04-26') -- 【2023】
   	,date_part('month', '2023-04-26'::date) -- 【4】
   	,date_part('week', '2023-04-26'::date) -- 【17】
   	,date_part('day', '2023-04-26 19:20:21'::timestamp) -- 【26】
   	,date_part('month', interval '2 years 3 months 10 days') -- 【3】
   	,date_part('year', interval '2 years 3 months 10 days')::text
		||(case when length(date_part('month', interval '2 years 3 months 10 days')::text)=2
				then date_part('month', interval '2 years 3 months 10 days')::text
			else '0'||date_part('month', interval '2 years 3 months 10 days')::text 
		   end)
		||(case when length(date_part('day', interval '2 years 3 months 10 days')::text)=2
				then date_part('day', interval '2 years 3 months 10 days')::text
			else '0'||date_part('day', interval '2 years 3 months 10 days')::text 
		   end)  -- 【20310】 跟DB2的计算一样，这里用五位数字代表间隔的年月日
;

在这里插入图片描述

函数2：extract() 从日期/时间字符串中抽取指定部分的值，与date_part()效果一样

select
	extract(year from timestamp '2023-04-26 19:20:21') -- 【2023】
	,extract(quarter from '2023-04-26 19:20:21'::timestamp) -- 【2】
	,extract(month from '2023-04-26 19:20:21'::timestamp) -- 【4】
	,extract(week from '2023-04-26 19:20:21'::timestamp) -- 【17】
	,extract(day from '2023-04-26 19:20:21'::timestamp) --【26】
	,extract(hour from '2023-04-26 19:20:21'::timestamp) -- 【19】
	,extract(minute from '2023-04-26 19:20:21'::timestamp) -- 【20】
	,extract(second from '2023-04-26 19:20:21'::timestamp) -- 【21】
	-- interval 时间间隔
	,extract(month from interval '2 years 3 months 10 days') -- 【3】
	,extract(year from interval '2 years 3 months 10 days')::text
		||(case when length(extract(month from interval '2 years 3 months 10 days')::text)=2
				then extract(month from interval '2 years 3 months 10 days')::text
			else '0'||extract(month from interval '2 years 3 months 10 days')::text 
		   end)
		||(case when length(extract(day from interval '2 years 3 months 10 days')::text)=2
				then extract(day from interval '2 years 3 months 10 days')::text
			else '0'||extract(day from interval '2 years 3 months 10 days')::text 
		   end) -- 【20310】 跟DB2的计算一样，这里用五位数字代表间隔的年月日
;

在这里插入图片描述

函数3：date_trunc() 截取日期到指定的时间单位，date_trunc()函数的返回可以看到还是个日期时间格式，这是与date_part()和extract()差异所在，计算后直接返回日期时间

select 
   	date_trunc('day',date '2023-04-26')  -- 【2023-04-26 00:00:00】
   	,date_trunc('week', '2023-04-26 19:20:21'::timestamp)  -- 【2023-04-24 00:00:00】
   	,date_trunc('month', '2023-04-26'::date)  -- 【2023-04-01 00:00:00】
   	,date_trunc('year', '2023-04-26'::date)  -- 【2023-01-01 00:00:00】
   	,date_trunc('hour', '2023-04-26 19:20:21'::timestamp)  -- 【2023-04-26 19:00:00】
   	,date_trunc('minute', '2023-04-26 19:20:21'::timestamp)  -- 【2023-04-26 19:20:00】
   	,date_trunc('second', '2023-04-26 19:20:21'::timestamp)  -- 【2023-04-26 19:20:21】
;

在这里插入图片描述

1.5日期时间函数的嵌套使用

两个日期直接相减，得到的结果是日期间的间隔天数，如下1和2行代码执行结果；
两个日期通过age函数相减，得到结果是 interval 间隔格式，如下3和4行代码执行结果；
3和4行代码执行结果，可以经过 || 拼接加工为类似于DB2的日期相减格式来使用，如五位数字的10407，即1年4个月7天。

select 
   	'2023-04-26'::date - (date_trunc('week','2021-12-26'::date) - interval '1 day') -- 【493 days】
   	,date_part('day','2023-04-26'::date - (date_trunc('week','2021-12-26'::date) - interval '1 day'))  --【493.0】
   	,age('2023-04-26', '2021-12-26')  --【1 year 4 mons】
   	,age('2023-04-26', (date_trunc('week','2021-12-26'::date) - interval '1 day'))  --【1 year 4 mons 7 days】
   	,age('2023-04-26', '2021-12-20'::date - interval '1 day')  -- 【1 year 4 mons 7 days】
   	,date_part('year', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text
		||(case when length(date_part('month', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text)=2
				then date_part('month', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text
			else '0'||date_part('month', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text 
		   end)
		||(case when length(date_part('day', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text)=2
				then date_part('day', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text
			else '0'||date_part('day', age('2023-04-26', '2021-12-20'::date - interval '1 day'))::text 
		   end)  -- 【10407】
;

在这里插入图片描述

二.使用案例

2.1存储过程计算日期时间表

第一步，构建表结构。因为某系统经常用到昨日、上周末、上旬末、上月末、上季末、上年同期的日期，每次调用都计算就很麻烦，所以开发了一张日期时间表，每次调用表即可。具体内容如下：

-- 存储表建表
CREATE TABLE public.date_information (
	dtstatdate date NOT NULL, -- 数据日期
	period_code bpchar(3) NOT NULL, -- 内容编码
	period_date date NOT NULL, -- 内容日期
	period_name varchar(10) NOT NULL, -- 内容名称
	period_type int4 NOT NULL, -- 内容格式
	interval_days int4 NOT NULL -- 内容日期与数据日期间隔天数
);
COMMENT ON TABLE public.date_information IS '日期时间表';

-- Column comments

COMMENT ON COLUMN public.date_information.dtstatdate IS '数据日期';
COMMENT ON COLUMN public.date_information.period_code IS '内容编码';
COMMENT ON COLUMN public.date_information.period_date IS '内容日期';
COMMENT ON COLUMN public.date_information.period_name IS '内容名称';
COMMENT ON COLUMN public.date_information.period_type IS '内容格式';
COMMENT ON COLUMN public.date_information.interval_days IS '内容日期与数据日期间隔天数';

第二步，编写存储过程，存储过程较长，完整脚本放在了文章最后；
第三步，调用存储过程生成对应日期，并插入到目标表中。

-- 存储过程调用
select date_inf((current_date - interval '1 day')::date);
select date_inf(('2023-01-21'::date - interval '1 day')::date);

第四步，结果验证

select * from public.date_information;

在这里插入图片描述

日期时间间隔计算的完整存储过程如下所示 

CREATE OR REPLACE FUNCTION date_inf(dt date)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
	DECLARE V_STEP VARCHAR(10) default '0';

	begin
		
		V_STEP :='1'; -- 删除历史数据
		delete from date_information where dtstatdate = dt;
		
		V_STEP :='2'; -- 插入select数据
		insert into date_information(dtstatdate,
		period_code,
		period_date,
		period_name,
		period_type,
		interval_days
			)
		select dtstatdate,
			period_code,
			period_date::date period_date,
			period_name,
			period_type,
			interval_days
		from
			(select dt as dtstatdate,
				'CUR' period_code,
				to_char(dt,'yyyy-mm-dd') as period_date,
				'本期' period_name,
				1 period_type,
				0 interval_days
			
			union all
			
			select dt as dtstatdate,
				'PDE' period_code,
				to_char(dt- interval '1 day','yyyy-mm-dd') as period_date,
				'昨日' period_name,
				1 period_type,
				1 interval_days
	
			union all
			
			select dt as dtstatdate,
				'PWE' period_code,
				to_char(date_trunc('week',dt) - interval '1 day','yyyy-mm-dd') as period_date,
				'上周末' period_name,
				1 period_type,
				date_part('day',dt - (date_trunc('week',dt) - interval '1 day')) interval_days
	
			union all
			
			select dt as dtstatdate,
				'PTE' period_code,
				case when substr(dt::text,9,10)::int < 11
						then to_char(concat(substr(dt::text,1,8),'01')::date - interval '1 day','yyyy-mm-dd')
					when substr(dt::text,9,10)::int < 21 
						then to_char(concat(substr(dt::text,1,8),'10')::date,'yyyy-mm-dd')
					when substr(dt::text,9,10)::int >= 21 
						then to_char(concat(substr(dt::text,1,8),'20')::date,'yyyy-mm-dd')
				end as period_date,
				'上旬末' period_name,
				1 period_type,
				date_part('day',dt - (case when substr(dt::text,9,10)::int < 11
						then concat(substr(dt::text,1,8),'01')::date - interval '1 day'
					when substr(dt::text,9,10)::int < 21 
						then concat(substr(dt::text,1,8),'10')::date
					when substr(dt::text,9,10)::int >= 21 
						then concat(substr(dt::text,1,8),'20')::date
				end)) interval_days
	
			union all
			
			select dt as dtstatdate,
				'PME' period_code,
				to_char(date_trunc('month',dt) - interval '1 day','yyyy-mm-dd') as period_date,
				'上月末' period_name,
				1 period_type,
				date_part('day',dt - (date_trunc('month',dt) - interval '1 day')) interval_days
	
			union all
			
			select dt as dtstatdate,
				'PQE' period_code,
				to_char(date_trunc('quarter',dt) - interval '1 day','yyyy-mm-dd') as period_date,
				'上季末' period_name,
				1 period_type,
				date_part('day',dt-(date_trunc('quarter',dt) - interval '1 day')) interval_days
						
			union all
	
			select dt as dtstatdate,
				'PYE' period_code,
				to_char(date_trunc('year',dt) - interval '1 day','yyyy-mm-dd') as period_date,
				'上年末' period_name,
				1 period_type,
				date_part('day',dt-(date_trunc('year',dt) - interval '1 day')) interval_days
	
			union all
	
			select dt as dtstatdate,
				'PYS' period_code,
				to_char(dt - interval '1 year','yyyy-mm-dd') as period_date,
				'上年同期' period_name,
				1 period_type,
				date_part('day',dt-(dt - interval '1 year')) interval_days
				) a
			;
		return 0;
	END;
$function$
;

2.2未完待续

声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，如有雷同纯属巧合。

赫加青空
关注
  • 1
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
PostgreSQL时间格式及相关函数实践
dimawei2225的博客
11-16 1504
在创建表的时候，有客户需要将时间转为字符串，而且要求了具体的格式，如：20181115101010001。方便记录数据的更新时间，貌似是给Mysql使用，当时就很蛋疼，时间格式存储子啊数据库中就是varchar，导出来是字符串，导入到那边，是什么格式不是自动就转好了吗。但是没办法，还是得听客户的。解决办法如下： apple=# create table test_time(id i...
SQL Server向Greenplum_移植参考手册
09-18
2. **数据类型对比**：SQL Server和Greenplum的数据类型存在差异，如日期时间类型、数值类型等。手册会详细比较两者之间的差异，并提供转换策略。 3. **建表语句**：Greenplum支持的建表语句与SQL Server有所不同，...
GreenPlum常用函数(日期)
热门推荐
sean_zhou的专栏
07-20 3万+
最近使用GP，网上查找资料不太方便，记录工作中遇到的一些常见函数供参考日期1、获取当前日期now(),current_day(),2、字符串转日期timestamp('2011-07-20')timestamp('2011-07-20 00:00:00' )to_date('20
PostgreSQL日期和时间相关函数
最新发布
青哥的博客
04-30 607
PostgreSQL的日期/时间函数 https://blog.csdn.net/Super_King_/article/details/121521421。PostgreSQL函数——时间函数 https://blog.csdn.net/qq_33459369/article/details/119417288。格式为1997-01-01 00:00:00，January 8 04:05:06 1999 PST。12:00:00,120000,12:00,8:00 AM,8:00 PM等格式。
PostgreSQL函数——时间函数
漓涂
08-05 1万+
时间类型 timestamp：时间戳 格式为1997-01-01 00:00:00，January 8 04:05:06 1999 PST date：日期 有1997-01-01,19970101,1/1/1997,Jan-1-1997等多种格式 time：时间 有12:00:00,120000,12:00,8:00 AM,8:00 PM等格式 interval：时间间隔 格式为 1 year 2 months 3 days 4 hours 5 minutes 6 seconds 缩写 含义
greenplum 时间戳及日期函数
hyfstyle的博客
06-24 1万+
一、greenplum获取当前时间： select now(); --2020-06-24 16:43:32 获取当前时间年月日到时分秒 select current_timestamp; --2020-06-24 16:44:53 select localtimestamp; --2020-06-24 16:45:01 select localtime; --16:45:22 select current_time; --16:45:27 select current_date; --2020-06-24
postgresql-常用日期函数
Java_Fly1的博客
09-05 9942
PostgreSQL 提供了以下日期和时间运算的算术运算符。获取当前系统时间-- 当前系统时间一周后的日期。
PostgreSQL之日期时间小结
qzw985的博客
10-20 1万+
PostgreSQL关于时间日期的语法及注意事项
greenplum创建dblink包postgresql-8.3.23.tar.gz
11-18
请注意，由于Greenplum是基于PostgreSQL但有其自身的特性和限制，所以在使用dblink时，要确保对两者的差异有足够的了解，比如事务管理、并发控制以及数据类型兼容性等问题。 总之，通过在Greenplum 4.3中引入...
greenplum4.2管理员手册
06-16
以上是 Greenplum 4.2 管理员手册中的关键知识点概览，这些内容涵盖了从架构设计、数据分布策略到查询优化及安全性管理等多个方面，对于理解和掌握 Greenplum 数据库的核心技术和管理要点具有重要意义。
greenplum 企业应用实战高清带目录版 解压成pdf
03-08
5. 分析与查询：使用SQL进行复杂查询，以及使用高级分析函数和窗口函数。 6. 安全性与权限管理：如何确保数据的安全性和用户访问控制。 7. 性能监控与调优：如何监控数据库性能，识别瓶颈，以及调优策略。 8. 高可用...
postgresql第二课：体系结构和特色用法1
08-08
随着大数据时代的到来，互联网公司纷纷基于PostgreSQL进行定制化开发，如Greenplum和HAWQ，这些系统因其高性能和学院派背景，展现了极高的可靠性和稳定性。 PostgreSQL拥有超过15年的积极开发历史，其架构经过验证...
greenplum 日期及时间函数及时间的加减
夜灬狼丶
11-24 5549
gp库中写一个视图 需要用到时间计算，动态显示当天数据，于是查找了一下gp时间操作函数 清一色的都是如下语句 SELECT now()::timestamp + '1 year'; --当前时间加1年 SELECT now()::timestamp + '1 month'; --当前时间加一个月 SELECT now()::timestamp + '1 day'; --当前时间加一天 SELECT now()::timestamp + '1 hour'; --当前时间加一个小时 SELECT ...
PostgreSQL时间加减
胡萧徒的博客
11-03 1万+
SELECT NOW()::TIMESTAMP+ '1 day'; --当前时间加一天 SELECT NOW() + INTERVAL '1 DAY'; --当前时间加一天
Postgresql数据库中的时间类型汇总
孙悟空
07-27 7806
Postgresql数据库中的时间类型有哪些?
Greenplum时间函数
qq_41198697的博客
10-31 714
【代码】Greenplum时间函数。
greenplum使用总结之常用SQL及函数
CS_GUNNER的博客
01-28 3480
---------------------------------------------------建表等----------------------------------------------------- –创建序列 –eg:CREATE SEQUENCE users181020_ID_seq 2 START WITH 100 3 INCREMENT BY 1 4 NO MINVALUE 5 NO MAXVALUE 6 CACHE 1; 使用: select nextval(‘users18102
PostgreSQL的日期/时间函数
Super_King_的博客
11-24 1万+
零、前言 公司里有一台阿里云RDS数据库用了PPAS（Postgres PlusTM Advanced Server），在处理日期/时间时遇到一些问题，花了点时间整理如下。 一、获取当前时间 select now() select current_timestamp select localtimestamp select clock_timestamp() 有时候，我们不需要这么完整细致的时间，自然就有 select current_date select current_time selec
PostgreSQL的常用时间函数
dev_hui 的博客
11-02 2540
PostgreSQL的常用时间函数使用整理如下： 一、获取系统时间函数 1.1 获取当前完整时间 select current_timestamp; select now(); 结果例子：2018-11-02 12:32:38.327824+08   select current_time; 结果例子：12:33:22.922431+08 1.2 获取当前日期 select c...
greenplum和postgresql有什么区别
04-25
Greenplum和PostgreSQL都是开源的关系型数据库管理系统，但是它们的重点不同。PostgreSQL更注重于单节点或少量节点，而Greenplum则专注于大数据、分布式环境下的高并发需求。Greenplum具有更好的扩展能力和更强大的分布式查询能力，可以更好地处理PB级别的数据。同时，Greenplum还提供了许多针对分析和BI应用的扩展功能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值