【maxcompute|ODPS|SQL|HSQL】日期数据非标准日期格式（yyyy/M/d），如何转为yyyy-MM-dd HH:mm:ss标准格式

SQL中的日期数据转换真是一个复杂多变的问题啊！

一、需求及问题描述

在日常开发中，我们会遇到时间戳与标准日期格式之间的转换，两个日期差，及日期加加减减，想必大家都已经非常熟悉了

题主最近接到一个需求：

业务方给我一个表格，其中有一个截止日期字段
要求：
给定截止日期，查询截止日期前六个月的信息

但是Excel表格里面的日期是这样子的
在这里插入图片描述
把这些数据导入到ODPS中（题主是用阿里云的ODPS开发的)，导入数据建表是，该截止日期字段是STRING类型的

sql">SELECT deadline FROM case_3;

由下图可见，导入之后日期格式仍然不是标准日期格式
在这里插入图片描述
如果我想查询截止日期在2024/9/1号之前6个月内的数据应该怎样限制日期呢？
应当是

time <= deadline and time >= deadline-6个月

我们先直接查一下，结果为true，说明time <= deadline这端是没问题的，阿里云的Maxcompute会自动转换类型进行加减，这是阿里云底层的优化

sql">SELECT '2024-08-28 18:39:34'<'2024/9/1';

在这里插入图片描述
但是，我们再查下面的SQL，得到如下结果。显然，这并不符合我们本意。使用了ADD_MONTHS函数之后我们期望得到2024/6/1，这样才能与'2024-08-28 18:39:34'作对比。这篇文章主要介绍如何解决这个问题。

sql">SELECT ADD_MONTHS('2024/9/1',-6),'2024-08-28 18:39:34'>ADD_MONTHS('2024/9/1',-6);

在这里插入图片描述

二、ADD_MONTH函数的用法

我在这里总结一下它的用法：

add_months函数需要接收两个参数
1、第一个参数是开始日期，它的数据类型可以是date|datetime|timestamp|string这些类型中的一个，date，datetime和string类型有对应的格式要求
2、第二个参数是一个整型的数字，用来表示加多少个月。

返回的数据类型是string类型的，格式为yyyy-mm-dd。

详情见下面的介绍

命令格式

sql">string add_months(date|datetime|timestamp|string <startdate>, int <num_months>)

参数说明

startdate：必填。DATE、DATETIME、TIMESTAMP或STRING类型，格式为yyyy-mm-dd、yyyy-mm-dd
hh:mi:ss或yyyy-mm-dd
hh:mi:ss.ff3。取值为STRING类型格式时，至少要包含yyyy-mm-dd且不含多余的字符串。

num_months：必填。INT型数值。

返回值说明

返回开始日期startdate增加num_months个月后的日期，返回STRING类型的日期值，格式为yyyy-mm-dd。返回规则如下：

startdate非DATE、DATETIME、TIMESTAMP或STRING类型，或格式不符合要求时，返回NULL。

startdate值为NULL时，返回报错。

num_months值为NULL时，返回NULL。

三、格式化2024/9/1到2024-09-01

知道add_months函数的用法之后，我们现在面临的问题是，如何把不规范的日期规范。
注意：此时DATE_FORMAT函数已经不再适用，因为DATE_FORMAT的入参不支持接受2024/9/1格式的数据

sql">string date_format(date|timestamp|string <date>, string <format>)

参数说明

date：必填。待转换的日期值。支持DATE、TIMESTAMP或STRING类型。

DATE和STRING类型只能在Hive兼容模式下使用，您可以执行set
odps.sql.hive.compatible=true;命令打开Hive模式。

如果参数为STRING类型，则格式只支持以下三种日期字段：

‘yyyy-MM-dd’，例如’2019-12-27’。

‘yyyy-MM-dd hh:mm:ss’，例如’2019-12-27 12:23:10’。

‘yyyy-MM-dd hh:mm:ss.SSS’，例如’2019-12-27 12:23:10.123’。

format：必填。STRING类型常量。format可由如下日期字段组成，例如yyyy-MM-dd
hh:mm:ss.SSS或yyyy-MM-dd hh:mi:ss.SSS：

解决方案

补齐日期中缺失的位数，并把它转化为标准日期格式。
废话不多说，直接贴出来SQL，标准化之后的数据如下图所示，符合我们的预期：

sql">SELECT  deadline,CONCAT(-- 年份  SPLIT(deadline,'/')[0],'-',-- 月份，确保两位  LPAD(SPLIT(deadline,'/')[1],2,'0'),'-',-- 日，确保两位  LPAD(SPLIT(deadline,'/')[2],2,'0'),' 00:00:00') AS formatted_date
FROM    case_3
;

在这里插入图片描述

这里着重介绍一下LPAD函数：

当需要格式化数据表中的字符串，以保证字符串输出的一致性和对齐时，使用LPAD函数，用字符串str2将字符串str1向左补足到length位。

总结一下它的用法：
LPAD函数接收三个参数

第一个参数为string类型，为需要补齐的字符串
第二个参数为Int类型，表示该字符串补齐后需要达到的长度或者位数
第三个参数为string类型，为长度不足时，使用该字符串补齐
返回类型为string类型

详情见下面的介绍

命令格式

sql">string lpad(string <str1>, int <length>, string <str2>)

参数说明

str1：必填。STRING类型。待向左补位的字符串。

length：必填。INT类型。补位后的目标位数。

str2：必填。用于补位的字符串。

返回值说明

返回STRING类型。返回规则如下：

如果length小于str1的位数，则返回str1从左开始截取length位的字符串。

如果length为0，则返回空串。

如果没有输入参数或任一输入参数值为NULL，返回NULL。

如果length大于str1的位数且小于str1和str2的位数之和，则按照str2字符从左到右的顺序将str1字符串向左补足到length位。

我们来解释一下这段SQL

sql">
-- SPLIT(deadline,'/')[0] 使用'/切分字符串'，使其转化为一个数组，取数组第0位数据，即年(yyyy)所在的位置，年份不需要补齐
--  LPAD(SPLIT(deadline,'/')[1],2,'0') 使用'/切分字符串'，使其转化为一个数组，取数组第1位数据，即月(MM)所在的位置，月份需要向左补齐为两位，缺失的位数用0补齐
-- LPAD(SPLIT(deadline,'/')[2],2,'0')  使用'/切分字符串'，使其转化为一个数组，取数组第2位数据，即日(dd)所在的位置，日需要向左补齐为两位，缺失的位数用0补齐
-- 使用concat函数将年月日用'-'拼接，并给它拼接上时分秒
SELECT  deadline,CONCAT(-- 年份  SPLIT(deadline,'/')[0],'-',-- 月份，确保两位  LPAD(SPLIT(deadline,'/')[1],2,'0'),'-',-- 日，确保两位  LPAD(SPLIT(deadline,'/')[2],2,'0'),' 00:00:00') AS formatted_date
FROM    case_3
;