How do I get the time difference from a MySQL datetime field

[WorldNews]

Hello,

How does one get the time difference in Hours from a MySQL date+time value which is kept in a datetime field?

To be exact, we have a field called:
date_added which is of datetime type

What we want to be able to do is:

SELECT id FROM xyz WHERE (Time_now is 6 Hours later than date_added) AND email_sent = 'no'

Of course "Time_now is 6 Hours later than date_added" is not MySQL
Just what we are trying to achieve.


Regards,

[cpradio]

You will want to use DATE_ADD()
http://www.w3schools.com/sql/func_date_add.asp

[r937]

To be exact...

how convenient that you should use that word, "exact"

surely you want to return rows where date_added is more than 6 hours ago... not exactly 6 hours 0 minutes and 0 seconds ago

right?

p.s. cpradio, i prefer to avoid that particular web site... see http://w3fools.com

[cpradio]

p.s. cpradio, i prefer to avoid that particular web site... see http://w3fools.com

It was just the first result from google that showed how to properly use DATE_ADD, so I stopped searching

To help rectify my miss-guidance, here is the link to mysql's webpage describing date_add
http://dev.mysql.com/doc/refman/5.5/...ction_date-add

[WorldNews]

Just more than 6 Hours ago and not exactly 6 hours 0 minutes and 0 seconds ago.
But just X hours have passed since the entry of that record.

[WorldNews]

This does not work AT ALL.

To be exact since I am interested in Hours and not DAYS which is what that link samples,

I then issued:

SELECT * FROM qal_history
WHERE DATE_ADD(date_added, INTERVAL 6 HOUR)
ORDER BY id DESC LIMIT 10;

And in returns entries that were added 1 minute ago!

SELECT * FROM qal_history
WHERE DATE_ADD(date_added, INTERVAL 1 DAY)
ORDER BY id DESC LIMIT 10;


Still brings back results from 1 minute ago!

[r937]

SELECT id 
  FROM xyz 
 WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR

look ma, no DATE_ADD function

[r937]

Still brings back results from 1 minute ago!

you did not compare the result of the function to anything

so as long as it's not zero, it evaluates as TRUE

[cpradio]

SELECT id 
  FROM xyz 
 WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR

look ma, no DATE_ADD function

Nice.

SELECT * FROM qal_history
WHERE DATE_ADD(date_added, INTERVAL 6 HOUR)
ORDER BY id DESC LIMIT 10;

I would think this is because you are not comparing it to anything... whereas, I would think the below would have worked (although I do like r937's solution better)

SELECT * FROM qal_history
WHERE CURRENT_TIMESTAMP >= DATE_ADD(date_added, INTERVAL 6 HOUR)
ORDER BY id DESC LIMIT 10;

[r937]

remember that when you apply a function to a column, mysql cannot use an index on that column

so instead of this --

WHERE CURRENT_TIMESTAMP >= DATE_ADD(date_added, INTERVAL 6 HOUR)

you should do this --

WHERE date_added < DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -6 HOUR)

or preferably the interval arithmetic (non-function) version for increased clarity

[WorldNews]

Hi,

Your suggestion works just fine as far as generating the list. ThanX.

But how do we display the time added Difference in Hours & Days?

So what I getting at is when we get the list of Queries which are at least 6 Hours old, they can be some 2 week ago posted, 3 days ago posted, 12 Hours ago posted, etc.

How do we get then from the date_added values of these queries the following info:

This record is:
1 week, 3 Days, 2 Hours old
or
1 Day, 12 Hours old,

etc.

ThanX,

[r937]

you might try the TIMEDIFF function, although i'm not sure it will express a value that's several weeks old -- test it and see

alternatively, you can use this --

SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date_added) AS secs_diff ...

and then translate the seconds difference into those lovely words like "1 week, 3 Days, 2 Hours" using your front-end language (php or whatever)