Python社区  »  DATABASE

MySQL 日期时间

JunChow520 • 1 周前 • 20 次点击  

数据类型

MySQL中有5中表示时间值的数据类型,分别是DATETIMEYEARDATETIMETIMESTAMP

  • 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_FORMATTIME_FORMAT日期时间转换函数可以将一个日期或时间转换成各种字符串格式。
  • DATE_FORMATTIME_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))

未完待续...

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/32999
 
20 次点击  
分享到微博