[Resolved] Help with time comparison DB query...

webdes03

I'm looking for what you think the best way for doing the below query is...

I need to find if a record has been posted within 1 month by the user, with specific criteria.

For example:
Does the database contain a record that was added within the last month where Validated = Yes.

I can't seem to get my head around the best way to find within one month... I do have a timestamp(14) field in my database... I can't figure out the best way to compare the dates (especially since different months have different days).

Any help is appreciated. Thanks.

zhabala

if it is mysql DB you can check this page

http://www.mysql.com/doc/en/Date_and_time_functions.html

weekender

ok there are two ways of doing this...

1 - use MONTH() and YEAR() sql functions

$sql = "SELECT * FROM tablename ".
    "WHERE MONTH(dateposted) = $month ".
    "AND YEAR(dateposted) = $year ".
    "AND validated = 1";

2 - You'll notice that a mysql timestamp is a concatenation of the current date - ie 20040108165503 is a concatenation of year + month + day + hour + minutes + seconds

build a date range in php and then use it in the select.

$year = "2004";
$month = "01";

$mindate = $year . $month . "00000000";
$maxdate = $year . $month . "31235959";

$sql = "SELECT * FROM tablename ".
    "WHERE dateposted >= $mindate ".
    "AND dateposted <= $maxdate ".
    "AND validated = 1";

hope that helps

adam

webdes03

I'm still having some trouble with this, so I'll post some more info... maybe it'll help...

I'm making a subscription based content system, where a user's credit card is automatically charged every month. When the credit card is charged, it adds a bunch of information to my database, one of which is the timestamp of the transaction...

The users are billed one month from their signup date, so I guess I should have said that I want to see if a user has paid in the last 30 days or so...

What I'm having a hard time doing, is figuring out how to figure in days of the month, since not all months are the same...

I understand both of your solutions, but I can't think how I'd apply it to what I'm trying to do... maybe I'm just being dumb today, I don't know.

I usually don't have this problem when coding, but I'm stuck, and don't know what to do.

Thanks...

weekender

i'm just gonna repost my first example, and then some

//year and month to check if user has paid
$year = "2003";
$month = "12";

//ie this will return all users that paid in december 2003

//in table payments, field lastpayment is a mysql timestamp 
//which stores the date the card was charged
//this sql gets the month and year parts of field lastpayment, 
//and returns rows where they match the values 
//in $year and $month (above) and where validated = 1
$sql = "SELECT * FROM tablename ".
    "WHERE MONTH(dateposted) = $month ".
    "AND YEAR(dateposted) = $year ".
    "AND validated = 1";

note: you don't need to calculate how many days are in that month - the only parts you need are month and year. if you just used month, this would list all payments made in, say, december - of any year

adam

webdes03

weekender: thanks, you're being very helpful, just one more question...

say a user pays on december 8th... that makes their membership good to january 8th, if a user pays on january 1st, then they are good until february 1st... if I checked on january 5th to see if the user paid in january, then that user that paid on decenber 8th would not be found, even though they have a vaild subscription.

weekender

ok so what you're saying is you want to list users who have paid in the last month

//get the current year
$year = date("Y");
//then the current month
$month = date("m");

//then we calculate the year and month of 1 month ago
if($month == "01"){
    $month = "12";
    $year = $year - 1;
} else {
    $month = $month - 1;
}

//we now have the month and year of one month ago
//(even if that month is last year

//now we format it into a mysql timestamp
$ts = $year . $month;
//and the rest
//this bit returns dayhourminuteseconds
$ts .= date("dHis");

//so in date terms, $ts now equals //yearmonthdatehourminuteseconds

//now we query to get all payments where paymentdate
//within last month (paymentdate > $ts) and validated = 1
$sql = "SELECT * FROM tablename ". 
    "WHERE dateposted >= '$ts' ". 
    "AND validated = 1";

you see? off to bed now.

adam

webdes03

Now I understand... thanks a lot.