MySQL time calculate - Super Method

Most Usefull:

TIMESTAMPDIFF(hour,'2008-08-08 00:01:00','2008-08-09 00:00:00')

TIMESTAMPDIFF(minute,'2008-08-08 00:01:00','2008-08-09 00:00:00')

TIMESTAMPDIFF(second,'2008-08-08 00:01:00','2008-08-09 00:00:00')

Result:

23.98333333

1439.0000

86340

Less Usefull:

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60/60

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))

Result:

The same

------------------------------------------------------------------------------------

※Timediff output: HH:MM:SS

※Time_to_sec Format: time_to_sec(HH:MM:SS)

------------------------------------------------------------------------------------

Ref. http://wen198599.pixnet.net/blog/post/22450019-mysql%E6%97%A5%E6%9C%9F%E5%92%8C%E6%99%82%E9%96%93%E5%87%BD%E6%95%B8%E4%B8%8D%E6%B1%82%E4%BA%BA

Ref. http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

Ref. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-to-sec

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');

        -> 128885

 TIME_TO_SEC(time)

Returns the time argument, converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

 -------------------------------------------------------------------------

select
timediff('2008-08-09 00:00:00','2008-08-08 00:01:00') timediff,
hour(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_hr,
minute(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_minute,
second(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_second,
time_to_sec('2008-08-08 00:01:00') To_Sec,
time_to_sec('2008-08-09 00:00:00') To_Sec,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) Tdiff_To_Sec,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60 Tdiff_To_Sec_min,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60/60 Tdiff_To_Sec_hr,
TIMESTAMPDIFF(minute,'2008-08-08 00:01:00','2008-08-09 00:00:00') stampdiff_min,
TIMESTAMPDIFF(second,'2008-08-08 00:01:00','2008-08-09 00:00:00') stampdiff_sec

timediff	timediff_hr	timediff_minute	timediff_second	To_Sec	To_Sec	Tdiff_To_Sec	Tdiff_To_Sec_min	Tdiff_To_Sec_hr	stampdiff_min	stampdiff_sec
23:59:00	23	59	0	60	0	86340	1439.0000	23.98333333	1439	86340