Another date compare question

You looked at this post ?

http://www.interactivetools.com/iforum/gforum.cgi?post=67448;search_string=day%20where;guest=59781951&t=search_engine#67448

Djulia

Thanks Djulia,

I just had a look and tried to implement the code, but no matter what I try all I get is a blank page

I'm afraid with my limited knowledge I'm still really lost.

Jerry

Hi Jerry,

Can you post your getRecords() code? Thanks!

Hi Jerry,

Date math is often kind of tricky. So this is an advanced topic.

When you have time, I'd recommend installing the free "Komodo Edit" software and getting it configured.
http://www.activestate.com/komodo_edit/

It has a helpful feature where it puts a red line under any line that has coding errors. Saves us a ton of time even if you just use it once and a while to detect code errors. Here's the problem with the code (missing ; after 7):

<?php
$days = (int) @$_REQUEST['days'];
if (!$days) { $days = 7; } // set default
$escapedDays = mysql_real_escape_string($days);
?>

For date queries, the first step is to figure out if you need to do it in MySQL or PHP. If you are able to do it in MySQL it's often simpler.

You can find a list of MySQL date/time functions here:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

And here's some examples:
NOW() - Gives you the current date and time: 2010-03-01 09:41:50
(NOW() - INTERVAL 7 DAY) - Get date/time 7 days ago: 2010-02-22 09:45:25
CURDATE() - Gives you the current date only: 2010-03-01
TIMESTAMP() - Format value as date/time, or if two values, add them together
TIMESTAMP(CURDATE()) - Get date/time at beginning of day: 2010-03-01 00:00:00
TIMESTAMP(CURDATE(), '23:59:59') - Get date/time at end of day 2010-03-01 23:59:59

And you can test all these with the MySQL Console Plugin by entering SELECT followed by the function. So:
SELECT NOW() in the Mysql Console returns: 2010-03-01 09:41:50

So the first step is to figure out the values you want to compare. My guess is you want:
The date 7 days ago: (NOW() - INTERVAL 7 DAY)
The reception date: reception_date
The date at the end of today: TIMESTAMP(CURDATE(), '23:59:59')

If you write it out in English first it's way easier:
- If the reception_date is 7 days or less from now
- AND the reception_date hasn't already passed

I like to arrange my code so if reads like a time range with the test date in the middle like this:
start_date <= test_date AND test_date <= end_date

So that would be:
(NOW() - INTERVAL 7 DAY) <= reception_date AND reception_date <= TIMESTAMP(CURDATE(), '23:59:59')

Let me know if using that as your where works for you. It may be easier to just use another getRecords() query for those Upcoming Reminders rather than re-doing everything in PHP.

Hope that helps! Let me know any questions.

Wow.

Thanks Dave,

I'll start playing around with it either later today or tomorrow and I'm sure that I'll have some questions as I get into it.

The code in post 67448 was yours, so I trust that you added the semicolon there too.

Best,

Jerry

Oh, thanks! That's what happen when I write code in the forum and not in my editor! :)

I've updated the original post!

Well Dave, you've done it again.

So far almost everything seems to work.

I needed to change the - to a + to get the logic to work correctly and change the single quotes to double quotes around the 23:59:59 or I got a blank page.

When I tested (at about 1:40pm local time) I found that the reception_date still does not show at all if it is equal to today's date. My local and MySQL times are both correct.

I won't be able to play with this any more today, but if you've got any ideas I'd like to hear them.

Here's what I used:

'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "23:59:59")',

The body code I used for a test was; <?php foreach ($date_compare_testRecords as $record): ?>
The Reception Date is <?php echo date("D, M jS, Y g:i a", strtotime($record['reception_date'])) ?>
<?php endforeach ?>



Thanks again for all your help,

Jerry

Hi Jerry,

Heh, date math always makes my head hurt. Try this:
'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "00:00:00")',

Which I find easier to read if written like this:
'where' => 'TIMESTAMP(CURDATE(), "00:00:00") <= reception_date AND reception_date <= (NOW() + INTERVAL 7 DAY)',

Or in english (add this as a comment for the code above)
// If the reception date falls between 00:00 today -and- 7 days from now.

I think that's right. Can you give it a try and let me know if that works for you.