MySQL 日期时间

数据类型

MySQL中有5中表示时间值的数据类型，分别是DATE、TIME、YEAR、DATETIME、TIMESTAMP

• DATE 日期值，格式为YYYY-MM-DD，大小3字节，范围从1000-01-01到9999-12-31。
• TIME 时间值或持续时间，格式为HH:MM:SS，大小3字节，范围从-838:59:59到838:59:59。
• YEAR 年份值，格式为YYYY，大小1字节，范围从1901到2155。
• DATETIME 混合日期和时间值，格式为YYYY-MM-DD HH:MM:SS，大小8字节，范围从1000-01-01 00:00:00到9999-12-31 23:59:59。
• TIMESTAMP 混合日期和时间值的时间戳，格式为YYYYMMDD HHMMSS，大小4字节，范围从1970-01-01 00:00:00到2037年。

注意：TIMESTAMP类型拥有专有的自动更新特性

时间与字符串转换

• DATE_FORMAT和TIME_FORMAT日期时间转换函数可以将一个日期或时间转换成各种字符串格式。
• DATE_FORMAT和TIME_FORMAT实际是STR_TO_DATE函数的逆转换

STR_TO_DATE 字符串转时间

• STR_TO_DATE转换函数可以将杂乱无章的字符串转换为日期格式，同时也可以用来转换时间。

STR_TO_DATE(str, format)

例如：

SELECT STR_TO_DATE('2019-05-14', '%Y-%m-%d %H:%i:%s')
-- 2019-05-14 00:00:00

DATE_FORMAT 日期格式化

DATE_FORMAT(date, format)

例如：

SELECT DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s")
-- 2019-05-14 16:04:20

TIME_FORMAT 时间格式化

TIME_FORMAT(time, format)

例如：

SELECT TIME_FORMAT(NOW(),'%H:%i:%s')
-- 17:24:44

日期时间格式化字符串

日期格式化取值范围

年

• %Y 四位数字表示的年份，如2019...
• %y 两位数字表示的年份，如19...

月

• %M 英文表示的月份，如January、February...
• %b 英文缩写表示的月份，如Jan,Feb...
• %m 两位数字表示的月份，如01,02,03...
• %c 数字表示的月份，如1,2,3...

日

• %d 两位数字表示的月中天数，如 01, 02...
• %e 数字表示的月中天数，如1,2...
• %D 英文后缀表示的月中天数，如1st, 2st...
• %j 以三位数字表示的年中天数，如001, 002...

周

• %W 英文全程表示的一周中每一天的名称，如Sunday, Monday...
• %a 英文缩写表示的一周中每一天的名称，如Sun, Mon...
• %w 数字形式表示一周中每一天，周日为0，周一为1，依次递增。
• %U 数字形式表示的周数，周日为第一天。
• %u 数字形式表示的周数，周一为第一天。

小时

• %H 24小时制两位数形式表示的小时，如00, 01,...,23
• %h 12小时制两位数形式表示的小时，如01, 02,...,12
• %k 24小时制数字形式表示的小时，如0,1,2,...,23
• %l 12小时制数字形式表示的小时，如0,1,2,...,12
• %T 24小时制的时间形式HH:mm:ss
• %r 12小时制时间形式hh:mm:ss AM/PM
• %p AM上午或PM下午

分钟

• %i 两位数字形式表示的分钟，如00,01,02,...,59
• %I

秒数

• %S 两位数字形式表示的秒数，如00,01,02,...,59
• %s 两位数字形式表示的秒数，如00,01,02,...,59

时间戳转换

UNIX_TIMESTAMP

• UNIX_TIMESTAMP() 获取当前时间戳

SELECT UNIX_TIMESTAMP()
-- 1557826503

• UNIX_TIMESTAMP(date) 时间转时间戳

SELECT UNIX_TIMESTAMP(NOW())
-- 1557825088

• UNIX_TIMESTAMP(str) 字符串转时间戳

例如：将字符串的日期转换为时间戳

SELECT UNIX_TIMESTAMP('2019-05-14')
-- 1557763200

FROM_UNIXTIME

• FROM_UNIXTIME(unix_timestamp) 时间戳转字符串

SELECT FROM_UNIXTIME(1557763200)
-- 2019-05-14 00:00:00

• FROM_UNIXTIME(unix_timestamp, format) 时间戳转字符串

SELECT FROM_UNIXTIME(1557763200, '%Y-%m-%d %H:%i:%s')
-- 2019-05-14 00:00:00

拼凑日期时间

• MAKEDATE(year, dayofyear)
• MAKETIME(hour, minute, second)

天数转换

• TO_DAYS(date) 日期转换为天数
• FROM_DAYS(days) 天数转换为日期

日期计算

DATE_SUB

DATE_SUB(date,INTERVAL expr unit)

当前日期时间

-- 2019-05-14 18:07:33
SELECT NOW()

计算年份

-- 今年 2019
YEAR(NOW()) 
-- 去年，前一年 2018
YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
-- 明年，后一年 2020
YEAR(DATE_SUB(NOW(), INTERVAL -1 YEAR))

计算月份

-- 当前月 5
MONTH(NOW())
-- 上个月 4
MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
-- 下个月 6
MONTH(DATE_SUB(NOW(),INTERVAL -1 MONTH))

计算天数

-- 月中天数 14
DAY(DATE_SUB(NOW(), INTERVAL 0 DAY))
-- 昨天：前一天 13
DAY(DATE_SUB(NOW(), INTERVAL 1 DAY))
-- 明天：后一天 15
DAY(DATE_SUB(NOW(), INTERVAL -1 DAY))

计算小时

-- 当前小时数 18
HOUR(DATE_SUB(NOW(), INTERVAL 0 HOUR))
-- 前一小时 17
HOUR(DATE_SUB(NOW(), INTERVAL 1 HOUR))
-- 后一小时 19
HOUR(DATE_SUB(NOW(), INTERVAL -1 HOUR))

计算分钟

-- 当前分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL 0 MINUTE))
-- 30分钟前的分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL 30 MINUTE))
-- 30分钟后的分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL -30 MINUTE))

日期查询

查询游戏今日登录用户数

SELECT 
COUNT(1) AS cnt 
FROM game_user
 WHERE 1=1 
AND TO_DAYS(NOW()) = TO_DAYS(FROM_UNIXTIME(logintime))

查询游戏昨日登录用户数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(logintime)) <= 1

查询游戏最近7天用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(logintime)) <= 7

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(FROM_UNIXTIME(logintime))

查询游戏本周登录用户数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND YEARWEEK(NOW()) = YEARWEEK(FROM_UNIXTIME(logintime,'%Y-%m-%d'))

查询游戏上周用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND YEARWEEK(NOW()) - YEARWEEK(FROM_UNIXTIME(logintime,'%Y-%m-%d')) = 1

查询游戏本月登录用户数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND DATE_FORMAT(CURDATE(), '%Y%m') = FROM_UNIXTIME(logintime, '%Y%m')

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND MONTH(NOW()) - MONTH(FROM_UNIXTIME(logintime)) = 0

查询游戏上月登录用户数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), FROM_UNIXTIME(logintime, '%Y%m')) = 1

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND MONTH(NOW()) - MONTH(FROM_UNIXTIME(logintime)) = 1

查询游戏本季度用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND QUARTER(NOW()) = QUARTER(FROM_UNIXTIME(logintime))

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND QUARTER(NOW()) - QUARTER(FROM_UNIXTIME(logintime)) = 0

查询游戏上季度用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER)) = QUARTER(FROM_UNIXTIME(logintime))

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND QUARTER(NOW()) - QUARTER(FROM_UNIXTIME(logintime)) = 1

查询游戏今年用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND YEAR(NOW()) - YEAR(FROM_UNIXTIME(logintime)) = 0

查询游戏去年用户登录数

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND YEAR(NOW()) - YEAR(FROM_UNIXTIME(logintime)) = 1

SELECT 
COUNT(1) AS cnt 
FROM game_user 
WHERE 1=1 
AND YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) = YEAR(FROM_UNIXTIME(logintime))

未完待续...