SQL 时间函数

转载自：SQL千字总结：如何更好的操练你手上的时间数据

1. 认识时间格式

非标准时间格式：20200101可转换
可识别时间格式：2020-12-12、2020-12-12 12:12:12、1577836800
说下时间戳：1577836800，表示1970年1月1日开始过去了多少秒

2. 时间格式转换

2.1 unix_timestamp 和from_unixtime

select '20200131' as `1`,
      unix_timestamp('20200131','yyyyMMdd') as `2`,
      from_unixtime(unix_timestamp('20200131','yyyyMMdd'),'yyyy-MM-dd') as `3`

select '2020-01-31' as `1`,
      unix_timestamp('2020-01-31','yyyy-MM-dd') as `2`,
      from_unixtime(unix_timestamp('2020-01-31','yyyy-MM-dd'),'yyyyMMdd') as `3`

2.2 2020-01-31 12:12:12 to 2020-01-31

select to_date('2020-01-31 12:12:12','yyyy-MM-dd')

2.3 2020-01-31 12:12:12 to 2020-01

select date_format(date '2020-01-31 12:12:12', "yyyy-LL")

2.4 2020-01-31 12:12:12 to 01 OR 1

select date_format(date '2020-01-31 12:12:12', "LL")
# 'M' or 'L': Month number in a year starting from 1

select date_format(date '2020-01-31 12:12:12', "L")

3. 时间计算

计算天数之差：
datediff(endDate, startDate) - Returns the number of days from startDate to endDate
计算月数之差：

months_between(timestamp1, timestamp2[, roundOff]) - If timestamp1 is later than timestamp2, then the result is positive. If timestamp1 and timestamp2 are on the same day of month, or both are the last day of month, time of day will be ignored. Otherwise, the difference is calculated based on 31 days per month, and rounded to 8 digits unless roundOff=false.
如果日期是同一天或者都处于月末，那么按照整月算，否则月 按照31天一个月来计算。

SELECT months_between('2023-02-28 10:30:00', '2023-03-31')

计算秒数之差：

select unix_timestamp('2020-01-31 12:21:12')-unix_timestamp('2020-01-31 12:12:12')

几个月前，几个月后：

select add_months('2020-01-31',1)

几天前：

select date_add('2020-02-29',7)