[Resolved] MySQL Queries and Date Math

ywickham

Hi,

I am trying to pull all records from a database where the join date is exactly 3 days past today's date. New records will be added to this database on a daily basis, so I have to make sure not to pull any of the newer records in the process. The code that I have is as follows:

//Get today's zero hour timestamp
$senddate  = mktime (0,0,0,date("m"),date("d")-1,date("Y"));

$query = "SELECT userid, joindate, email
	 FROM user
	 WHERE DATE_ADD(joindate, INTERVAL 3 DAY) = '$senddate';";

('joindate' is a date stored as a timestamp in the database.) I know that my code above will not work, because the joindate column in the database also contains the time information from when the record was initially entered. I have no way of controlling this.

Does anyone have any ideas on how I can find a solution to my problem? (And yes, I have looked at MySQL Manual 6.3.4 Date and Time Functions Chapter, but I think I am just not putting things together correctly...) 🙁

Please help a frustrated soul!
Thanks!

ultraslacker

SELECT userid, joindate, email
FROM user
WHERE TO_DAYS(now()) - TO_DAYS(joindate) = 3

ywickham

Thank you, but I have already tried that with no success. I have four different records with consecutive join dates going back 4 days and that query produces an empty set for me.

Any other ideas? Anyone?

ultraslacker

The to_days function will work, if you are looking for joindates having x number of days previous to current date. Find the difference in days that you require, if not 3 then 4.

buck2bcr

I may be a little thick today, but I'm a little confused. You want a date exactly 3 days in the future from todays date?

Originally posted by ywickham
I am trying to pull all records from a database where the join date is exactly 3 days past today's date.

However your $senddate variable is made to be one day ago at midnight.

//Get today's zero hour timestamp
$senddate  = mktime (0,0,0,date("m"),date("d")-1,date("Y"));

If you want a zero timestamp for the current date just take out your -1 and you have a stamp for 00:00:00 m/d/Y. So if today was 8/20/2003 your code would give you a stamp for 00:00:00 8/19/2003.

If you could clear this up for me a bit I might get a better understanding on what you are wanting to do and how I may be able to help you.

Buck

ywickham

What I needed was to find those records with dates that were 3 days before the current date. The TO_DAYS functions were not working for me, as I did modify the query various times to try and pull those records with dates 1, 2, or 3 days from today's date.

I was able to resolve the problem by simplying doing a search between a range of dates as in the query below:

$query = "SELECT userid, joindate, email
FROM user
WHERE joindate <= $end_date AND joindate >= $start_date;";

That's what worked for me.

Thank you everyone for your help.