Page 1 of 2

MySQL dates - checking less than more than a date in PHP?

Posted: Thu Jul 15, 2004 6:17 pm
by mjseaden
Dear All,

I have a date stored in a MySQL table in standard MySQL 'date' format.

I also have a PHP string stored in DD/MM/YYYY format. Is there any way I can check whether this date is 'more' or 'less' than the date stored in the MySQL table? Or for example how many days away it is from this date?

Many thanks,

Mark

Posted: Thu Jul 15, 2004 6:19 pm
by Joe
You might just be able to use a standard less than comparison but if that doesn't work you may need to make unix time stamps to determine the right dates/times.

Posted: Fri Jul 16, 2004 3:37 am
by mjseaden
To get the UNIX timestamp (the number of seconds since 1st Jan 1970 I think), you need to use the time() function. Can you tell me how I'd use this to make the comparison?

Many thanks

Mark

Posted: Fri Jul 16, 2004 3:43 am
by JayBird
yeah, easy, just use the less than/more than comparison

Code: Select all

SELECT * FROM some_table WHERE old_date > new_date
Mark

Posted: Fri Jul 16, 2004 3:46 am
by mjseaden
Bech100,

Thanks for your suggestion. Rather than select rows in a table via a MySQL query, I need to make a comparative 'IF' statement - so I need the equivalent of

Code: Select all

if ( $thisdate > $thatdate )
{
    /* execute code */
}

Posted: Fri Jul 16, 2004 3:54 am
by JayBird
..in that case, you will need to convert each of the dates into a timestamp and compare them.

mark

Posted: Fri Jul 16, 2004 3:56 am
by feyd
^

Code: Select all

SELECT UNIX_TIMESTAMP( `date` ) `date` FROM `table`
then

Posted: Fri Jul 16, 2004 3:58 am
by mjseaden
Hi feyd,

Thanks for the help - can you clarify? What would I use to extract the UNIX_TIMESTAMP from the query? Then I would use time() to compare with the present date?

Many thanks

Mark

Posted: Fri Jul 16, 2004 4:00 am
by feyd
try the query..

Posted: Fri Jul 16, 2004 4:08 am
by mjseaden
Hi feyd,

I've tried this for the date cell 'CustomerViewFrom':

Code: Select all

SELECT UNIX_TIME_STAMP('CustomerViewFrom') CustomerViewFrom FROM Customers
I get this output from $row['CustomerViewFrom']...

Code: Select all

SELECT UNIX_TIME_STAMP('CustomerViewFrom') CustomerViewFrom FROM Customers
Which is the same as the query.

Many thanks

Mark

Posted: Fri Jul 16, 2004 4:09 am
by feyd
how'd you run the query?

Posted: Fri Jul 16, 2004 4:10 am
by mjseaden
Sorry my mistake - I asked my error output to display the query, not the error. It's generating the following error:

Code: Select all

Error performing MySQL query - You have an error in your SQL syntax near '('CustomerViewFrom') CustomerViewFrom FROM Customers' at line 1
Cheers

Mark

Posted: Fri Jul 16, 2004 4:18 am
by feyd
you may want to use UNIX_TIMESTAMP instead of UNIX_TIME_STAMP

Posted: Fri Jul 16, 2004 4:21 am
by mjseaden
Hi feyd - the date I am looking at is 07/07/2004, however the query seems to be reporting a timestamp of 0.

Posted: Fri Jul 16, 2004 4:27 am
by feyd
what's the code you have for it?

My local test of this returned:
[CustomerViewFrom] => 1089183600