Time difference

[se001]

I've been stuck on this issue for a few days. The answer is probably basic, but I can't figure it out. I have a variable originating from a mysql database $action_time. Contents are like $action_time="16:45:00".

I need to get the time difference in minutes from the current time. I can obviously do without the seconds.

I believe to get the current date, it is date("h:i:s")

Thanks

 

[requinix]

MySQL can do this more easily than PHP with its TIME_TO_SEC function.

[Barand]

If you use MySql's timestampdiff() function you can go directly to minutes.

DATA:
+----+--------+-------------+         +-------------+-------------+------+-----+---------+----------------+
| id | name   | action_time |         | Field       | Type        | Null | Key | Default | Extra          |
+----+--------+-------------+         +-------------+-------------+------+-----+---------+----------------+
|  1 | John   | 15:30:00    |         | id          | int(11)     | NO   | PRI | NULL    | auto_increment |
|  2 | Paul   | 18:00:00    |         | name        | varchar(45) | YES  |     | NULL    |                |
|  3 | George | 12:00:00    |         | action_time | time        | YES  |     | NULL    |                |
|  4 | Ringo  | 10:00:00    |         +-------------+-------------+------+-----+---------+----------------+
+----+--------+-------------+

SELECT name
     , NOW() as Now
     , action_time
     , timestampdiff(MINUTE, now(), action_time) as mins
FROM a_test_table;

RESULTS:
+--------+---------------------+-------------+------+
| name   | Now                 | action_time | mins |
+--------+---------------------+-------------+------+
| John   | 2020-12-06 11:10:15 | 15:30:00    |  259 |
| Paul   | 2020-12-06 11:10:15 | 18:00:00    |  409 |
| George | 2020-12-06 11:10:15 | 12:00:00    |   49 |
| Ringo  | 2020-12-06 11:10:15 | 10:00:00    |  -70 |
+--------+---------------------+-------------+------+

 

[se001]

Thanks for the suggestions. I get null when I use the timestampdiff function. I changed my data type from char(8) to time to match your structure. Here is my actual query.

SELECT action
     , NOW() as Now
     , action_time
     , timestampdiff(MINUTE, now(), action_time) as mins
FROM schedules;

Result

action    Now                                     action_time    mins

1            2020-12-07  21:09:14         07:40:00        NULL

0            2020-12-07  21:09:14        16:45:00         NULL

[Barand]

The manual says the fields should be type date or datetime, which I is why I tested it with a time field before posting.

Maybe your version doesn't support time types (Mine is MySQL 5.7)

Alternative...

SELECT name
     , NOW() as Now
     , action_time
     , round(time_to_sec(timediff(action_time, time(now())))/60, 0) as mins
FROM a_test_table;

+--------+---------------------+-------------+------+
| name   | Now                 | action_time | mins |
+--------+---------------------+-------------+------+
| John   | 2020-12-08 09:51:28 | 15:30:00    |  339 |
| Paul   | 2020-12-08 09:51:28 | 18:00:00    |  489 |
| George | 2020-12-08 09:51:28 | 12:00:00    |  129 |
| Ringo  | 2020-12-08 09:51:28 | 10:00:00    |    9 |
+--------+---------------------+-------------+------+

 

[se001]

Thanks for your help. The alternative worked.

I forgot to mention that I am using mysql on a raspberry pi which to my understanding has some limitations.