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

Databases
1

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 :slight_smile:
Just what we are trying to achieve.

Regards,

2

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

3

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

4

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

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/en/date-and-time-functions.html#function_date-add

5

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.

6

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!

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

look ma, no DATE_ADD function :smiley:

8

you did not compare the result of the function to anything

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

9

Nice.

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;
10

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

11

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,

12

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)