Subtraction of MySQL times inconsistent on different machines.

Question

I have a MySQL query structured as follows:

SELECT time(c.start_time),
       time(c.end_time),
       time(c.end_time) - time(c.start_time) as 'opening_hours' 
FROM my_shop c;

The data in start and end time is 1970-01-01 07:00:00 and 1970-01-01 19:00:00 respectively.

On my local machine this this query returns:

| 07:00:00 | 19:00:00 | 12 |

However on a remote machine (production) it is returning

| 07:00:00 | 19:00:00 | 120000 |

Any ideas as to why this might be happening and how to fix it?

Both sets of data are identical and too the best of my knowledge both MySQL installations are identical.

Any help is much appreciated.

Update:

It would seem that the versions of MySQL are slightly different: 5.0.27-community-nt versus 5.0.51b-community-nt. This is most probably the reason why.

djt has raised a good point in that Bill's solution does not take into account minutes. As well as this djt's solution is not quite what i need.

So i guess the question has morphed into how to subtract two times including minutes such that:

1970-01-01 19:00:00 - 1970-01-01 07:00:00 = 12 
1970-01-01 19:00:00 - 1970-01-01 07:30:00 = 11.5
1970-01-01 19:00:00 - 1970-01-01 07:45:00 = 11.25

etc.

Answer 1

I'd do it this way:

SELECT TIME(c.start_time),
       TIME(c.end_time),
       EXTRACT(HOUR FROM TIMEDIFF(TIME(c.end_time), TIME(c.start_time)))
         AS 'opening_hours' 
FROM my_shop c;

Answer 2

Usually the same methods causing different output gives the impression that it is either a different server configuration or version. I'm not really able to help with that but it may be worth trying the below method in your query instead:

SELECT TIME(c.start_time),
       TIME(c.end_time),
       TIMEDIFF(TIME(c.end_time), TIME(c.start_time)) as 'opening_hours'

FROM my_shop c;

This will give you a formatted timestamp of the opening hours, minutes and seconds.

Answer 3

Is there a better solution than this:

SELECT EXTRACT(HOUR FROM TIMEDIFF(TIME(c.end_time), TIME(c.start_time))) 
+ ((EXTRACT(MINUTE FROM TIMEDIFF(TIME(c.end_time), TIME(c.start_time))))/60)
FROM my_shop c;