-4

I want the query to calculate the difference between two date time fields

  • to calculate total time taken

  • two date time fields are in same table as start_time and stop_time

  • And I want to calculate total start duration and stop duration
msdq
  • 143
  • 2
  • 9
  • You need to work on your question, we can't just do the job for you! – David Aleu Aug 30 '12 at 10:41
  • 1
    http://www.google.com will answer it for you. Or searching stack overflow? http://stackoverflow.com/questions/4759248/difference-between-two-dates-in-mysql http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns http://sqlandme.com/2011/05/22/how-to-get-difference-between-two-dates-tsql/ Show what you have tried so far, IN CODE and we can help out. Otherwise you can pay a programmer to do your work for you. – RossC Aug 30 '12 at 10:45

3 Answers3

8

You can use MySQL's UNIX_TIMESTAMP() function to convert your datetime expressions to seconds since the UNIX epoch, then taking the sum of all differences will yield the total duration in seconds:

SELECT SUM(UNIX_TIMESTAMP(stop_time) - UNIX_TIMESTAMP(start_time)) FROM my_table

See it on sqlfiddle.

Note that UNIX_TIMESTAMP() is limited to the range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

eggyal
  • 122,705
  • 18
  • 212
  • 237
6

HINT:

SELECT TIMESTAMPDIFF(SECOND,'2012-12-30 12:01:01','2012-12-31 10:02:00'); 
-- result: 79259  the difference in seconds with the time.

See this link for more details on DateTime functions.

Jainendra
  • 24,713
  • 30
  • 122
  • 169
3

Try this

SELECT TIMEDIFF(STOP_TIME - START_TIME) AS INTERVAL
FROM ......
Himanshu
  • 31,810
  • 31
  • 111
  • 133
Steve
  • 245
  • 1
  • 6