difference of time MYSQL PHP

Question

How can I get an update another field (whole column) the difference of time in (TIME 00:00:00) format in my MYSQL phpMyAdmin database

<?php
  $con=mysqli_connect("***","****","****","******");
  // Check connection
  if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  mysqli_query($con,"UPDATE members SET time_on = current_time-logon_time");
?>

I keep getting 0:00:00 and no results when I do this in PHP. The other times our fine, just trying to get the difference and update all fields in the time_on column.

You can find the answer of you question here, it is solved in php http://stackoverflow.com/questions/13928021/getting-time-difference-between-two-times-in-php — Bira, Sep 11 '14 at 01:58

score 0 · Accepted Answer · edited Jan 06 '15 at 21:30

0

You should use timediff()

mysqli_query($con,"UPDATE members SET time_on = timediff(current_time,logon_time)");

edited Jan 06 '15 at 21:30

Funk Forty Niner

74,450
15
68
141

answered Sep 11 '14 at 01:59

dieuvn3b

5,826
3
17
29

score 0 · Answer 2 · answered Sep 11 '14 at 02:04

0

You can use TIMESTAMPDIFF() to get the difference between two dates and/or datetimes. Try this:

UPDATE members
SET time_on = TIMESTAMPDIFF(SECOND, logon_time, current_time)

This will set the difference in seconds.

answered Sep 11 '14 at 02:04

Mark Miller

7,442
2
16
22

score 0 · Answer 3 · answered Jan 06 '15 at 21:40

Just to expand on why the other answers are right: You're doing MATH on times, which doesn't work as you expect:

mysql> create table foo (x time, y time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo (x, y) values ('14:15:16', '09:08:07');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+----------+----------+
| x        | y        |
+----------+----------+
| 14:15:16 | 09:08:07 |
+----------+----------+
1 row in set (0.00 sec)

mysql> select x - y from foo;
+-------+
| x - y |
+-------+
| 50709 |
+-------+
1 row in set (0.00 sec)

Note how the result is NOT a time value, it's an integer. It happens to be a direct time->string->numeric conversion of 05:07:09, which is the literal as-expected time difference of the two values. But since you're doing MATH, i.e. subtraction, MySQL is assuming you want a numeric answer, and giving it to you.

That's why there's timediff() and similar date/time functions, which return a real date/datetime value, not this wonky/useless integer.

difference of time MYSQL PHP

3 Answers3