php date() vs. mysql date_format()

[rect]

which one of these would have faster execution time? i would assume using the date_format() function within the mysql query would yield faster execution, but just curious if any one knows for sure. and as a general rule, is it better to use mysql functions in a query when possible?

thanks!

[redux]

although i have no test data to back this up, i would say the mysql way is slighly quicker. i usually tend to do what i can in the query, as i keeps the actual php code a lot cleaner, and it's easier to maintain (or if you're switching db at a later stage). just my $0.02 worth of advice. might be worth writing a test suite, doing a select of 1000 records in both methods and clocking them...but at the moment i can't be physically asked, sorry

[DR_LaRRY_PEpPeR]

wouldn't you use PHP's date() for Unix timestamps (e.g. timestamps stored in an INT) and DATE_FORMAT() for dates/times in DATE/TIME/DATETIME/TIMESTAMP columns? so it depends on how the date is stored.

[freddydoesphp]

Or

DATE_FORMAT(FROM_UNIXTIME(datefield), '%y-%m-%d')); 

to format your unixtimestamp in the query.

[DR_LaRRY_PEpPeR]

according to the manual, you can just do this too:

FROM_UNIXTIME(datefield, '%y-%m-%d')

[rect]

thanks for the replys! i think i will try and do my date formatting in the SQL query because it seems to make the most sense to query and end up with data exactly how i want it.

[Skunk]

I don't know what the generally accepted "best practise" for this is, but I always deal with date formatting at the last possible moment (i.e in the code that deals with final presentation to the user) as date formatting is fundamentally a presentation issue. Now that I've started using Smarty as my final template system I deal with date formatting in a Smarty template tag as that completely seperates the decision of how the date should be displayed form the "business logic" and database interactions. This is a great principle if you are working with more complicated three tier architecture sites but for small sites I don't see any reason not to format the date in the SQL query. As far as performance goes unless your traffic is high enough to put a significant load on your server (say more than 2 or 3 hits a second) it won't make any difference either way. If you ARE getting that kind of high traffic you should look at benchmarking to find out which has the best performance (but then you should probably also be looking at some kind of three tier architecture )