MySQL 時間、日期、時間戳 函數(shù)總結(jié)

背景

微信技(mo)術(shù)(yu)群里看到一張圖片, 是考察 MySQL 的知識點. 掃了一眼, 很簡單啊 ! 實戰(zhàn)下吧...尷尬了 PHP 的時間日期轉(zhuǎn)換會用, 發(fā)現(xiàn) MySQL 的日期時間轉(zhuǎn)換想不起來了...

b0446a8c67c8b74ae7d59e3ddab3ba7.png

知識回顧

獲取當(dāng)前日期時間

  • 獲得當(dāng)前日期+時間 (date + time) 函數(shù): now();

    mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-08-02 17:30:28 |
+---------------------+
1 row in set (0.00 sec)

  • 獲取當(dāng)前日期+時間 (date + time) 函數(shù): sysdate();

    sysdate() 日期函數(shù)和 now() 類似, 不同之處在于: now() 在執(zhí)行開始的時候就得到值了, sysdate() 在函數(shù)執(zhí)行時動態(tài)得到的值. 舉個栗子:

    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)

  • 獲取當(dāng)前時間函數(shù): current_timestamp, current_timestamp()

    current_timestamp,current_timestamp() 結(jié)果是一致的

    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)
日期轉(zhuǎn)換函數(shù), 時間轉(zhuǎn)換函數(shù)

  • 日期/時間轉(zhuǎn)字符串函數(shù): 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)

  • 字符串轉(zhuǎn)換日期 函數(shù): 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) 轉(zhuǎn)換函數(shù), 可以把一些雜亂無章的字符串轉(zhuǎn)換為日期格式.

可選參數(shù)

format 說明
%a 工作日的縮寫名稱 (Sun..Sat)
%b 月份的縮寫名稱 (Jan..Dec)
%c 月份, 數(shù)字形式 (0...12)
%D 帶有英語后綴的該月日期 (0th, 1st, 2nd,...)
%d 該月日期, 數(shù)字形式 (00...31)
%e 該月日期, 數(shù)字形式 (0...31)
%f 微秒 (000000...999999)
%H 小時 (00...23)
%h 小時 (01...12)
%I 小時 (01...12)
%i 分鐘 (00...59)
%j 一年中的天數(shù) (0001-366)
%k 小時 (0...23)
%l 小時 (1..12)
%M 月份名稱 (January..December))
%m 月份, 數(shù)字形式 (00..12)
%p 上午（AM）或下午（ PM）
%r 時間, 12小時制 (小時hh:分鐘mm:秒數(shù)ss 后加 AM或PM)
%S 秒 (00...59)
%s 秒 (00...59)
%T 時間, 24小時制 (小時hh:分鐘mm:秒數(shù)ss)
%U 周(01...53), 其中周日為每周的第一天;
%u 周(01...53), 其中周一為每周的第一天;
%V 周(01...53), 其中周日為每周的第一天; 和 %X 同時使用
%v 周(01...53), 其中周一為每周的第一天; 和 %x 同時使用
%W 工作日名稱 (周日...周六)
%w 一周的每日 (0 = 周日 ... 6 = 周六)
%X 該周的年份, 其中周日為每周的第一天, 數(shù)字形式; 和 %V 同時使用
%x 該周的年份, 其中周一為每周的第一天, 數(shù)字形式; 和 %v 同時使用
%Y 年份, 數(shù)字形式 (4位數(shù))
%y 年份, 數(shù)字形式 (2位數(shù))
%% '%'文字字符

  • 日期, 天數(shù)轉(zhuǎn)換函數(shù): 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() 函數(shù), 返回的是一個天數(shù)!從年份 0 開始的天數(shù)

  • 時間, 秒 轉(zhuǎn)換函數(shù): time_to_sec(time), sec_to_time(seconds)

    # 時間轉(zhuǎn)換成秒
mysql> select time_to_sec('00:00:09');
+-------------------------+
| time_to_sec('00:00:09') |
+-------------------------+
|                       9 |
+-------------------------+
1 row in set (0.00 sec)

# 秒轉(zhuǎn)換成時間
mysql> select sec_to_time(9);
+----------------+
| sec_to_time(9) |
+----------------+
| 00:00:09       |
+----------------+
1 row in set (0.00 sec)

    兩個函數(shù)是相互的

  • 設(shè)定日期, 時間函數(shù): makedate(year, dayfoyear), maketime(hour, minute, second)

    # makedate(); 第二個參數(shù)是一年中第 ? 天
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)

  • 時間戳, 日期轉(zhuǎn)換函數(shù): unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp, format)

    # 當(dāng)前時間戳
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)


# 時間戳轉(zhuǎn)換成日期
mysql> select from_unixtime(1599624000);
+---------------------------+
| from_unixtime(1599624000) |
+---------------------------+
| 2020-09-09 12:00:00       |
+---------------------------+
1 row in set (0.00 sec)


# 時間戳轉(zhuǎn)換成指定格式日期
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)
日期時間計算函數(shù)

  • 日期增加一個時間間隔函數(shù): 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() 函數(shù), 可以用 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)

  • 為指定日期減去一個時間間隔函數(shù): 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)

  • 兩個日期, 時間相減函數(shù): date(date1, date2), timediff(time1, time2)

    # 日期相減, 返回天數(shù)
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() 函數(shù)的兩個參數(shù)類型必須相同

  • 時間戳 (timestamp) 轉(zhuǎn)換, 增加, 減少函數(shù): 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), 返回結(jié)果以天為單位
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)

# 返回結(jié)果以小時為單位
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() 函數(shù)就比 datediff() 功能強多了，datediff() 只能計算兩個日期（date）之間相差的天數(shù)

時區(qū) (timezone) 轉(zhuǎn)換

  • 時區(qū)轉(zhuǎn)換函數(shù): 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() 來實現(xiàn)

    
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
寫出當(dāng)月分組統(tǒng)計每個用戶的成績總和的 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);
如何優(yōu)化表和 SQL

對于優(yōu)化數(shù)據(jù)表, 有很多種方式.

但從目前圖里的這個表, 目前只能想到在 name, created 字段添加索引

希望有更好答案的 大帥筆, 大漂亮 多多指點下

總結(jié)

MySQL 時間各種形式轉(zhuǎn)換, 不難, 也不復(fù)雜.

需要經(jīng)常使用, 長時間不用, 容易忘記...

參考 :

MySQL 時間函數(shù), 日期函數(shù)

mysql獲取當(dāng)天,昨天,本周,本月,上周,上月的起始時間

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容