MySQL 时间、日期、时间戳函数总结

背景

微信技(mo)术(yu)群里看到一张图片, 是考察 MySQL 的知识点. 扫了一眼, 很简单啊 ! 实战下吧...尴尬了 PHP 的时间日期转换会用, 发现 MySQL 的日期时间转换想不起来了...

b0446a8c67c8b74ae7d59e3ddab3ba7.png

知识回顾

获取当前日期时间

获得当前日期+时间 (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)

解决思路

知识点回顾了, 再来解决问题

b0446a8c67c8b74ae7d59e3ddaba7.png

写出当月分组统计每个用户的成绩总和的 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 时间各种形式转换, 不难, 也不复杂.

需要经常使用, 长时间不用, 容易忘记...

参考 :

MySQL 时间函数, 日期函数

mysql获取当天,昨天,本周,本月,上周,上月的起始时间

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,271评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,275评论 2赞 380
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,151评论 0赞 336
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,550评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,553评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,559评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,924评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,580评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,826评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,578评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,661评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,363评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,940评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,926评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,156评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,872评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,391评论 2赞 342

MySQL 时间、日期、时间戳 函数总结

背景