Mysql時間函數

標籤：

http://blog.sina.com.cn/s/blog_6d39dc6f0100m7eo.html

 

mysql中函數和關鍵字不區分大小寫。下文函數的datetime參數處既可以用時間字串也可以用時間變數或運算式。

一、擷取目前時間

下面幾個方法等效，字串環境輸出格式為：YYYY-MM-DD HH:MM:SS，數字環境輸出格式為：YYYYMMDDHHMMSS

now()current_timestamp()   / current_timestamplocaltime() /localtime localtimestamp() /localtimestamp

 

sysdate()

其他方法執行開始時就得到值了，sysdate()方法在函數中動態獲得值。

 

 

CURDATE()

CURRENT_DATE() / CURRENT_DATE

這兩個方法只擷取日期，字串環境中返回HH:MM:SS格式，數字環境中返回HMMSS格式

 

CURTIME()

CURRENT_TIME() / CURRENT_TIME

這兩個方法只擷取時間，數字環境中返回YYYYMMDD格式

 

UTC_DATE() /UTC_DATE

UTC_TIME() /UTC_TIME

UTC_TIMESTAMP()/UTC_TIMESTAMP

返回UTC時間（世界統一時間，0時區，我國在東8區，所以比我國本地時間慢8小時， 本地時間=UTC時間+8小時）

 

 

二、擷取時間部分資料

Date(datetime)

返回日期 yyyy-MM-dd

 

Time(datetime)

返回時間 hh:mm:ss

 

Year(datetime)

Quarter(datetime)

Month(datetime)

MonthName(datetime)

Day(datetime)

Hour(datetime)

Minute(datetime)

Second(datetime)

MicroSecond(datetime)

 

DayName(datetime)

返回星期幾的名字

Week(datetime)

等於Week(datetime,0)

 

Week(datetime, mode)

mode	一周的第一天	範圍	Week 1為第一周...
0	周日	0～53	本年度中有一個周日
1	周一	0～53	本年度中有3天以上
2	周日	1～53	本年度中有一個周日
3	周一	1～53	本年度中有3天以上
4	周日	0～53	本年度中有3天以上
5	周一	0～53	本年度中有一個周一
6	周日	1～53	本年度中有3天以上
7	周一	1～53	本年度中有一個周一

YearWeek(datetime)

年和周數

 

Extract( unit From datetime)

unit取值：  year, quarter, month, week, day, hour, minute, second, microsecond

　　　　　  year_month, day_hour, day_minute, day_second, day_microsecond, hour_minute, hour_second, hour_microsecond, minute_second, minute_microsecond, second_microsecond

xxx_zzz格式的值表示選取的範圍xxxyyyzzz，而不是只選這兩項。

 

 

DayOfWeek(datetime)

datetime是一周的第幾天，（周日為一個星期的第一天,ODBC標準）

DayOfMonth(datetime)

datetime是一月的第幾天，1-31

DayofYear(datetime)

datetime是一年的第幾天，1-366

 

WeekDay(datetime)

datetime是一周中的第幾天，（周一為一個星期的第0天）

 

WeekOfYear(datetime)

datetime是一年中的第幾周，等於WEEK(datetime,3)。

 

Last_Day(date)

返回月份中最後一天

 

三、時間操作

+/-操作符

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  
-> 1998-01-01 00:00:00  

SELECT INTERVAL 1 DAY + "1997-12-31";  
-> 1998-01-01  

SELECT "1998-01-01" - INTERVAL 1 SECOND;  
-> 1997-12-31 23:59:59  

 

Date_Add(datetime, interval expr unit)

unit:   day, hour, minute, second, microsecond, week, month, quarter, year

　　year_month, day_hour, day_minute, day_second, day_microsecond, hour_minute, hour_second, hour_microsecond, minute_second, minute_microsecond, second_microsecond

xxx_zzz格式的值表示選取的範圍xxxyyyzzz，而不是只選這兩項。

 

樣本：

select DATE_ADD(now(),INTERVAL 3 DAY)

select DATE_ADD(now(),INTERVAL -2.5 hour)

select DATE_ADD(now(),INTERVAL ‘01:15:30‘ hour_second)

select DATE_ADD(now(),INTERVAL ‘- 2 01:15:30‘ day_second)

 

 

AddDate(datetime, INTERVAL expr unit)

AddDate(datetime, days)

AddTime(expr1, expr2)

 

Date_Sub(datetime, interval expr unit)

用法同Date_Add(datetime, interval expr unit)

 

SubDate(datetime, INTERVAL expr unit)

SubDate(datetime, days)

SubTime(expr1, expr2)

 

timestampAdd(uint, interval, datetime_expr)

 

Period_Add(P,N)

增加N個月到階段P，P的格式為YYMM或者YYYYMM

select PERIOD_ADD(9801,2);  -- 199803 

 

PERIOD_DIFF(P1,P2)

返回P1和P2相差的月數，P的格式為YYMM或者YYYYMM

 

DateDiff(datetime1, datetime2)

返回相差天數

 

TimeDiff(datetime1, datetime2)

返回相差時間（hh:mm:ss）,兩個參數格式必須相同

 

TimeStampDiff(unit,datetime1,datetime2)
返回日期或日期時間運算式datetime_expr1 和datetime_expr2the 之間的整數差。

unit參數指定結果單位，可用值為：

• FRAC_SECOND 毫秒
• SECOND  秒
• MINUTE  分鐘
• HOUR  小時
• DAY  天
• WEEK  星期
• MONTH  月
• QUARTER  季度
• YEAR  年

 

樣本語句：

SELECT TIMESTAMPDIFF(SECOND, ‘2012-12-21‘, CURRENT_TIMESTAMP()) AS `Second`;

SELECT * from autotesting_mobiledevice where TIMESTAMPDIFF(SECOND, MDLastSynchTime, NOW())<6000;

 

注意事項：

秒以上單位的時間差計算都是以秒為基礎的。

天數的時間差是以24*60*60（86400）為依據的。假如相差間隔小於86400秒，則為0，如果>=86400秒且<86400*2秒，則為1。

如果單位為分鐘，那麼計算間隔分鐘是以60秒為依據的。假如相差間隔小於60秒，則為0，如果>=60秒且<60*2秒，則為1。

select timestampdiff(day,"2012-08-22 15:15:15","2012-08-23 15:15:16");
1

select timestampdiff(day,"2012-08-22 15:15:17","2012-08-23 15:15:16");
0

 

 

三、時間轉換

time_to_sec(datetime)

返回總秒數

 

sec_to_time(seconds)

 

to_days(date)

from_days(days)

 

makeDate(year,dayofyear)

makeTime(hour,minute,second)

 

str_to_date(str,format)

select str_to_date(‘08.09.2008 08:09:30‘, ‘%m.%d.%Y %h:%i:%s‘); -- 2008-08-09 08:09:3

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), 這裡星期一是星期的第一天  
　　%% 一個文字“%”。  
　　所有的其他字元不做解釋被複製到結果中。

 

date_format(date,format)

time_format(time,format)

 

get_format(date|time|datetime, ‘eur|usa|jis|iso|internal‘)

擷取國家地區的時間格式

 

unix_timestamp()

返回自1970-1-1 0:00:00 UTC（因為中國是東八區，所以是8:00:00.）開始到當前系統時間為止的秒數

unix_timestamp(datetime)

早於1970-1-1 0:00:00 UTC的時間，返回0

from_unixTime(unix_timestamp)

字串環境輸出格式為：YYYY-MM-DD HH:MM:SS，數字環境輸出格式為：YYYYMMDDHHMMSS

 

from_unixTime(unix_timestamp, format)

 

 

convert_tz(datetime, from_tz, to_tz)

時區轉換，也可以通過date_add(),date_sub(),timestampadd()等函數實現

select convert_tz(‘2008-08-08 12:00:00‘, ‘+08:00‘, ‘+00:00‘); -- 2008-08-08 04:00:00

Mysql時間函數