Databases
StackOverflow2013
Postsdbo
POHelp with writing a MySQL query involving date differences
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with writing a MySQL query involving date differences
    text
    copied!<p>I need to delete rows where a datetime field is over 2 weeks old.</p> <p>This is what I have came up with</p> <pre><code>$duration = Date::WEEK * 2; // int(1209600) $query = 'DELETE FROM properties WHERE TIMEDIFF(' . date(DATE_ISO8601) . ', reserved_datetime) &gt; ' . $duration; </code></pre> <p>I don't often write complicated queries (preferring to do stuff in PHP, where I'm more comfortable) but I'd like to know more about them, plus doing this sort of thing in PHP would be very inefficient and I am handling a large amount of rows.</p> <p>Anyone know what I'm doing wrong? Cheers.</p> <h2>Update</h2> <p>I gave <a href="https://stackoverflow.com/questions/2353438/help-with-writing-a-mysql-query-involving-date-differences/2353479#2353479">Wallyk's answer</a> a shot, changing it slightly in phpMyAdmin to SELECT just so I could see what was going on.</p> <p>This is what I used</p> <pre><code>SELECT * FROM properties WHERE date_sub( `reserved_datetime` , INTERVAL 2 week ) &gt;0 LIMIT 0 , 30 </code></pre> <p>The only problem however, is that it has returned rows where the <code>reserved_datetime</code> is <code>2010-02-28 10:45:59</code>, definitely less than 2 weeks ago (from now).</p> <p>I thought of checking MySQL's internal date. I have been using <code>date(DATE_ISO8601)</code> in my queries, because MySQL's <code>NOW()</code> wasn't exactly right (it just returned if interested <code>2010-02-28 20:09:19</code>).</p> <p>Is there a way to specify the current date in that query? Any other suggestions?</p> <p>Many thanks</p> <h2>Another Update</h2> <p>Here is a screenshot from phpMyAdmin that may demonstrate anything better than my words can. Oh, and the reason it has returned 3 only is because all the others have blank values, i.e. <code>0000-00-00 00:00:00</code> </p> <p><a href="http://alexanderdickson.com/hosted/stackoverflow.com/datetime-query.png" rel="nofollow noreferrer">query http://alexanderdickson.com/hosted/stackoverflow.com/datetime-query.png</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload