mysql时间函数之hour,minute,second用法_前进的火车_新浪博客

语法

HOUR(time)

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

返回时间的小时。 对于一天时间值，返回值的范围是0到23。 但是，TIME值的范围实际上要大得多，所以HOUR可以返回大于23的值。

MINUTE(time)

Returns the minute for time, in the range 0 to 59.

返回时间分钟，范围为0到59。

SECOND(time)

Returns the second for time, in the range 0 to 59.

返回时间秒数，范围为0到59。

SELECT hour('12:13:14');                  # 12

SELECT hour('122:13:14');                 # 122

SELECT hour('12-13-14');                  # 0

SELECT hour('2008-09-10 12:13:14');       # 12

SELECT hour('2008-09-10 122:13:14');      # null

SELECT hour('2008-09-10 12-13-14');       # 12

SELECT minute('12:13:14');                # 13

SELECT minute('12:60:14');                # null

SELECT minute('12-13-14');                # 0

SELECT minute('2008-09-10 12:13:14');     # 13

SELECT minute('2008-09-10 122:13:14');    # null

SELECT minute('2008-09-10 12-13-14');     # 13

SELECT second('12:13:14');                # 14

SELECT second('12:13:60');                # null

SELECT second('12-13-14');                # 12

SELECT second('2008-09-10 12:13:14');     # 14

SELECT second('2008-09-10 122:13:14');    # null

SELECT second('2008-09-10 12-13-14');     # 14

sql语句中的应用：

if(timestampdiff(MINUTE,modify_time,now())>720

       if uid then

  set mm=timestampdiff(day,ftm,curdate());

  if mm>0 then

    insert into user_count(userId,count,time) values(uid,dct,CURRENT_TIMESTAMP);

    update A set dayCount=v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;

  else

    update A set dayCount=dayCount+v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;

  end if;

end if;