SQL 时间计算方法汇总

Toby

一个爱思考的打工人

sql中的常见时间数据类型

mysql中具有5种时间类数据类型，分别是DATE, TIME, DATETIME, TIMESTAMP 和 YEAR。DATE的格式为CCYY-MM-DD，TIME的格式为hh:mm:ss，分别用来表示时分秒，DATETIME和TIMESTAMP类型使用CCYY-MM-DD hh:mm:ss格式的字符串来保存时间和日期的组合值。

TIMETAMP类型在数据库中实际的存储方式其实是UNIX_TIME，当一个客户端从服务器插入一个TIMESTAMP类型值时，服务器将此值自动转化为UTC，并且保存一个UTC时间值，当客户查询时，服务器进行反向操作。

hive中具有三种数据类型：timestamp、date、interval，格式分别为yyyy-mm-dd hh:mm:ss[.f…] 和YYYY-MM-DD、INTERVAL ‘1’ DAY。

得到想要的时间数据或格式

虽然 mysql 和 hive 中有以上数据类型，但实际工作中，我们可能需要其它的时间格式，最常见的比如说我们要得到每年每月的情况（例如2022-01月）、求得这个月的天数算日均什么的，这些格式需要我们自己进行转换。

1. 得到想要的时间格式

SQL中得到想要的时间格式，例如将 datetime 或 timestamp 类型转化为年-月，年-月-日形势等，最常用的做法是通过date_format() 、trunc()、date_trunc() 函数来实现。

date_format()
date_format(date, format)函数：得到想要的日期格式，例如'2022-02'，第一个参数 date 选择合法的日期（timestamp 也可以，函数自动选取日期部分），第二个参数 format 是想要的日期/时间的输出格式，在mysql中有“%Y-%m"、"%Y-%m-%d”等形式，而在hive中使用的格式是“yyyy-mm”、“yyyy-mm--dd”等。

SELECT
    order_start_time
    , date_format(order_start_time, 'yyyy-MM')
FROM order.order_table

trunc(date, [fmt])
其中fmt是日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

返回的是timestamp类型，即yyyy-mm-dd hh:mm:ss，不管截取的是什么维度的时间。

SELECT 
  order_start_time
  , trunc(order_start_time, 'mm') as order_month 
FROM order.order_table

date_trunc(' field '，source)

field是需要截取的字段，source是需要截取的数据。需要截取的字段如下，需注意的是date_trunc() 得到的是年月日的形式，不管要截取到是年还是月，返回结果都会以该颗粒度下的第一天来表示。

substring()、left()等
这种方式是将一个时间或者日期值直接当作一个字符串，然后简单粗暴地使用字符串函数进行截取我们需要的数值。

2. 得到想要的时间数据

（1）date_format() 或 time_format()

date_format(date, format)函数：得到想要的日期格式，例如月中天数、周中天数、月份等。此函数接受两个参数，参数date是合法的日期，format是想要的日期/时间的输出格式，可以返回年、月、日、时、分、秒、年-月等各种时间类型。但是返回的值是字符串，不能直接用于计算。format的格式可以选择以下类型：- 'y'：2021- ’M':1- ‘MM’：01- 'yyyy-MM':2021-01- ’yyyy-MM-dd‘：2021-01-01- 其它可参照 https://www. w3school.com.cn/sql/fun c_date_format.asp

（2）使用专用函数

第二种方法是使用SQL标准函数从日期或者时间值中分解出各部分值。例如使用特定函数：year(), month(), day()，minute()，返回的值为数值，有些函数从日期或者时间值中直接分解出一个子串作为返回值，例如YEAR()和DAYOFMONTH() 函数，有些函数可以返回在日期或者时间值中没有直接对应子串的部分，例如年份中的天数。一些常用的函数如下：

代码示例和结果为：

SELECT
      order_start_time
      , year(order_start_time) as year_id 
      , month(order_start_time) as month_id 
      , dayofmonth(order_start_time) as dayofmonth 
      , dayofweek(order_start_time) as dayofweek
      , dayofyear(order_start_time) as dayofyear
      , hour(order_start_time) as hour_id
      , minute(order_start_time) as minute_id 
      , second(order_start_time) as sec_id 
FROM order.order_table

需要注意的是DAYNAME()函数返回date的星期名。DAYOFWEEK和WEEKDAY函数返回date的星期值，分别返回值对应星期天为第一天和星期一为第0天。另外 pmod(int a, int b) 返回 a/b的绝对值，也可以用来达到dayofweek()的效果。

（3）extract(unit From date)

其中 unit是保留字，例如year, month, day, hour, inute 或 second

（4）获取日期：date(), to_date() （to_date 是 hive中的函数）

在时间数据类型和基本单位之间转换

Time值和秒之间相互转换
time_to_sec()、sec_to_time()

如果将一个DATETIME 或者 TIMESTAMP 类型值传递给函数，那么其中的日期部分值将会被忽略，以上两个函数为mysql函数，在hive中没有发现类似函数。

日期值和天数之间进行转换
to_days()、from_days()

将一个日期值转化为对应的天数，如果传递了DATETIME 或者TIMESTAMP 类型值，该函数会从参数中提取出日期部分。以上两个函数为 mysql函数，在hive中没有发现类似函数。

在 DATETIME 或者 TIMESTAMP 类型值和秒数之间进行转换

TIMESTAMP，也就是UNIX TIME，是指从1970-01-01 00:00:00 UTC 年开始计算秒数，也是UNIX系统中计算时间的起点，大约可以计算到2037年。在TIMESTAMP和DATETIME之间的转换可以用

UNIX_TIMESTAMP()直接跟timestamp即可
FROM_UNIXTIME()，后面跟格式

使用UNIX_TIME STAMP函数也可以把DATE类型值转化为秒，相当于给DATE类型值一个00:00:00的时间部分值

计算两个日期或时间之间的间隔

计算两个时间之间的间隔，可以使用时间差函数，也可以将两个时间转化为基本时间单位，然后计算间隔。

（1）使用时间差函数

DATEDIFF(大日期，小日期)

计算两个日期值之间间隔天数可以传入datetime类型，忽略时间部分。

SELECT
    order_start_time
    , datediff(current_date(), order_start_time) as date_diff
FROM dwb_asset_order.wide_order_charge_whole
WHERE   
    station_type_name = '物流'
    and order_month = '2022-02'

timediff()：计算两个time类型值之间的时间间隔，hive中无此类函数。
timestampdiff()
可以指定函数返回的时间间隔使用的基本单位（天、周、日、时、分、秒等），例如timestampdiff(hour, dt1, dt2) as hour_diff。需要注意的是timestampdiff中的两个时间值，第一个时间值应该比第二个时间值小，否则返回的是负数，这与datediff() 和 timediff() 是不同的。hive中也无此函数。

（2）利用基本时间单位计算间隔

这是SQL中最常用的计算时间间隔的方式，其思想就是将目前的时间值转化为一定的基本时间单位（秒、日等），然后用两个数字相减，再做除法求得时间差。

用time_to_sec() 计算秒数差
用to_days() 计算日期差
用unix_timestamp()计算以秒数为单位的时刻差

增加日期或时间值

对日期或者时间做加减法，也有两种方法，第一种是使用时间加法函数，第二种是转换为基本计时单位，然后求和。

（1）利用时间加法函数

date_add()
date_sub()
add_months()：月份相加，内是完整日期格式，不完整可以计算，但返回是完整日期格式的值
month_between()

SELECT