Jump to content

Time difference


Recommended Posts

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

 

Link to post
Share on other sites

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 |
+--------+---------------------+-------------+------+

 

Link to post
Share on other sites

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

Link to post
Share on other sites

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 |
+--------+---------------------+-------------+------+

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.