MySQL時間分組查詢

標籤：style   blog   http   color   使用   ar   for   strong   sp   

表TESTER

欄位：id　　-- INT

　　   date  -- TIMESTAMP

　　

1、如何按年、月、日分組查詢？

     select DATE_FORMAT(date,‘%Y-%m-%d‘) time, count(*) count from TESTER group by year(date), month(date), day(date);

     其中year()、month()、day()分別是提取date中的年、月、日欄位。

 

2、時間分組查詢的效率？

　　在不建立索引時，我100W行資料進行測試，用了以下幾種寫法，得到效能：

　　NO INDEX 　　select DATE_FORMAT(date,‘%Y-%m-%d‘) time,count(*) count from TESTER group by time 　　　　　　　　　　　　　　　　   　　returned in 1.315 sec
　　NO INDEX 　　select DATE_FORMAT(date,‘%Y-%m-%d‘) time,count(*) count from TESTER group by year(time),month(time),day(time)　　 returned in 1.045 sec
　　NO INDEX 　　select DATE_FORMAT(date,‘%Y-%m-%d‘) time,count(*) count from TESTER group by year(date),month(date),day(date) 　　returned in 0.624 sec

　　

　　很明顯，直接使用date欄位是最優的。在建立索引後：

　　INDEX ON date　　select DATE_FORMAT(date,‘%Y-%m-%d‘) time,count(*) count from TESTER group by year(date),month(date),day(date)　　　　returned in 0.578 sec

　　發現與不建索引時差別不算太大，不過當資料量達到千萬層級是，提升還是很可觀的。

 

3、如果需要返回Unix時間怎麼做？

　　測試在無索引模式下進行：
　　返回秒：　　select UNIX_TIMESTAMP(DATE_FORMAT(date,‘%Y-%m-%d‘)) time,count(*) count from svndb.TlocTask group by year(date),month(date),day(time)　　　　　　returned in 0.640 sec
　　返回毫秒：　select UNIX_TIMESTAMP(DATE_FORMAT(date,‘%Y-%m-%d‘))*1000 time,count(*) count from svndb.TlocTask group by year(date),month(date),day(time)　　　returned in 0.640 sec

　　可見，效能還是不錯的，可以不用拉到後台去計算。

 

4、關於DATE_FORMAT(date,format)  函數
  根據format字串格式化date值。下列修飾符可以被用在format字串中：
  %M     月名字(January……December)      
  %W     星期名字(Sunday……Saturday)      
  %D     有英語首碼的月份的日期(1st,   2nd,   3rd,   等等。）    
  %Y     年,   數字,   4   位    
  %y     年,   數字,   2   位    
  %a     縮寫的星期名字(Sun……Sat)      
  %d     月份中的天數,   數字(00……31)      
  %e     月份中的天數,   數字(0……31)      
  %m     月,   數字(01……12)      
  %c     月,   數字(1……12)      
  %b     縮寫的月份名字(Jan……Dec)      
  %j     一年中的天數(001……366)      
  %H     小時(00……23)      
  %k     小時(0……23)      
  %h     小時(01……12)      
  %I     小時(01……12)      
  %l     小時(1……12)      
  %i     分鐘,   數字(00……59)      
  %r     時間,12   小時(hh:mm:ss   [AP]M)      
  %T     時間,24   小時(hh:mm:ss)      
  %S     秒(00……59)      
  %s     秒(00……59)      
  %p     AM或PM      
  %w     一個星期中的天數(0=Sunday   ……6=Saturday   ）    
  %U     星期(0……52),   這裡星期天是星期的第一天    
  %u     星期(0……52),   這裡星期一是星期的第一天    
  %%     一個文字“%”。

 

5、MySQL中時間能直接相減嗎？

　　答案是否定的，比如表中有time1、time2兩個欄位，都是時間類型，想算它們之間的差別（秒為單位）

　　time1=2014-01-01

　　time2=2014-01-02

　　當使用這個判斷條件 where (time2 - time1 > 36)時，就會踩坑，MySQL並不會把真正的時間相減，而是拼接起來組成字串相減 ‘20140101‘ - ‘20140102‘，這個結果是沒有實際意義的。

　　那麼當想要算時間差時，有如下三種方法：

　　TIME_TO_SEC(TIMEDIFF(t2, t1)) 

　　TIMESTAMPDIFF(second, t1, t2) 　　-- 注意，這裡t1在前

      UNIX_TIMESTAMP(t2) - UNIX_TIMESTAMP(t1)　　就能得到t2-t1的正確時間差，單位為秒。  6、一個tips： js字串型的時間格式怎麼轉為Date？　　要用2014/10/13 00:00:00或2014/10/13 0:0:0或2014/10/13這樣的格式才能正確完成，如new Date(‘2014/10/13‘)，否則在IE瀏覽器下，會得到的錯誤的轉換結果。　　

　　好了，這次的部落格比較短，採用了問答的形式，看看效果吧。

　　轉載請註明原址：http://www.cnblogs.com/lekko/p/4023113.html 

MySQL時間分組查詢