MySQL 时间、日期、时间戳 函数总结
站长
· 阅读数 15
背景
微信技(mo)术(yu)群里看到一张图片, 是考察
MySQL的知识点. 扫了一眼, 很简单啊 ! 实战下吧...尴尬了PHP的时间日期转换会用, 发现MySQL的日期时间转换想不起来了...
知识回顾
获取当前日期时间
-
获得当前日期+时间 (date + time) 函数:
now();mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-08-02 17:30:28 | +---------------------+ 1 row in set (0.00 sec) -
获取当前日期+时间 (date + time) 函数:
sysdate();sysdate()日期函数和now()类似, 不同之处在于:now()在执行开始的时候就得到值了,sysdate()在函数执行时动态得到的值. 举个栗子:mysql> select now(), sleep(3), now(); +---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2021-08-02 17:36:53 | 0 | 2021-08-02 17:36:53 | +---------------------+----------+---------------------+ 1 row in set (3.01 sec) mysql> select sysdate(), sleep(3), sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | +---------------------+----------+---------------------+ | 2021-08-02 17:37:24 | 0 | 2021-08-02 17:37:27 | +---------------------+----------+---------------------+ 1 row in set (3.00 sec) -
获取当前时间函数:
current_timestamp,current_timestamp()current_timestamp,current_timestamp()结果是一致的mysql> select current_timestamp, current_timestamp(); +---------------------+---------------------+ | current_timestamp | current_timestamp() | +---------------------+---------------------+ | 2021-08-02 17:41:37 | 2021-08-02 17:41:37 | +---------------------+---------------------+ 1 row in set (0.00 sec)
日期转换函数, 时间转换函数
-
日期/时间转字符串函数:
date_format(date, format),time_format(time, format)# date_format 可以格式日期和时间 mysql> select date_format('2021-08-02 17:50:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | date_format('2021-08-02 17:50:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 20210802175001 | +----------------------------------------------------+ 1 row in set (0.00 sec) # time_format 只可以格式时间 mysql> select time_format('2021-08-02 17:50:01', '%r'); +------------------------------------------+ | time_format('2021-08-02 17:50:01', '%r') | +------------------------------------------+ | 05:50:01 PM | +------------------------------------------+ 1 row in set (0.00 sec) -
字符串转换日期 函数:
str_to_date(str, format)mysql> select str_to_date('09.02.2020', '%m.%d.%Y'); +---------------------------------------+ | str_to_date('09.02.2020', '%m.%d.%Y') | +---------------------------------------+ | 2020-09-02 | +---------------------------------------+ 1 row in set (0.20 sec) mysql> select str_to_date('08.09.2020 08:09:30', '%m.%d.%Y %h:%i:%s'); +---------------------------------------------------------+ | str_to_date('08.09.2020 08:09:30', '%m.%d.%Y %h:%i:%s') | +---------------------------------------------------------+ | 2020-08-09 08:09:30 | +---------------------------------------------------------+ 1 row in set (0.00 sec)可以看出,
str_to_date(str, format)转换函数, 可以把一些杂乱无章的字符串转换为日期格式.可选参数
format 说明 %a 工作日的缩写名称 (Sun..Sat) %b 月份的缩写名称 (Jan..Dec) %c 月份, 数字形式 (0...12) %D 带有英语后缀的该月日期 (0th, 1st, 2nd,...) %d 该月日期, 数字形式 (00...31) %e 该月日期, 数字形式 (0...31) %f 微秒 (000000...999999) %H 小时 (00...23) %h 小时 (01...12) %I 小时 (01...12) %i 分钟 (00...59) %j 一年中的天数 (0001-366) %k 小时 (0...23) %l 小时 (1..12) %M 月份名称 (January..December)) %m 月份, 数字形式 (00..12) %p 上午（AM）或下午（ PM） %r 时间, 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM) %S 秒 (00...59) %s 秒 (00...59) %T 时间, 24小时制 (小时hh:分钟mm:秒数ss) %U 周(01...53), 其中周日为每周的第一天; %u 周(01...53), 其中周一为每周的第一天; %V 周(01...53), 其中周日为每周的第一天; 和 %X 同时使用 %v 周(01...53), 其中周一为每周的第一天; 和 %x 同时使用 %W 工作日名称 (周日...周六) %w 一周的每日 (0 = 周日 ... 6 = 周六) %X 该周的年份, 其中周日为每周的第一天, 数字形式; 和 %V 同时使用 %x 该周的年份, 其中周一为每周的第一天, 数字形式; 和 %v 同时使用 %Y 年份, 数字形式 (4位数) %y 年份, 数字形式 (2位数) %% '%'文字字符 -
日期, 天数转换函数:
to_days(date),from_days(days)# to_days() mysql> select to_days('1970-01-01'); +-----------------------+ | to_days('1970-01-01') | +-----------------------+ | 719528 | +-----------------------+ 1 row in set (0.00 sec) mysql> select to_days('0001-01-01'); +-----------------------+ | to_days('0001-01-01') | +-----------------------+ | 366 | +-----------------------+ 1 row in set (0.00 sec) # from_days()可以看出来
to_days()函数, 返回的是一个天数!从年份 0 开始的天数 -
时间, 秒 转换函数:
time_to_sec(time),sec_to_time(seconds)# 时间转换成秒 mysql> select time_to_sec('00:00:09'); +-------------------------+ | time_to_sec('00:00:09') | +-------------------------+ | 9 | +-------------------------+ 1 row in set (0.00 sec) # 秒转换成时间 mysql> select sec_to_time(9); +----------------+ | sec_to_time(9) | +----------------+ | 00:00:09 | +----------------+ 1 row in set (0.00 sec)两个函数是相互的
-
设定日期, 时间函数:
makedate(year, dayfoyear),maketime(hour, minute, second)# makedate(); 第二个参数是一年中第 ? 天 mysql> select makedate(2021, 91); +--------------------+ | makedate(2021, 91) | +--------------------+ | 2021-04-01 | +--------------------+ 1 row in set (0.00 sec) # maketime(); mysql> select maketime(23, 01, 30); +----------------------+ | maketime(23, 01, 30) | +----------------------+ | 23:01:30 | +----------------------+ 1 row in set (0.00 sec) -
时间戳, 日期转换函数:
unix_timestamp(),unix_timestamp(date),from_unixtime(unix_timestamp),from_unixtime(unix_timestamp, format)# 当前时间戳 mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1627956774 | +------------------+ 1 row in set (0.01 sec) # 指定时间的时间戳 mysql> select unix_timestamp('2020-09-09 12:00:00'); +---------------------------------------+ | unix_timestamp('2020-09-09 12:00:00') | +---------------------------------------+ | 1599624000 | +---------------------------------------+ 1 row in set (0.00 sec) # 时间戳转换成日期 mysql> select from_unixtime(1599624000); +---------------------------+ | from_unixtime(1599624000) | +---------------------------+ | 2020-09-09 12:00:00 | +---------------------------+ 1 row in set (0.00 sec) # 时间戳转换成指定格式日期 mysql> select from_unixtime(1599624000, '%Y-%D-%M %h:%i:%s'); +------------------------------------------------+ | from_unixtime(1599624000, '%Y-%D-%M %h:%i:%s') | +------------------------------------------------+ | 2020-9th-September 12:00:00 | +------------------------------------------------+ 1 row in set (0.00 sec)
日期时间计算函数
-
日期增加一个时间间隔函数:
date_add()mysql> set @dt = now(); Query OK, 0 rows affected (0.00 sec) # 增加一天 mysql> select date_add(@dt, interval 1 day); +-------------------------------+ | date_add(@dt, interval 1 day) | +-------------------------------+ | 2021-08-04 11:01:37 | +-------------------------------+ 1 row in set (0.00 sec) # 增加一小时 mysql> select date_add(@dt, interval 1 hour); +--------------------------------+ | date_add(@dt, interval 1 hour) | +--------------------------------+ | 2021-08-03 12:01:37 | +--------------------------------+ 1 row in set (0.00 sec) # 增加一周 mysql> select date_add(@dt, interval 1 week); +--------------------------------+ | date_add(@dt, interval 1 week) | +--------------------------------+ | 2021-08-10 11:01:37 | +--------------------------------+ 1 row in set (0.00 sec) # 增加一季度 mysql> select date_add(@dt, interval 1 quarter); +-----------------------------------+ | date_add(@dt, interval 1 quarter) | +-----------------------------------+ | 2021-11-03 11:01:37 | +-----------------------------------+ 1 row in set (0.00 sec) # 减少一天 mysql> select date_add(@dt, interval -1 day); +--------------------------------+ | date_add(@dt, interval -1 day) | +--------------------------------+ | 2021-08-02 11:01:37 | +--------------------------------+ 1 row in set (0.00 sec) -
adddate(),addtime()函数, 可以用 date_add() 来替代.mysql> set @dt = '2020-08-20 19:30:40'; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@dt, interval '01:15:30' hour_second); +------------------------------------------------+ | date_add(@dt, interval '01:15:30' hour_second) | +------------------------------------------------+ | 2020-08-20 20:46:10 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add(@dt, interval '1 01:12:30' day_second); +-------------------------------------------------+ | date_add(@dt, interval '1 01:12:30' day_second) | +-------------------------------------------------+ | 2020-08-21 20:43:10 | +-------------------------------------------------+ 1 row in set (0.00 sec) -
为指定日期减去一个时间间隔函数:
date_sub()mysql> select date_sub('2020-09-09 09:09:00', interval '1 1:1:1' day_second); +----------------------------------------------------------------+ | date_sub('2020-09-09 09:09:00', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 2020-09-08 08:07:59 | +----------------------------------------------------------------+ 1 row in set (0.00 sec) -
两个日期, 时间相减函数: date(date1, date2), timediff(time1, time2)
# 日期相减, 返回天数 mysql> select datediff('2020-09-09', '2020-09-01'); +--------------------------------------+ | datediff('2020-09-09', '2020-09-01') | +--------------------------------------+ | 8 | +--------------------------------------+ 1 row in set (0.00 sec) # 时间相减, 返回时间差值 mysql> select timediff('2020-09-09 09:00:00', '2020-09-09 00:00:00'); +--------------------------------------------------------+ | timediff('2020-09-09 09:00:00', '2020-09-09 00:00:00') | +--------------------------------------------------------+ | 09:00:00 | +--------------------------------------------------------+ 1 row in set (0.00 sec)timediff()函数的两个参数类型必须相同 -
时间戳 (timestamp) 转换, 增加, 减少函数:
timestamp(date),timestamp(dt, time),timestampadd(unit, interval, datetime_expr),timestampdiff(unit, datetime_expr1, datetime_expr2)# timestamp(date) mysql> select timestamp('2020-09-09'); +-------------------------+ | timestamp('2020-09-09') | +-------------------------+ | 2020-09-09 00:00:00 | +-------------------------+ 1 row in set (0.00 sec) # timestamp(dt, time) mysql> select timestamp('2020-09-09 09:00:00', '01:00:00'); +----------------------------------------------+ | timestamp('2020-09-09 09:00:00', '01:00:00') | +----------------------------------------------+ | 2020-09-09 10:00:00 | +----------------------------------------------+ 1 row in set (0.00 sec) # timestampadd(unit, interval, datetime_expr) mysql> select timestampadd(day, 1, '2020-09-09 09:00:00'); +---------------------------------------------+ | timestampadd(day, 1, '2020-09-09 09:00:00') | +---------------------------------------------+ | 2020-09-10 09:00:00 | +---------------------------------------------+ 1 row in set (0.00 sec) # timestampdiff(unit, datetime_expr1, datetime_expr2), 返回结果以天为单位 mysql> select timestampdiff(day, '2020-09-09', '2020-09-01'); +------------------------------------------------+ | timestampdiff(day, '2020-09-09', '2020-09-01') | +------------------------------------------------+ | -8 | +------------------------------------------------+ 1 row in set (0.00 sec) # 返回结果以小时为单位 mysql> select timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00'); +-------------------------------------------------------------------+ | timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00') | +-------------------------------------------------------------------+ | -193 | +-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select datediff('2020-09-09', '2020-09-01'); +--------------------------------------+ | datediff('2020-09-09', '2020-09-01') | +--------------------------------------+ | 8 | +--------------------------------------+ 1 row in set (0.00 sec)timestampdiff()函数就比datediff()功能强多了，datediff()只能计算两个日期（date）之间相差的天数
时区 (timezone) 转换
-
时区转换函数:
convert_tz(dt, from_tz, to_tz)mysql> select convert_tz('2020-09-09 12:00:00', '+08:00', '+00:00'); +-------------------------------------------------------+ | convert_tz('2020-09-09 12:00:00', '+08:00', '+00:00') | +-------------------------------------------------------+ | 2020-09-09 04:00:00 | +-------------------------------------------------------+ 1 row in set (0.00 sec) -
也可以通过
date_add(),date_sub(),timestampadd()来实现mysql> select date_add('2020-09-09 12:00:00', interval -8 hour); +---------------------------------------------------+ | date_add('2020-09-09 12:00:00', interval -8 hour) | +---------------------------------------------------+ | 2020-09-09 04:00:00 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_sub('2020-09-09 12:00:00', interval 8 hour); +---------------------------------------------------+ | date_add('2020-09-09 12:00:00', interval -8 hour) | +---------------------------------------------------+ | 2020-09-09 04:00:00 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select timestampadd(hour, -8, '2020-09-09 12:00:00'); +-----------------------------------------------+ | timestampadd(hour, -8, '2020-09-09 12:00:00') | +-----------------------------------------------+ | 2020-09-09 04:00:00 | +-----------------------------------------------+ 1 row in set (0.00 sec)
解决思路
知识点回顾了, 再来解决问题
写出当月分组统计每个用户的成绩总和的 SQL 语句
select name, sum(score) from A
where
created >= UNIX_TIMESTAMP(FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-01 00:00:00'))
and
created <= UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59'))
group by (name);
如何优化表和 SQL
对于优化数据表, 有很多种方式.
但从目前图里的这个表, 目前只能想到在 name, created 字段添加索引
希望有更好答案的 大帅笔, 大漂亮 多多指点下
总结
MySQL 时间各种形式转换, 不难, 也不复杂.
需要经常使用, 长时间不用, 容易忘记...