博客 SQL中日期格式处理

SQL中日期格式处理

   数栈君   发表于 2023-08-31 10:03  149  0

背景:实际工作,使用SQL语句对数据进行处理,有一大部分工作是对日期时间型数据进行处理,通过对字段的拼接或转换生成实际需要的格式的日期字段。本文章尽可能全面记录现在主流的数据库(MySQL和Hive)对日期格式的处理,形成一份工作速查文档,提升工作效率。

一、获取当前日期

MySQL
select now(); --日期时间格式
select current_date(); -- 日期格式
select current_time(); -- 时间格式
select sysdate(); -- 日期时间格式
select current_timestamp(); -- 日期时间戳

Hive
select current_date() -- 日期格式
select current_timestamp() -- 日期时间格式

sql server
select getdate()
--获得年月日
select convert(nvarchar(10),getdate(),120)
--获得年月
select convert(nvarchar(7),getdate(),120)

二、日期格式化

格式化可以将不符合要求规范的日期格式字段或字符串类型字段转换为格式化日期。

MySQL
select date_format('20230613','%Y-%m-%d');
select date_format('20230613','%Y-%m-%d %H:%i:%s');
select date_format(now(),'%Y-%m-%d');
-- 可使用unix_timestamp函数获取 unix 时间戳,使用时间格式转换函数from_unixtime转换为格式日期
select from_unixtime(unix_timestamp('20230613'), '%Y-%m-%d');

Hive
select date_format(current_timestamp(),'yyyy-MM-dd')
-- Hive中对月份格式用MM
select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss')

select from_unixtime(unix_timestamp('2023-06-13', 'yyyy-MM-dd'), 'yyyyMMdd') -- 生成20230613对应格式的日期
-- unix_timestamp返回对应时间戳
select unix_timestamp() -- 返回当前时间对应的时间戳,时间戳数值可以加减,如一小时3600秒,一天86400秒

sql server
select convert(datetime,'YYYY-MM-DD HH24:MI:SS')
-- cast强制转换
select cast('YYYY-MM-DD HH24:MI:SS' as datetime)

三、日期转换为字符串

SQL表中存储日期对数据类型有要求,特别是 hive存储日期时间的时候通常用string,所以有时也要实现日期转换为字符串。

MySQL

-- 可使用substr字符串切割函数,返回从m开始长度为n的字符串
select substr(now(),1,10);
-- 使用concat字符串拼接函数
select concat(substr(now(),1,4),substr(now(),6,2));

Hive
-- 使用cast强制转换为字符串
select cast(from_unixtime(unix_timestamp('20230613', 'yyyyMMdd'), 'yyyy-MM-dd') as string)

sql server
--
select convert(nvarchar(10),getdate(),120)
-- 常用格式
Select CONVERT(varchar(100), GETDATE(), 120) -- 年月日 时分秒
Select CONVERT(varchar(100), GETDATE(), 112) -- 年月日(无分隔符)
Select CONVERT(varchar(100), GETDATE(), 111) -- 年月日(分隔符/)
Select CONVERT(varchar(100), GETDATE(), 102) -- 年月日(分隔符.)
Select CONVERT(varchar(100), GETDATE(), 23) -- 年月日(分隔符-)
Select CONVERT(varchar(100), GETDATE(), 8) -- 时分秒


日期计算

在表的实际计算中,常需要计算距离某个日期或现在日期往前或往后某段时间的日期。

-- 加(Mysql)
select date_add(now(),interval 1 day);
-- year:年,quarter:季,month:月,week:周,day:天,hour:小时,minuter:分钟,second:秒,microsecond:毫秒
-- 加(sql server)
select dateadd(day,n,'2023-06-20')
-- 加(Hive)
select date_add(current_timestamp(),1) -- 加一天
select date_format(date_sub(current_timestamp(),1),'yyyy-MM-dd') --指定前一天日期
select from_unixtime((unix_timestamp()-86400),'yyyy-MM') --指定前一天日期
-- 减
select date_sub(now(),interval 1 month);
select date_sub(date_format(current_timestamp(),'yyyy-MM-dd'),1)
select date_sub(current_timestamp(),14) -- Hive指定前一天日期
-- 计算日期间隔
select datediff('2023-6-16','2023-6-1') -- mysql计算两个日期之间的间隔(相差天数)
select datediff('d','2023-6-1','2023-6-16 12:20:00') --sql server计算两个日期之间间隔天数
select timediff('12:00:00', '11:30:00') -- 两个时间间隔
select timestampdiff(month,date_sub(now(),interval 60 day),now()); -- 相差月
-- timestampdiff第一个参数定义返回计算的结果。year:年,quarter:季,month:月,week:周,day:天,hour:小时,minuter:分钟,second:秒,microsecond:毫秒
-- timestampdiff减去的日期带有时间,会认为是下一天(2023-07-20 08:00:00会按照2023-07-21计算);datediff会直接截取时间(2023-07-20 18:00:00也会按照2023-07-20计算)
select timestampdiff(day,'2023-07-13',current_date()) -- timestampdiff可以实现各种字符串(例如2023-07-13,20230703)日期的加减

-- 当月的第一天
select STR_TO_DATE(CONCAT(DATE_FORMAT(now(),'%Y-%m'), '-01'), '%Y-%m-%d')
-- sql server返回当月1号
select CONVERT(varchar(10),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0),120)
-- 当月最后一天
select DATE_ADD(STR_TO_DATE(CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL 1 MONTH),'%Y-%m'), '-01'), '%Y-%m-%d'),INTERVAL -1 DAY);





免责申明:


本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!

《数据治理行业实践白皮书》下载地址:https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址:
https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:
https://www.dtstack.com/?src=bbs

同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:
https://github.com/DTStack

0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群