Mysql时间函数

时间:2024-05-22 10:07:02

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

mysql中函数和关键字不区分大小写。下文函数的datetime参数处既可以用时间字符串也可以用时间变量或表达式。

一、获取当前时间

下面几个方法等效,字符串环境输出格式为:YYYY-MM-DD HH:MM:SS,数字环境输出格式为:YYYYMMDDHHMMSS

now()

current_timestamp()   / current_timestamp

localtime() /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