点击上方蓝字关注我们
今天是SQL知识大全的第五讲，主要内容是和时间函数相关，主要包括了常用的时间函数，时间提取函数，时间计算函数以及时间和时间戳之间的转换。
常用的时间函数
to_date()函数
to_date()函数将字符串的日期转换为时间格式的日期，转换后的时间格式可以是如下形式：
| 格式 | 意义 |
| YEAR | 年的拼写 |
| MM | 数字月 |
| MONTH | 月的全拼 |
| MON | 月的缩写 |
| DD | 数字日 |
| DAY | 星期的全拼 |
| DY | 星期的缩写 |
| AM | 表示上午或者下午 |
| HH24、HH12 | 12小时制或24小时制 |
| MI | 分钟 |
| SS | 秒钟 |
具体示例如下：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL
current_date()/current_time()
返回当前时间,某年某月某日，current_time()等同于current_date。
SELECT CURDATE()# 返回值：
# '2020-10-09'
current_timestamp()
current_timestamp()相当于now(),返回当前的时间戳。
时间提取
时间提取函数包括year(),month(),day(),hour(),minute(),second()等，其用法都是一直的，这些函数分别返回 date 的年份，月份，日期，时间等，范围为1000-9999。当 date 为0时，返回0。
具体示例如下：
SELECT YEAR('98-02-03')
# 返回值 1998
SELECT MONTH('98-02-03')
# 返回值 02
SELECT DAY('98-02-03')
# 返回值 3
时间计算函数
DATEDIFF()
语法：DATEDIFF(expr1,expr2)
DATEDIFF()将返回expr1 - expr2的值，用来表示两个日期相差的天数。expr1 和 expr2 都是日期或日期时间表达式。运算中只用到了这些值的日期部分。
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30')
#返回结果
1
date_sub()/date_add()
语法格式：
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
执行日期计算的两种函数。date 是一个用来指定开始日期的 DATETIME 或 DATE 值。expr 是一种以字符串形式呈现的表达式，用来指定从开始日期增加或减少的间隔值。如果是负的间隔值，则 expr 值的第一个字符是-号。unit 是一个单位关键字，用来指定expr表达式应该采取的单位。
INTERVAL 关键字与单位说明符都不区分大小写。
下表列出了每个单位数值所对应的 expr 参数的期望格式。
| 单位所能取的值 | 期望的expr格式 |
|---|---|
| MICROSECOND | 毫秒 |
| SECOND | 秒 |
| MINUTE | 分 |
| HOUR | 小时 |
| DAY | 日 |
| WEEK | 周 |
| MONTH | 月 |
| QUARTER | 季度 |
| YEAR | 年 |
| SECOND_MICROSECOND | '秒.毫秒' |
| MINUTE_MICROSECOND | '分.毫秒' |
| MINUTE_SECOND | '分:秒' |
| HOUR_MICROSECOND | '小时.毫秒' |
| HOUR_SECOND | '小时:分:秒' |
| HOUR_MINUTE | '小时:分' |
| DAY_MICROSECOND | '日.毫秒' |
| DAY_SECOND | '日 小时:分:秒' |
| DAY_MINUTE | '日 小时:分' |
| DAY_HOUR | '日 小时' |
| YEAR_MONTH | '年-月' |
时间戳
时间戳的定义
时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。
UNIX时间戳的0按照ISO 8601规范为 ：1970-01-01T00:00:00Z.
一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算。
在大多数的UNIX系统中UNIX时间戳存储为32位，这样会引发2038年问题或Y2038。
UNIX_TIMESTAMP(date)
若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳 。
若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD或YYYMMDD格式的数字。
例如:
SELECT UNIX_TIMESTAMP() ; （执行时的时间:2009-08-06 10:10:40）
# 1249524739
SELECT UNIX_TIMESTAMP('2009-08-06') ;
# 1249488000
from_unixtime(）
from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。
对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data
where …
SELECT from_unixtime(cast(ts AS bigint))
FROM temptable
参考文章
https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html
https://baike.baidu.com/item/unix%E6%97%B6%E9%97%B4%E6%88%B3
https://blog.csdn.net/qq_24309787/article/details/81095238
SQL系列文章持续更新中
往期推荐
分享数据知识，成就数据理想
京公网安备 11010502036488号