Calculating time elapsed btwn two datetime fields

[w4lru5]

I'm trying to calculate the time elapsed (in HH:MM) between two DATETIME columns. I'm storing the result in another column with DATATYPE = TIME:

UPDATE table SET time_elapsed = later_datetime - earlier_datetime;

This is not working when the individual components of the DATETIME field in later_datetime (i.e. seconds, minutes, etc.) are lower than those in the earlier_datetime, as in the following example:

later_datetime = 2003-02-04 12:39:20
earlier_datetime = 2003-02-04 12:04:44
time_elapsed = 00:00:00

I think this is happening because MySQL deems the operation ilegal because the individual seconds in later_datetime (20) is less than the individual seconds in earlier_datetime (44).

Any suggestions as to how can I correctly calculate the time elapsed (in HH:MM) between two datetime fields?

[nicc777]

Hi

Here is a possible solution:

* First, create the test table with the test data:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `tnow` datetime NOT NULL default '0000-00-00 00:00:00',
  `tlater` datetime default NULL,
  `timediff` int(10) unsigned default '0'
) TYPE=MyISAM;

INSERT INTO `test` (`tnow`, `tlater`, `timediff`) VALUES ('2003-07-24 05:57:52', '2003-07-24 05:59:52', NULL);
INSERT INTO `test` (`tnow`, `tlater`, `timediff`) VALUES ('2003-07-24 05:59:15', '2003-07-24 05:58:15', NULL);
INSERT INTO `test` (`tnow`, `tlater`, `timediff`) VALUES ('2003-07-24 05:59:00', NULL, NULL);
INSERT INTO `test` (`tnow`, `tlater`, `timediff`) VALUES ('2003-07-24 06:00:21', '2003-07-24 06:00:21', NULL);
INSERT INTO `test` (`tnow`, `tlater`, `timediff`) VALUES ('2003-07-24 06:01:04', '2003-07-24 06:01:06', 2);

* Next, test:

mysql> SELECT * FROM test;
+---------------------+---------------------+----------+
| tnow                | tlater              | timediff |
+---------------------+---------------------+----------+
| 2003-07-24 05:57:52 | 2003-07-24 05:59:52 |     NULL |
| 2003-07-24 05:59:15 | 2003-07-24 05:58:15 |     NULL |
| 2003-07-24 05:59:00 | NULL                |     NULL |
| 2003-07-24 06:00:21 | 2003-07-24 06:00:21 |     NULL |
| 2003-07-24 06:01:04 | 2003-07-24 06:01:06 |        2 |
+---------------------+---------------------+----------+
5 rows in set (0.00 sec)

* Now, populate the `timediff` field. First, let's see which rows qualify for an UPDATE:

mysql> SELECT UNIX_TIMESTAMP( tlater ) -  UNIX_TIMESTAMP( tnow ) AS timediff FROM test WHERE timediff IS NULL AND ( tnow < tlater OR tnow = tlater );
+----------+
| timediff |
+----------+
|      120 |
|   262800 |
+----------+
2 rows in set (0.00 sec)

There should not be to much confusion with the logic. Only row 1 and row 4 qualify in this example. Now for the actual UPDATE command:

mysql> UPDATE test SET timediff = ( UNIX_TIMESTAMP( tlater ) -  UNIX_TIMESTAMP( tnow ) ) WHERE timediff IS NULL AND ( tnow < tlater OR tnow = tlater );
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

* Finally, verify the data was updated correctly:

mysql> SELECT * FROM test;
+---------------------+---------------------+----------+
| tnow                | tlater              | timediff |
+---------------------+---------------------+----------+
| 2003-07-24 05:57:52 | 2003-07-24 05:59:52 |      120 |
| 2003-07-24 05:59:15 | 2003-07-24 05:58:15 |     NULL |
| 2003-07-24 05:59:00 | NULL                |     NULL |
| 2003-07-24 06:00:21 | 2003-07-27 07:00:21 |   262800 |
| 2003-07-24 06:01:04 | 2003-07-24 06:01:06 |        2 |
+---------------------+---------------------+----------+
5 rows in set (0.00 sec)

Notes : The timediff column is of type INT, and contains the number of SECONDS `tnow` and `tlater` differs. To query this field, you can use the following:

mysql> SELECT  timediff FROM test WHERE timediff IS NOT NULL;
+----------+
| timediff |
+----------+
|      120 |
|   262800 |
|        2 |
+----------+
3 rows in set (0.00 sec)

The `timediff` is of course the time difference in seconds.

References :

• Date and Time Functions
• Mathematical Functions

Cheers

[w4lru5]

thanks...I used your idea and added an extra step to store the information in a time format:

UPDATE test SET timediff = sec_to_time( ( UNIX_TIMESTAMP( tlater ) - UNIX_TIMESTAMP( tnow ) ) )
WHERE timediff IS NULL AND ( tnow < tlater OR tnow = tlater );

thanks again.