Bug #882 Subtraction of DATETIME's doesn't work across midnight boundary
Submitted: 21 Jul 2003 12:11 Modified: 21 Jul 2003 12:33
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:4.0.13-0 OS:Linux (Red Hat Linux 9)
Assigned to: CPU Architecture:Any

[21 Jul 2003 12:11] [ name withheld ] 
Description:
mysql> select start_time, stop_time, stop_time - start_time from party where ID = 731;
+---------------------+---------------------+------------------------+
| start_time          | stop_time           | stop_time - start_time |
+---------------------+---------------------+------------------------+
| 2003-07-19 21:46:18 | 2003-07-20 00:21:31 |                 787513 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

The actual result should be:

2003-07-20 00:21:31
2003-07-19 21:46:18
-------------------
           02:35:13 (or 9313 seconds)

We are billing our customers based on the difference of the above two fields so it is imperative that we have a solution for this.

How to repeat:
create database datetime;
use datetime;
insert into dt (start_time, stop_time) values ("2003-07-19 21:46:18", "2003-07-20 00:21:31");
select start_time, stop_time, stop_time - start_time from dt;
[21 Jul 2003 12:33] Indrek Siitan 
MySQL does not support that kind of arithmetics on date fields 
directly. If you try mathematical functions, MySQL does the best 
to interpret the given values as numbers. In this case, what it 
does, is 20030720002131-20030719214618, which comes out 
to 787513.

To yield the result you need, you can do:
select start_time, stop_time, unix_timestamp(stop_time) - unix_timestamp(start_time) from party;
[21 Jul 2003 12:52] [ name withheld ] 
Thanks.  That works.