總結工作中用到的SQL

• 熱門SQL
• • 處理時間的函數(shù)
  • • • 字符串轉換為日期格式
      • 將時間、日期轉換為字符串 日期轉換函數(shù)、時間轉換函數(shù)
      • **time_format 規(guī)定日時間的輸出格式。**
      • ***format* 規(guī)定日期/時間的輸出格式。**
      • MySQL 日期時間計算函數(shù)
      • **日期、時間相減函數(shù)：datediff(date1,date2), timediff(time1,time2)**
      • time_to_sec(timediff (time1，time2))
      • mysql 判斷閏年
      • MySQL根據(jù)出生日期計算年齡
      • **時間戳（timestamp）轉換、增、減函數(shù)：**
      • MySQL 時區(qū)（timezone）轉換函數(shù)
      • 關于 date_add()等type的取值
      • 日期和時間函數(shù)圖鑒
  • 日期類總結
• 冷門SQL
• [MySQL 格式化日期函數(shù) DATE_FORMAT(), FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 之間區(qū)別](https://blog.wpjam.com/m/mysql-date_format-from_unixtime-unix_timestamp/)
• • • • 時間時間戳互轉工具
• 參考網(wǎng)址推薦
• 詳細練習
• • • • 時間函數(shù)練習
• 動態(tài)sql語句基本語法：https://bbs.csdn.net/topics/280007702
• 動態(tài)sql語句基本語法：https://bbs.csdn.net/topics/280007702

注：本文僅僅是我工作用常用到sql，只是冰山一角，后續(xù)會持續(xù)更新，逐漸補全，先總結出來一起學習共同進步

熱門SQL

處理時間的函數(shù)

先了解幾個常用的獲取當前時間的函數(shù)輸出

#根據(jù)你想要的時間格式選擇對應函數(shù) select curdate(); -- 2021-11-11; select CURRENT_DATE(); -- 2021-11-11; select CURRENT_TIME(); -- 17:13:58; select CURRENT_TIMESTAMP(); -- 2021-11-11 17:13:58; select NOW(); -- 2021-11-11 17:13:58; select sysdate(); -- 2021-11-11 17:13:58; 一般情況下很少用到。需要時分秒時不建議使用 SELECT DATE(SYSDATE());--格式為年月日 上面的都可以放到里面 -- sysdate() 日期時間函數(shù)跟 now() 類似，不同之處在于：now() 在執(zhí)行開始時值就得到了， sysdate() 在函數(shù)執(zhí)行時動態(tài)得到值。 select now(), sleep(3), now();-- 2021-11-25 12:40:38 0 2021-11-25 12:40:38 select sysdate(), sleep(3), sysdate(); -- 2021-11-25 12:40:41 0 2021-11-25 12:40:44 #MySQL 獲得當前時間戳函數(shù)：current_timestamp, current_timestamp() select current_timestamp,sleep(3), current_timestamp();-- 2021-11-25 12:43:23 0 2021-11-25 12:43:23select utc_timestamp(), utc_date(), utc_time(), now(),CURRENT_TIME;-- 2021-11-26 19:04:54 2021-11-26 19:04:54 2021-11-27 03:04:54 03:04:54 相差8小時 select utc_timestamp(),DATE_FORMAT(utc_timestamp(),'%Y-%m-%d %I:%i:%s'),now();-- 2021-11-26 19:16:06 2021-11-26 07:16:06 2021-11-27 03:16:06 #因為我國位于東八時區(qū)，所以本地時間 = UTC 時間 + 8 小時。UTC 時間在業(yè)務涉及多個國家和地區(qū)的時候，非常有用。 #注：返回當前UTC日期和時間作為'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的一個值，根據(jù)函數(shù)是否用在字符串或數(shù)字語境中。 #一個使用技巧 注意結合使用 SELECT CURDATE() + 0; -- 20211125 直接得到純數(shù)字 SELECT '2021-11-26 18:45:28' + 0; -- 2021 字符串只能從頭截取到非數(shù)字部分的數(shù)值

求天數(shù)：兩段日期之間的天數(shù)差

#方法一： to_days()函數(shù) 返回日期和年份0之間的天數(shù)（日期“0000-00-00”）注：只能用于公歷中的日期 #語法：TO_DAYS(date) 參數(shù)必須項，給定的日期。 SELECT NOW();-- 2021-11-25 10:15:44 SELECT TO_DAYS('2021-11-25'); -- 738484 SELECT TO_DAYS( NOW()); -- 738484 -- 注意：MySQL將日期中的兩位數(shù)年份值轉換為四位數(shù)形式 。 例如， '2021-11-25'、 '21-11-25'和211125被看作是相同的日期： #TO_DAYS()不適用于公歷日歷（1582）出現(xiàn)之前的值，因為它不考慮日歷更改時丟失的日期。在1582年之前的日期（可#能在其他地區(qū)的其他年份），此功能的結果不可靠。 SELECT TO_DAYS(20211125); -- 738484 SELECT TO_DAYS(211125); -- 738484 -- 注意：月份跟天數(shù)必須有才會算出天數(shù) 否則為空 SELECT TO_DAYS('0000-00-00'); -- NULL SELECT TO_DAYS('0000-00-01'); -- NULL SELECT TO_DAYS('0000-01-01'); -- 1 SELECT TO_DAYS('0001-00-00'); -- NULL SELECT TO_DAYS('0001-01-00'); -- NULL SELECT TO_DAYS('0001-01-01'); -- 366 #使用如下： SELECT TO_DAYS('2021-11-25') -TO_DAYS('2021-11-25') ;-- 0 SELECT TO_DAYS(now()) -TO_DAYS('2021-11-25') ; -- 0 SELECT TO_DAYS(now()) -TO_DAYS('2021-11-24') ; -- 1 SELECT TO_DAYS(now()) -TO_DAYS(20211124) ; -- 1 SELECT TO_DAYS(now()) -TO_DAYS(211124) ; -- 1#還可以這樣玩：但是不建議這么玩 除非適合這么用的時候，了解即可 一種思路 #有一說一，這個更強大畢竟精確到天數(shù) 日期范圍的事它都能處理 這塊不插入過多練習，獲取更多練習看下面詳細練習 -- 把日期轉換為一個數(shù)字 select * from 表 where to_days（'字段名'）＝'2021-11-25' -- 如果要查詢當前表中今天、昨天的數(shù)據(jù) select * from admin where to_days(now())-to_days(create_time)=0; -- 今天的 select * from admin where to_days(now())-to_days(create_time)=1; -- 昨天的 依次類推 #聯(lián)合使用求某個時間段的天數(shù) 如昨天數(shù)據(jù)還可以這么求 select * from admin where to_days(now())-to_days(create_time)<2 -- 今天及昨天數(shù)據(jù) select * from admin where to_days(now())-to_days(create_time)>=1 -- 昨天之前的全部數(shù)據(jù) 包括昨天 select * from admin where to_days(now())-to_days(create_time)<2 and to_days(now())-to_days(create_time)>=1 -- 求個交集獲取到昨天獨有數(shù)據(jù)#獲取兩段時間天數(shù)還可以如下： SELECT DATEDIFF(now(),'2018-11-13') SELECT day(now())-DAY('2018-11-13') -- 這個可不行會忽略年

這塊擴展 注意： to_days()函數(shù)此函數(shù)與FROM_DAYS()函數(shù)相反 。

FROM_DAYS()函數(shù)使用方法及示例

#FROM_DAYS()函數(shù) 從數(shù)字日期值返回一個日期。僅與公歷中的日期一起使用。 #語法：FROM_DAYS(number) 參數(shù)需要，要轉換為日期的數(shù)字日 SELECT FROM_DAYS(now()); -- 0000-00-00 SELECT FROM_DAYS("2021-11-25"); -- 0005-07-14 SELECT FROM_DAYS(2021); -- 0005-07-14 # 正規(guī)使用如下： SELECT FROM_DAYS(738484); -- 2021-11-25 #指定日期1秒后： SELECT DATE_ADD('2021-11-25 23:59:59', INTERVAL 1 SECOND);-- 2021-11-26 00:00:00 select DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND);-- 2011-01-01 00:00:00 #指定日期1天后： select DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);-- 2011-01-01 23:59:59 #指定日期減去10小時： SELECT DATE_ADD('2011-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);-- 2010-12-30 14:00:00 #指定日期的一個月前： SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY);-- 2010-12-02 #指定日期的前一天： SELECT date_add('2011-01-01', INTERVAL -1 DAY);-- 2010-12-31 #將日期時間轉換成指定格式： DATE_FORMAT(date,format) #根據(jù)format 字符串安排date 值的格式。所有其它字符都被復制到結果中，無需作出解釋。注意，'%'字符要求在格式#指定符之前。月份和日期說明符的范圍從零開始，原因是 MySQL允許存儲諸如 '2004-00-00'的不完全日期。 SELECT FROM_UNIXTIME( 1290996580 ) -- 2010-11-29 10:09:40 SELECT DATE_FORMAT( FROM_UNIXTIME( 1290996580 ), '%Y-%m-%d %H:%i:%s' );-- 2010-11-29 10:09:40 SELECT DATE_FORMAT( FROM_UNIXTIME( 1290996580 ) , '%Y-%m-%d' );-- 2010-11-29 -- 下面兩個函數(shù)FROM_UNIXTIME(unix_timestamp) ;FROM_UNIXTIME(unix_timestamp,format);#語法： VARCHAR FROM_UNIXTIME(BIGINT unixtime[, VARCHAR format])#參數(shù)unixtime為長整型，是以秒為單位的時間戳。#參數(shù)format可選，為日期格式，默認格式為yyyy-MM-dd HH:mm:ss，表示返回VARCHAR類型的符合指定格式的日期，如果有參數(shù)為null或解析錯誤，則返回null。#返回值為VARCHAR類型的日期值，默認日期格式：yyyy-MM-dd HH:mm:ss，若指定日期格式按指定格式輸出任一輸入?yún)?shù)是NULL，返回NULL。 -- 返回'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS 格式值的unix_timestamp參數(shù)表示，具體格式取決于該函數(shù)是否用在字符串中或是數(shù)字語境中。 -- 若format 已經(jīng)給出，則結果的格式是根據(jù)format 字符串而定。 format 可以包含同DATE_FORMAT() 函數(shù)輸入項列表中相同的說明符。

字符串轉換為日期格式

可以看到，str_to_date(str,format) 轉換函數(shù)，可以把一些雜亂無章的字符串轉換為日期格式。

select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');-- 20080808222301 select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09 select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09 select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09 select str_to_date('08,2008,09', '%m,%Y,%d'); -- 2008-08-09 select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30 select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

將時間、日期轉換為字符串 日期轉換函數(shù)、時間轉換函數(shù)

（日期/時間轉換為字符串）函數(shù)：date_format(date,format), time_format(time,format)

能夠把一個日期/時間轉換成各種各樣的字符串格式。它是 （字符串轉換為日期）str_to_date(str,format) 函數(shù)的 一個逆轉換。

date_format，一種是time_format。 先說一下這兩者，time_format 只支持時分秒的格式化，對年月日時不起作用的，而 date_format對于年月日時分秒或者兩者分開都起作用。 所以使用date_format的即可知道有這個函數(shù)就行。 語法：select time_format(time, format_mask) time 必須項。格式化的時間 format_mask 必須項。要使用的格式。可以是以下之一或組合： SELECT now(); -- 2021-11-25 17:14:16 SELECT TIME_FORMAT(now(), "%H %i %s"); -- 17 14 16 SELECT DATE_FORMAT(now(), "%H %i %s"); -- 17 14 16

time_format 規(guī)定日時間的輸出格式。

格式描述格式描述

％H	小時（00到23）	％h	小時（00到12）
％I	小時（00到12）	％i	分鐘（00至59）
％r	時間為12小時AM或PM格式（hh：mm：ss AM / PM）	％p	上午或下午
％f	微秒（000000至999999）	％S	秒（00到59）
％s	秒（00到59）	％T	24小時格式的時間（hh：mm：ss）

format 規(guī)定日期/時間的輸出格式。

格式描述格式描述

%X	年，其中的星期日是周的第一天，4 位，與 %V 使用	%Y	年，4 位
%x	年，其中的星期一是周的第一天，4 位，與 %v 使用	%y	年，2 位
%b	縮寫月名	%c	月，數(shù)值
%M	月名	%m	月，數(shù)值(00-12)
%D	帶有英文前綴的月中的天	%d	月的天，數(shù)值(00-31)
%e	月的天，數(shù)值(0-31)	%j	年的天 (001-366)
%H	小時 (00-23)	%h	小時 (01-12)
%I	小時 (01-12)	%l	小時 (1-12)
%k	小時 (0-23)	%i	分鐘，數(shù)值(00-59)
%p	AM 或 PM	%r	時間，12-小時（hh:mm:ss AM 或 PM）
%S	秒(00-59)	%s	秒(00-59)
%f	微秒	%T	時間, 24-小時 (hh:mm:ss)
%a	縮寫星期名	%W	星期名
%U	周 (00-53) 星期日是一周的第一天	%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，與 %X 使用	%v	周 (01-53) 星期一是一周的第一天，與 %x 使用
%w	周的天 （0=星期日, 6=星期六）

MySQL （日期、天數(shù)）轉換函數(shù)：to_days(date), from_days(days) 上面有不再贅述

TO_DAYS(date)給出一個日期 `date`，返回一個天數(shù)(從 0 年開始的天數(shù))： FROM_DAYS(N)給出一個天數(shù) `N`，返回一個 `DATE` 值：

MySQL （時間、秒）轉換函數(shù)：time_to_sec(time), sec_to_time(seconds)

time_to_sec 的作用 將指定時間轉換為秒 語法：TIME_TO_SEC(time) time：傳入時間，如果傳入了日期部分，也不會管，只將時間部分轉換成秒 重點：是指將傳入的時間轉換成距離當天00:00:00的秒數(shù)，00:00:00為基數(shù)，等于 0 秒 ---------時間轉秒--------- select now(); -- 2021-11-25 17:32:39 select TIME_TO_SEC(now()); -- 63159 select TIME_TO_SEC('2021-11-25 17:32:39'); -- 63159 select TIME_TO_SEC('17:32:39'); -- 63159 select TIME_TO_SEC('00:00:00'); -- 0 select TIME_TO_SEC('00:01:00'); -- 60 #驗證：select 17*3600+32*60+39 -- 63159 ---------秒轉時間--------- select sec_to_time(63159); -- 17:32:39

MySQL 拼湊日期、時間函數(shù)：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2021,31); -- 2021-01-31 select makedate(2021,32); -- 2021-02-01 select makedate(2021,36); -- 2021-02-05select maketime(12,15,30); -- 12:15:30 select maketime( 17,32,39); -- 17:32:39

MySQL （Unix 時間戳、日期）轉換函數(shù)

-- unix_timestamp(), -- unix_timestamp(date), -- from_unixtime(unix_timestamp), -- from_unixtime(unix_timestamp,format) -- 這個非常有用，做自動化的同學都能看到，當然如果直接linux中比對，使用shell 中的date函數(shù)也可以。select unix_timestamp(); -- 1218169790 select unix_timestamp('2008-08-08'); -- 1218124800 select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800select from_unixtime(1218169790); -- 2008-08-08 12:29:50 select from_unixtime(1218124800); -- 2008-08-08 00:00:00 select from_unixtime(1218169800); -- 2008-08-08 12:30:00select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008' -- 先計算兩個時間差 SELECT (UNIX_TIMESTAMP('2020-12-20 12:09:23.123') - UNIX_TIMESTAMP('2020-12-20 12:09:23.121'))*1000 -- 2.000

MySQL 日期時間計算函數(shù)

-- month 月份 minute 分鐘 second 秒 hour 小時 week 周 quarter 刻 year 年 select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) -- 1997-12-30 22:58:59 select date_sub('2021-11-25 17:32:39', interval '1 1:1:1' day_second) -- 2021-11-24 16:31:38#MySQL 為日期增加一個時間間隔：date_add() set @dt = now(); select date_add(@dt, interval 1 day); -- 2021-11-26 19:43:19 select date_add(@dt, interval 1 hour); -- 2021-11-25 20:43:19 select date_add(@dt, interval 1 minute);-- 2021-11-25 19:44:19 select date_add(@dt, interval 1 second); -- 2021-11-25 19:43:20 select date_add(@dt, interval 1 microsecond);-- 2021-11-25 19:43:19.000001 select date_add(@dt, interval 1 week);-- 2021-12-02 19:43:19 select date_add(@dt, interval 1 month);-- 2021-12-25 19:43:19 select date_add(@dt, interval 1 quarter); -- 2022-02-25 19:43:19 select date_add(@dt, interval 1 year);-- 2022-11-25 19:43:19 select date_add(@dt, interval -1 day); -- 2021-11-24 19:43:19#MySQL adddate(), addtime()函數(shù)，可以用 date_add() 來替代。下面是 date_add() 實現(xiàn) addtime() 功能示例：set @dt = '2008-08-09 12:12:33';select date_add(@dt, interval '01:15:30' hour_second);-- 2008-08-09 13:28:03select date_add(@dt, interval '1 01:15:30' day_second);-- 2008-08-10 13:28:03 #MySQL 為日期減去一個時間間隔：date_sub() select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);-- 1997-12-30 22:58:59#對于某個日期加上n分鐘n秒 date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND)-- 2018-06-27 00:01:00 #對于某個日期加上n小時n分鐘n秒 select date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND)-- 2018-06-27 01:01:00 #對某個日期加上n小時n分鐘 select date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE);-- 2018-06-27 01:00:59 #對某個日期加上幾天幾小時幾分鐘幾秒鐘 select date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND);-- 2018-06-29 02:01:00 #ADDTIME函數(shù)：時間加法運算 將 expr2添加至expr 然后返回結果。 expr 是一個時間或時間日期表達式，而expr2 是一個時間表達式。 SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002'); -- 2008-01-02 01:01:01.000001 SELECT ADDTIME('2007-12-31 23:59:58', '1 1:1:1'); -- 2008-01-02 01:00:59 SELECT ADDTIME('01:00:00.999999', '02:00:00.999998'); -- 03:00:01.999997 SELECT ADDTIME('01:00:01', '02:00:01'); -- 03:00:02 select ADDTIME('2015-01-31 16:27:08',1);-- 2015-01-31 16:27:09 select ADDTIME('2015-01-31 16:27:08',59); -- 2015-01-31 16:28:07 select ADDTIME('10:30:59','5:10:37') -- 15:41:36-- date_add函數(shù)的type屬性可以動態(tài)么？ 下面這么寫會報錯 select num,DATE_ADD(now(),INTERVAL 2 (CASE num WHEN 1 THEN DAY WHEN 2 THEN MONTH WHEN 3 THEN YEAR ELSE YEAR END)) -- 換個思路修改為： select num , CASE num WHEN 1 THEN DATE_ADD(now(),INTERVAL 2 DAY)WHEN 2 THEN DATE_ADD(now(),INTERVAL 2 MONTH)WHEN 3 THEN DATE_ADD(now(),INTERVAL 2 YEAR)ELSE DATE_ADD(now(),INTERVAL 2 YEAR) END from admin

日期、時間相減函數(shù)：datediff(date1,date2), timediff(time1,time2)

前面操作日期得到天數(shù) 后面操作時間的 且參數(shù)都必須是相同的類型，不然結果為空

而TIMESTAMPDIFF比較有意思 能設置返回值屬性 并且是第二個日期減去第一個日期

select datediff('2008-08-01', '2008-08-08'); -- -7 select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08 select timediff('2008-08-09 08:08:08', '2008-08-08 00:00:00'); -- 32:08:08 select timediff('08:08:08', '00:00:00'); -- 08:08:08#datediff(date1,date2) 函數(shù) 值為date1- date2 得到一個天數(shù)整數(shù)差 時分秒會忽略掉 SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,-- 1DATEDIFF('2017-11-30','2017-12-15') AS col2, -- -15DATEDIFF('2017-11-30 08:08:08','2017-12-15') AS col3, -- -15DATEDIFF('2017-11-30 08:08:08','2017-12-15 08:08:06') AS col4; -- -15#timestampdiff函數(shù)日期或日期時間表達式之間的整數(shù)差。#語法：TIMESTAMPDIFF(interval，datetime1，datetime2)，比較的單位interval可以為以下數(shù)值#FRAC_SECOND。表示間隔是毫秒 SECOND。秒 MINUTE。分鐘 HOUR。小時 DAY。天#WEEK。星期 MONTH。月 QUARTER。季度 YEAR。年#請注意：DATEDIFF，TIMESTAMPDIFF對日期差值的計算方式剛好是相反的。#計算方式為datetime2-datetime1的差值。#留意跟時分秒掛鉤 會影響結果 少一秒你的結果就得減去1-- 根據(jù)需求 如果可以建議去掉時分秒 select TIMESTAMPDIFF(DAY,'2018-07-01 23:59:59','2018-07-04 23:59:59'); -- 3 select TIMESTAMPDIFF(DAY,'2018-07-01 23:59:59','2018-07-04 23:59:58'); -- 2 select TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58'); -- -1 select TIMESTAMPDIFF(second,'2018-07-04 23:59:58','2018-07-04 23:59:59'); -- 1select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:00','2018-07-31 23:59:59'); -- 0 select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:01','2018-08-01 00:00:00'); -- 0 select TIMESTAMPDIFF(MONTH,'2018-07-01 23:59:59','2018-08-01 23:59:58'); -- 0 select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:00','2018-08-01 00:00:00'); -- 1select TIMESTAMPDIFF(HOUR,'2018-07-04 23:00:00','2018-07-04 23:59:59'); -- 0 select TIMESTAMPDIFF(HOUR,'2018-07-04 23:00:00','2018-07-05 00:00:00'); -- 1select TIMESTAMPDIFF(WEEK,'2018-07-04 23:59:59','2018-07-11 23:59:58'); -- 0 select TIMESTAMPDIFF(WEEK,'2018-07-04 23:59:59','2018-07-11 23:59:59'); -- 1 select TIMESTAMPDIFF(WEEK,'2018-07-05 23:59:59','2018-07-12 00:00:00'); -- 0 select TIMESTAMPDIFF(WEEK,'2018-07-05 00:00:00','2018-07-12 00:00:00'); -- 1 select TIMESTAMPDIFF(WEEK,'2018-07-05 00:00:01','2018-07-12 00:00:00'); -- 0 -- 如果需要獲取大于時分秒的類型就用年月日格式 時分秒的依舊可以獲取到對應的值但值意義不大這時用時分秒年月日格式即可 select TIMESTAMPDIFF(HOUR,'2018-07-04','2018-07-05'); -- 24 select TIMESTAMPDIFF(WEEK,'2018-07-04','2018-07-11'); -- 1 -- 且兩者都必須是相同的類型，不然結果為空。 SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');-- 49:57:00 SELECT TIMEDIFF('2018-05-21','2018-05-19');-- 00:00:00 SELECT TIMEDIFF('14:51:43','12:54:43');-- 01:57:00

time_to_sec(timediff (time1，time2))

-- TIMESTAMPDIFF 跟 time_to_sec(timediff (time1，time2)) 都可以用來求秒數(shù) 但是有點不同 #但如果時間差比較大，超出timediff 的限制(timediff最大值：838:59:59即：34天22小時59分59秒)，得出的結果就是一樣的，因為timediff只計算時分秒，不計算 年月日 select time_to_sec(timediff('08:08:08','00:00:00')) -- 29288 = 8*60*60+8*60+8select time_to_sec(timediff('23:59:58','23:59:59')) -- -1SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');-- 49:57:00select time_to_sec(timediff('2018-05-21 14:51:43','2018-05-19 12:54:43')) -- 179820 = 49*60*60+57*60 select time_to_sec(TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58')) -- -1select TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58');-- -1select time_to_sec(TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:58:58')) -- null 只要年月日時分不一致就是空 跟TIMESTAMPDIFF配合使用的話年月日時分必須的保持一致 換句話TIMESTAMPDIFF函數(shù)我都已經(jīng)求出秒數(shù)了 也沒必要在搭配time_to_sec 畫蛇添足select TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-05 23:59:58') -- 86399select timediff('2018-06-21 23:59:59','2018-05-18 01:01:01') -- 838:58:58 這個多一秒永遠的值都是 838:59:59select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:01:01')) ;-- 3020338 = 838*60*60+58*60+58select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:01:00')) ;-- 3020339 這個多一秒永遠的值都是 3020339select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:00:00')) ;-- 3020399 #注：sql很活 都能拿到一致的結果情況下優(yōu)先使用最優(yōu)解函數(shù) -- 計算日期是星期幾 DAYOFWEEK(date) SELECT DAYOFWEEK('2021-11-26') AS '今天是星期幾'; -- 6 實際星期5 -- 計算本月是第幾天 DAYOFMONTH(date) SELECT DAYOFMONTH('2021-11-26') AS '本月第幾天';-- 26 -- 計算本年是第幾天DAYOFYEAR(date) SELECT DAYOFYEAR('2021-11-26') AS '本年第幾天';-- 330 最優(yōu)解 SELECT TO_DAYS('2021-11-26')-TO_DAYS('2021-01-01')+1 AS '本年第幾天';-- 330 SELECT DATEDIFF('2021-11-26','2021-01-01')+1 AS '本年第幾天';-- 330 SELECT TIMESTAMPDIFF(day,'2021-01-01','2021-11-26')+1 AS '本年第幾天';-- 330 -- 計算本年是第幾月份 MONTH(date) SELECT MONTH('2021-11-26') AS '本年第幾月份'; -- 11 -- 計算本年是第幾季度 QUARTER(date) SELECT QUARTER('2021-11-26') AS '本年第幾季度'; -- 4

mysql 判斷閏年

---

#判斷2月份是不是28天 SELECT DAY(LAST_DAY(DATE_ADD(DATE_ADD(DATE_ADD( CURRENT_DATE, INTERVAL - DAYOFYEAR( CURRENT_DATE ) DAY ),INTERVAL 1 DAY ),INTERVAL 1 MONTH ) ) ) #if判斷法： select IF(((YEAR(now()) % 4 = 0 AND YEAR(now()) % 100 != 0) OR YEAR(now()) % 400 = 0) ,0,1) date_add(date,interval if(day(birthday)=29&&day(date)=28,1,0) day)

MySQL根據(jù)出生日期計算年齡

#方法一：缺陷就是當日期為未來日期時結果為0，而不是負數(shù)；這里使用了5個函數(shù)和兩個運算符。 SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS('1994-04-28')), '%Y')+0 AS age #方法二：解決了方法一為負數(shù)的問題，但看起來更復雜；這里使用了6個函數(shù)和3個運算符。 SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT('1994-04-28', '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT('1994-04-28', '00-%m-%d')) AS age # 綜合一、二版本：=======================建議使用========================= SELECT year( from_days( datediff( now( ), '1994-04-28')));-- 一 SELECT YEAR(CURDATE())-YEAR('1994-04-28')-(RIGHT(CURDATE(),5)<RIGHT('1994-04-28',5));-- 二 #方法三：取生日和當前日期之前的天數(shù)除以一年的實際天數(shù)（365天5小時48分46秒），然后取整。這樣只用了三個函數(shù)和一個運算符就搞定了。 SELECT FLOOR(DATEDIFF(CURDATE(), '1994-04-28')/365.2422) #方法四 SELECT TIMESTAMPDIFF(YEAR,'1994-04-28', CURDATE()) #方法五 SELECT ROUND(DATEDIFF(CURDATE(), '1994-04-28')/365.2422)

時間戳（timestamp）轉換、增、減函數(shù)：

時間戳的更多知識點擊獲取

#注：踩坑點 TIMESTAMP值不能比1970早，也不能比2037晚，這意味著，一個日期例如'1968-01-01'，當作為一個 DATETIME或DATE值時它是合法的，但它不是一個正確TIMESTAMP值！并且如果將這樣的一個對象賦值給TIMESTAMP列，它將被變換為 0。 #當指定日期值時，當心某些缺陷： #1.允許作為字符串指定值的寬松格式能被欺騙。例如，因為“:”分隔符的使用，值'10:11:12'可能看起來像時間值，但是如果在一個日期中使用，上下文將作為年份被解釋成'2010-11-12'。值'10:45:15'將被變換到'0000-00-00'，因為'45'不是一個合法的月份。 #2.以2位數(shù)字指定的年值是模糊的，因為世紀是未知的。MySQL使用下列規(guī)則解釋2位年值：在00-69范圍的年值被變換到2000-2069。 在范圍70-99的年值被變換到1970-1999。三、當指定日期值時，當心某些缺陷：#時間戳是指格林威治時間1970年01月01日00時00分00秒(北京時間1970年01月01日08時00分00秒)起至現(xiàn)在的總秒數(shù)。 #在MySQL上述三個大版本中，默認時間戳(Timestamp)類型的取值范圍為'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC，數(shù)據(jù)精確到秒級別，該取值范圍包含約22億個數(shù)值，因此在MySQL內部使用4個字節(jié)INT類型來存放時間戳數(shù)據(jù)：#1、在存儲時間戳數(shù)據(jù)時，先將本地時區(qū)時間轉換為UTC時區(qū)時間，再將UTC時區(qū)時間轉換為INT格式的毫秒值(使用UNIX_TIMESTAMP函數(shù))，然后存放到數(shù)據(jù)庫中。 #2、在讀取時間戳數(shù)據(jù)時，先將INT格式的毫秒值轉換為UTC時區(qū)時間(使用FROM_UNIXTIME函數(shù))，然后再轉換為本地時區(qū)時間，最后返回給客戶端。 #在MySQL上述三個大版本中，默認時間戳(Timestamp)類型的取值范圍為'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC，數(shù)據(jù)精確到秒級別，該取值范圍包含約22億個數(shù)值，因此在MySQL內部使用4個字節(jié)INT類型來存放時間戳數(shù)據(jù)：#1、在存儲時間戳數(shù)據(jù)時，先將本地時區(qū)時間轉換為UTC時區(qū)時間，再將UTC時區(qū)時間轉換為INT格式的毫秒值(使用UNIX_TIMESTAMP函數(shù))，然后存放到數(shù)據(jù)庫中。 #2、在讀取時間戳數(shù)據(jù)時，先將INT格式的毫秒值轉換為UTC時區(qū)時間(使用FROM_UNIXTIME函數(shù))，然后再轉換為本地時區(qū)時間，最后返回給客戶端。時間戳字段定義主要影響兩類操作：在創(chuàng)建新記錄和修改現(xiàn)有記錄的時候都對這個數(shù)據(jù)列刷新 插入記錄時，時間戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入記錄時未指定具體時間數(shù)據(jù)則將該時間戳字段值設置為當前時間 更新記錄時，時間戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新記錄時未指定具體時間數(shù)據(jù)則將該時間戳字段值設置為當前時間 #語法1： timestamp(date) -- date to timestamp 語法2：timestamp(dt,time)-- dt + time select timestamp(now()); -- 2021-11-27 01:21:48 select timestamp(CURRENT_DATE); -- 2021-11-27 00:00:00 -- 時間部分被設置為'00:00:00'，因為DATE值中不包含有時間信息。 select timestamp('2008-08-08'); -- 2008-08-08 00:00:00 select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01 #語法：timestampadd(unit,interval,datetime_expr) unit值跟TIMESTAMPDIFF一致 select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00 select timestampadd(day, 1, '2008-08-08'); -- 2008-08-09 #語法 timestampdiff(unit,datetime_expr1,datetime_expr2) -- 上文已存在 #MySQL timestampadd() 函數(shù)類似于 date_add()。 select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00 #MySQL timestampdiff() 函數(shù)就比 datediff() 功能強多了，datediff() 只能計算兩個日期（date）之間相差的天數(shù)。select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1 select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485 select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12select datediff('2008-08-08 23:59:59', '2008-08-01 00:00:00'); -- 7 只看年月日 select datediff('2008-08-08', '2008-08-01'); -- 7

MySQL 時區(qū)（timezone）轉換函數(shù)

#語法：convert_tz(dt,from_tz,to_tz) select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00 SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');-- 2004-01-01 22:00:00 SELECT CONVERT_TZ('2020-11-19 19:59:00', '+00:00', '+05:30') -- 2020-11-20 01:29:00 SELECT CONVERT_TZ('2020-11-19', '+00:00', '+05:30') -- 2020-11-19 05:30:00select CONVERT_TZ('2020-11-19 10:53:00', '+00:00', '+05:30') ;-- 2020-11-19 16:23:00 select CONVERT_TZ('2020-11-19 10:53:00', '-05:00', '+05:30') ;-- 2020-11-19 21:23:00select CONVERT_TZ('2020-11-19 10:53:00' , '+00:00', '-05:00');-- 2020-11-19 05:53:00 select CONVERT_TZ('2020-11-19 10:53:00' , '+05:30', '-05:00');-- 2020-11-19 00:23:00 -- 總結：先算后再算前 后邊符號不變 前邊取相反符號 算出來是多少就是多少 #時區(qū)轉換也可以通過 date_add, date_sub, timestampadd 來實現(xiàn)。 select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00 select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00 select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

關于 date_add()等type的取值

date 參數(shù)是合法的日期表達式。expr 參數(shù)是您希望添加的時間間隔。

type 參數(shù)可以是下列值：

Type 值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

日期和時間函數(shù)圖鑒

日期類總結

獲得當前時間：NOW(); curdate(); CURRENT_DATE();(sysdate()用的少,在需要函數(shù)執(zhí)行時動態(tài)得到值在用它) CURTIME();獲取時分秒 獲得當前時間戳函數(shù)： CURRENT_TIME(); CURRENT_TIMESTAMP(); 日期/時間 轉換為字符串函數(shù)：date_format(date,format), time_format(time,format) 混亂字符串轉換為日期函數(shù)：str_to_date(str, format) 日期、天數(shù) 轉換函數(shù)：to_days(date), from_days(days) 時間、秒 轉換函數(shù)：time_to_sec(time), sec_to_time(seconds)

冷門SQL

MySQL 格式化日期函數(shù) DATE_FORMAT(), FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 之間區(qū)別

DATE_FORMAT() 把日期進行格式化，FROM_UNIXTIME() 把時間戳格式化成一個日期，UNIX_TIMESTAMP() 正好相反，把日期格式化成時間戳。

注：FROM_UNIXTIME 默認 年月日時分秒格式 如果需要其他日期格式需要在加上日期格式

#互轉 SELECT UNIX_TIMESTAMP(20101129100940), UNIX_TIMESTAMP(101129100940);-- 都為：1290996580 SELECT FROM_UNIXTIME(1290996580),FROM_UNIXTIME(1290996580,'%Y-%m-%d %H:%i:%s') as var10 ;-- 都為：2010-11-29 10:09:40SELECT UNIX_TIMESTAMP(20101129), UNIX_TIMESTAMP(101129);-- 都為：1290960000 SELECT FROM_UNIXTIME(1290960000),-- 2010-11-29 00:00:00 FROM_UNIXTIME(1290960000,'%Y-%m-%d') ;-- 2010-11-29 #FROM_UNIXTIME(unix_timestamp,format)函數(shù) #該參數(shù)是Unix 時間戳 #驗證 SELECT FROM_UNIXTIME(1290996580) as var1, -- 2010-11-29 10:09:40 FROM_UNIXTIME(1290996580,'MMdd-yyyy') as var2,-- MMdd-yyyy FROM_UNIXTIME(1290996580,'') as var3,-- 空FROM_UNIXTIME(1290996580,'nullstr') as var4, -- nullstrFROM_UNIXTIME(1290996580,null) as var5, -- 空FROM_UNIXTIME(1290996580,'%Y%m%d') as var6 ,-- 20101129FROM_UNIXTIME(1290996580,'%Y年%m月%d') as var7 ;-- 2010年11月29#UNIX_TIMESTAMP() 是與之相對正好相反的時間函數(shù) #若無參數(shù)調用，則返回一個 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒數(shù)) 作為無符號整數(shù)。若用date 來調用 UNIX_TIMESTAMP()，它會將參數(shù)值以'1970-01-01 00:00:00' GMT后的秒數(shù)的形式返回。date 可以是一個 DATE 字符串、一個 DATETIME字符串、一個 TIMESTAMP或一個當?shù)貢r間的YYMMDD 或YYYMMDD格式的數(shù)字。SELECT FROM_UNIXTIME( 1290996580 ) -- 2010-11-29 10:09:40SELECT UNIX_TIMESTAMP() ,-- 1637823867 UNIX_TIMESTAMP('2010-11-29'),-- 1290960000UNIX_TIMESTAMP(2010-11-29),-- 0UNIX_TIMESTAMP(20101129),-- 1290960000UNIX_TIMESTAMP(101129),-- 1290960000 -- UNIX_TIMESTAMP(2010-11-29 10:09:40),-- 報錯UNIX_TIMESTAMP(20101129100940),-- 1290996580UNIX_TIMESTAMP(101129100940);-- 1290996580SELECT FROM_UNIXTIME(1290960000) as var1, -- 2010-11-29 00:00:00FROM_UNIXTIME(1290960000,'MMdd-yyyy') as var2,-- MMdd-yyyy FROM_UNIXTIME(1290960000,'') as var3,-- 空FROM_UNIXTIME(1290960000,'nullstr') as var4, -- nullstrFROM_UNIXTIME(1290960000,null) as var5 ,-- 空FROM_UNIXTIME(1290960000,'%Y%m%d') as var6 ,-- 20101129FROM_UNIXTIME(1290960000,'%Y年%m月%d') as var7 ,-- 2010年11月29FROM_UNIXTIME(1290960000,'%Y-%m-%d %H:%m:%s') as var8 ,-- 2010-11-29 00:11:00FROM_UNIXTIME(1290960000,'%Y-%m-%d %H:%i:%s') as var9 ,-- 2010-11-29 00:00:00FROM_UNIXTIME(1290996580,'%Y-%m-%d %H:%i:%s') as var10 ;-- 2010-11-29 10:09:40

時間時間戳互轉工具

參考網(wǎng)址推薦

詳細練習

時間函數(shù)練習

Mysql查詢“當天、昨天、上周、本月、上一季、本年”的SQL語句

今天 select * from 表名 where to_days(時間字段名) = to_days(now()); 或者 select * from 表名 where to_days(時間字段名) -to_days(now())=0; 昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 時間字段名) <= 1 and TO_DAYS( NOW( ) ) - TO_DAYS( 時間字段名) != 0 上面固然可以實現(xiàn)畢竟很麻煩 建議使用更貼切的函數(shù) SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 1 DAY) = DATE_FORMAT(時間字段名,'%Y-%m-%d') -- 昨天數(shù)據(jù) (注意前后日期格式不一致時注意格式化一致)select DATE_SUB(CURDATE(), INTERVAL 1 DAY)-- 2021-11-24 -- 昨天select DATE_SUB(CURDATE(), INTERVAL 0 DAY)-- 2021-11-25 -- 今天select DATE_SUB(CURDATE(), INTERVAL -1 DAY)-- 2021-11-26 -- 后天近7天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(時間字段名)近30天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(時間字段名)本月 SELECT * FROM 表名 WHERE DATE_FORMAT( 時間字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )上一月 SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 時間字段名, '%Y%m' ) ) =1查詢本季度數(shù)據(jù) select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now())查詢上季度數(shù)據(jù) select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));查詢本年數(shù)據(jù) select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());查詢上年數(shù)據(jù) select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));查詢當前這周的數(shù)據(jù) SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());查詢上周的數(shù)據(jù) SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;查詢上個月的數(shù)據(jù) select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where pudate between 上月最后一天 and 下月第一天查詢當前月份的數(shù)據(jù) select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')查詢距離當前現(xiàn)在6個月的數(shù)據(jù) select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

動態(tài)sql語句基本語法：https://bbs.csdn.net/topics/280007702

https://bbs.csdn.net/topics/310162964?list=9107182

動態(tài)sql語句基本語法：https://bbs.csdn.net/topics/280007702

https://bbs.csdn.net/topics/310162964?list=9107182

作者：遠看寒山石徑斜鏈接：https://www.imooc.com/article/71440

總結

以上是生活随笔為你收集整理的关于sql中处理日期的相关函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。