How can I compare MySQL/PHP dates?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bredoteau
Forum Newbie
Posts: 18
Joined: Fri Apr 01, 2005 7:46 am

How can I compare MySQL/PHP dates?

Post by bredoteau »

Hi,

is there a simple way of comparing MySQL-dates with PHP-ones?

When I query MySQL I receive a string like 2005-04-05 (does it depend on the locale settings or can I use substr to retrieve data??).

I produces dates like 05. 04. 2005 with PHP. Comparing those does not work. Are there any useful functions for this?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how do you want to compare them, specifically? Comparing if they are equal, you can do a lot.. have MySQL use DATE_FORMAT() to change the for it returns it, or (MySQL) UNIX_TIMESTAMP() to generate a unix timestamp of the date... or you can have php use MySQL's format..

If you want to comare if A is more recent than B, then you need to use MySQL's format, or something similar, as that accurately allows math-like comparison.
bredoteau
Forum Newbie
Posts: 18
Joined: Fri Apr 01, 2005 7:46 am

Post by bredoteau »

As I need to construct a MySQL query I cannot use MySQL-side functions.

I didn't manage to convert the data via any php function like strtodate.
But it works now, I just did it "by hand":

Code: Select all

//$min/$max are strings of the format &quote;01. 04. 2005&quote;

$tempmax = substr($search_arrayїmax_date],7,5).&quote;-&quote;.substr($search_arrayїmax_date],4,2).&quote;-&quote;.substr($search_arrayїmax_date],0,2);
$tempmin = substr($search_arrayїmin_date],7,5).&quote;-&quote;.substr($search_arrayїmin_date],4,2).&quote;-&quote;.substr($search_arrayїmin_date],0,2);

sql = &quote;SELECT * FROM table WHERE date BETWEEN $tempmin AND $tempmax;

feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

untested...

Code: Select all

$part = explode('.', $min);
$mysql_date = "{$part[2]}-{$part[1]}-{$part[0]}";
meaby some trimming will be needed too...
Post Reply