SQL中日期格式处理

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

一、获取当前日期

MySQL

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

Hive

selectcurrent_date()-- 日期格式selectcurrent_timestamp()-- 日期时间格式

sql server

select getdate()--获得年月日selectconvert(nvarchar(10),getdate(),120)--获得年月selectconvert(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中对月份格式用MMselect 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

selectconvert(datetime,'YYYY-MM-DD   HH24:MI:SS')-- cast强制转换select cast('YYYY-MM-DD   HH24:MI:SS'asdatetime)

三、日期转换为字符串

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

-- selectconvert(nvarchar(10),getdate(),120)-- 常用格式SelectCONVERT(varchar(100), GETDATE(),120)-- 年月日 时分秒SelectCONVERT(varchar(100), GETDATE(),112)-- 年月日（无分隔符）SelectCONVERT(varchar(100), GETDATE(),111)-- 年月日（分隔符/）SelectCONVERT(varchar(100), GETDATE(),102)-- 年月日（分隔符.）SelectCONVERT(varchar(100), GETDATE(),23)-- 年月日（分隔符-）SelectCONVERT(varchar(100), GETDATE(),8)-- 时分秒

日期计算

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

-- 加(Mysql)select date_add(now(),interval1day);-- 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(),interval1month);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(),interval60day),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号selectCONVERT(varchar(10),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0),120)-- 当月最后一天select DATE_ADD(STR_TO_DATE(CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL1MONTH),'%Y-%m'),'-01'),'%Y-%m-%d'),INTERVAL-1DAY);