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
September 4, 2012, 5:36pm
2
r937
September 4, 2012, 6:22pm
3
WorldNews:
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
September 4, 2012, 6:26pm
4
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/en/date-and-time-functions.html#function_date-add
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.
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
September 4, 2012, 6:46pm
7
SELECT id
FROM xyz
WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR
look ma, no DATE_ADD function
r937
September 4, 2012, 6:47pm
8
you did not compare the result of the function to anything
so as long as it’s not zero, it evaluates as TRUE
cpradio
September 4, 2012, 6:50pm
9
r937:
SELECT id
FROM xyz
WHERE date_added < CURRENT_TIMESTAMP - INTERVAL 6 HOUR
look ma, no DATE_ADD function
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;
r937
September 4, 2012, 6:59pm
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
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
September 4, 2012, 9:44pm
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)