Menu
I have this table but I am helpless how to calculate seconds for each separate day from this table:
id from to --------------------------------------------------- 1 2013-01-31 23:50:00 2013-02-02 09:00:00 2 2013-02-05 11:21:12 2013-02-08 01:01:01 3 2013-02-08 17:33:44 2013-02-08 18:22:55 4 2013-02-12 01:40:12 2013-02-12 02:00:59 5 2013-02-28 01:40:12 2013-03-02 02:00:59 Now I need to gain number of seconds for each day in february between from and to. so for 1st Feb - x seconds, 2nd Feb - y seconds, 3rd Feb - 0 seconds, etc. Any idea how should I do it? Many thanks.
359
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year . Here, you'd like to get the difference in seconds, so choose second.
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR .
The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
MySQL TIMESTAMPDIFF() function A date value is treated as a datetime with a default time part '00:00:00'. The unit for the result is given by another argument. The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. A datetime expression.
Use TIMESTAMPDIFF in MySQL:
SELECT TIMESTAMPDIFF(SECOND,from,to); Example:
SELECT TIMESTAMPDIFF(SECOND,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 864000 SELECT TIMESTAMPDIFF(MINUTE,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 14400 SELECT TIMESTAMPDIFF(HOUR,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 240 SELECT TIMESTAMPDIFF(DAY,'2016-01-01 00:00:00','2016-01-11 00:00:00'); -- 10 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
