Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Mysql query to get day, month, and year?

[lexington]

Hello, I have always used php to get the current day, month, and year from the database. I heard that using a mysql query instead is faster. Here is what I have:

PHP Code:

"UPDATE stats_table SET rank = '".$rank."'
                             WHERE song_id = '".$rank_row['song_id']."' 
                             AND day = '".date("j")."'
                             AND month = '".date("n")."'
                             AND year = '".date("Y")."'" 


You can see where I used the date() functions. Could you tell me how to make do the same thing using mysql to get the current day, month, and year? Basically the same query you see above without php generating the date. It cannot be NOW() or anything, it has to be separate rows for the date like I have posted. Thanks.

[marrtins]

You can use EXTRACT() function. Please check out: http://dev.mysql.com/doc/refman/5.1/...nction_extract

[lexington]

Thanks, but I am having a hard time understanding which one to use for mysql to get the separate day, month, and year without inputting the date value. Basically looking for the equivalent to date() in php but in a mysql format.

[marrtins]

I believe it won't have any noticeable impact on performance, unless you do this query billions times.

If you want to improve performance, I would rather start with optimizing overall application design than with such micro optimizations.

[mattle]

Um...personally, I'd look at the following MySQL functions:

DAY(), MONTH(), YEAR()

[lexington]

Originally Posted by mattle

Um...personally, I'd look at the following MySQL functions:

DAY(), MONTH(), YEAR()

Yeah but I must be doing it wrong since if I use:

SELECT * FROM stats_table where day = DAY()

It errors. What would be the correct query please? Thanks.

[mattle]

All of those functions take a date argument, just like the PHP date() function does--except there are no default values in MySQL functions. To get the current day, for example, you'd use DAY(NOW()).

http://dev.mysql.com/doc/refman/5.1/...functions.html

[lexington]

That is exactly what I needed thanks So you guys can confirm that using DAY(NOW()) is slightly faster (even if noticeable) than using a php date() function to display the current day?

[tim2718281]

Originally Posted by lexington

That is exactly what I needed thanks So you guys can confirm that using DAY(NOW()) is slightly faster (even if noticeable) than using a php date() function to display the current day?

No, it will be slower.

But why don't you just time it and see?

[mattle]

Originally Posted by tim2718281

No, it will be slower.

But why don't you just time it and see?

I don't know that you can say that unequivocally. Certainly, this would be faster:

PHP Code:

$date = date("j"); 


than

PHP Code:

$date = mysql_result(mysql_query("SELECT DAY(NOW()) AS date", 0, "date")); 


However, the following two approaches will be dependent on server hardware, and process priority:

PHP Code:

mysql_query("UPDATE table SET day=" . date("j") . " WHERE id=1");
// vs
mysql_query("UPDATE table SET day=DAY(NOW()) WHERE id=1"); 


It's oft claimed here that php can do internal date calculations faster than MySQL can, but I've seen zero evidence to support such claims.