mysql 日期减法_日期格式处理的几种方法

转眼2021都快过了一个月了，不知道大家是否在写日期的时候还是会不自觉地写下“2020-XX-XX”？

日常的工作和生活中，日期的书写随处可见，对分析师而言，日期和时间的处理更如一日三餐不可或缺。由于库表结构、字段类型以及具体业务逻辑等多种原因，数据库中往往同时存在多种日期格式，又由于数据需求中可能有较为复杂多变的计算规则，需要对多种日期格式进行相互转换和计算。今天我们就来梳理一下SQL中常用的日期处理函数。

大家都知道我们国内标准时间是北京时间，但很多时候我们可能还要处理其他时区的问题。比如，有些企业涉及海外业务、服务器在国外，这种情况下，时间数据一般不能直接拿来就用，可能数据在上传或者存储时使用了utc时间或者当地区时。

那么UTC和GMT时间又是什么呢？

UTC的全程为Universal Time Coordinated，意思是协调世界时；GMT则为Greenwich Mean Time，意思是格林威治标准时间。广义上来说二者含义是一致的，不过前者是更为精确的原子时。初中地理课上，我们就知道了全球被划分为24个时区，本初子午线穿过的格林威治天文台所在时区即为0时区。从0时区往东数八个时区就是北京所在的东八区，以utc时间为基准，加8个小时就是我们最常用的北京时间。

unix时间戳是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数，不考虑闰秒。也就是定义“1970-01-01  00:00:00”这个点的时间戳为0，以此为标准，每过一秒钟，对应时间戳就加1，如“1971-01-01 00:00:00”的时间戳就是365*24*60*60=31536000，如果字符串中不包含时分秒，则以0时0分0秒计。除了以秒为单位外，也有以毫秒计算的时间戳，只需要以1000ms=1s来换算即可。

下面我们就以mysql和hive为例，介绍几种常用的日期和时间处理方法。 时区转换 mysql中可以使用convert_tz函数进行时区转换，如：

select convert_tz('2021-01-20 12:00:00','+00:00','+08:00')as tz

其第一个参数为原时间，格式为'yyyy-MM-dd HH:mm:ss'，第二个参数为原时间所属时区，第三个参数即目标时区，结果如下：

hive中不支持convert_tz函数，但是提供了一个from_utc_timestamp函数可以将utc时间转换为目标时区的区时：

select from_utc_timestamp('2020-01-27 00:00:00','Asia/Shanghai')shanghai,from_utc_timestamp('2020-01-27 00:00:00','Asia/Chongqing')chongqing

‍ 第一个参数是utc时间，第二个是目标时区，在这里要注意的是，东八区的写法可以是Asia/Chongqing或者Asia/Shanghai，但不能是Beijing，是不是很费解？师兄也很无奈，这是历史遗留问题，没得办法：

格式化字符串

有时候数据库里保存的可能是时间戳格式，那这时候我们就需要把它进行格式化处理，转为可读性更高的格式化字符串？

mysql和hive均提供了from_unixtime、unix_timestamp两个函数以供时间戳和字符串互相转换，首先我们看下hive中的实现： 1) from_unixtime是从时间戳转为格式化字符串，接受两个参数，如：

SELECT from_unixtime(1580101200,"yyyy-MM-dd HH:mm:ss")tm1,       from_unixtime(1580101200,"yyyy-MM-dd HH:mm")tm2,       from_unixtime(1580101200,"yyyy-MM-dd hh:mm")tm3

第一个参数即需要转化的时间戳(以秒为单位)，第二个是目标字符串格式。

⚠️注意： 在hive中格式化字符串中需要分清"MM"和"mm"有不同的含义，前者指月份month，后者指分钟minute；对于小时而言，hh和HH也是不同的，小写代表12小时制，大写代表24小时制   2) unix_timestamp可以将格式化日期转为时间戳，第一个参数是原日期字符串，第二个是该字符串的格式，与from_unixtime不同的是，unix_timestamp是不可以将12小时制的时间转为时间戳的。如：

SELECT unix_timestamp("2021-01-27 13:00","yyyy-MM-dd HH:mm") tm1,       unix_timestamp("2021-01-27 13:00:00","yyyy-MM-dd HH:mm:ss") tm2

‍

结果：

mysql虽然也有这两个函数，但是与hive中的用法完全不同，首先是格式化字符串的标示符不一样，mysql中是以“%”百分号加大写或小写字母来标示年月日、时分秒的，其次，unix_timestamp函数只接受一个参数，可以是20210127这种整数型的日期，也可以是‘2021-01-27‘这种的date类型，或是带有时分秒的datetime类型，像下面这样的写法：

select from_unixtime(1580101200,'%Y-%m-%d %H:%i:%s') t1,unix_timestamp(20210127)t2 ,unix_timestamp('2021-01-27')t3,unix_timestamp('2021-01-27 00:00:00')t4

‍

结果如下：

下面表格里是几种常用的标示符，按需替换即可:

日期加减计算

有些时候我们需要对日期进行加减处理，比如下面的例子：

我们有一张用户活跃表(active)，表里有日期(dt)和用户id(uid)两列，那么我们如何计算每天活跃用户的次日留存率呢？ 首先我们从这张表里可以知道每天有哪些用户是活跃的，接下来要做的就是知道每天活跃的用户里有谁第二天仍然是活跃的，这个时候我们要将不同日期的数据关联起来，除了要使用uid连接外还要把日期对齐，即留存日期要减1天后和活跃日期相等：

select  a.dt,  count(distinct a.uid)active_uv,  count(distinct b.uid)retain_uvfrom active a left join (  select uid,date_sub(dt,interval 1 day)dt  from active)b on a.dt=b.dt and a.uid=b.uidgroup by a.dt

如上述SQL(mysql)的子查询b所示，我们使用date_sub对dt执行减法运算，第二个即为参数指定减去的天数，在mysql中还可以支持整月和整年的加减运算，interval后面的关键字分别是month和year。与减法运算date_sub相对应的加法运算为date_add，用法完全一样。 在hive中同样有date_sub和date_add两个函数，但与mysql不同的是，hive只支持以天为单位的加减操作，并且第二个参数不需要指定关键字，形如date_sub('2021-01-20',7)即可得到'2021-01-13'。

时间差

有日期加减运算，自然也免不了对日期求差值，mysql和hive中都提供了datediff函数，datediff接受两个日期参数(格式为"yyyy-MM-dd")，前者减后者，返回相差天数，如下SQL返回结果为366天：

select datediff("2021-01-01","2020-01-01")

hive中还有个months_betwee，是求两个日期之间相差的月数，返回结果有零有整，可以 按需使用哦：

select months_between('2021-01-01','2021-01-31')m1,months_between('2021-01-01','2021-02-01')m2,months_between('2021-01-01','2021-03-01')m3

 返回结果如下，相同的Day返回整月差值，否则为小数： sql是数据分析师吃饭的家伙之一，以上只是时间、日期相关知识的整理，还有很多函数和用法没有提及，后续将会有更多相关分享。 关注我，和师兄一起写sql！