Parsing timestamps - do it in MySQL or in PHP?

Question

Let's say you've got a table with a timestamp column, and you want to parse that column into two arrays - $date and $time.

Do you, personally:

a) query like this DATE(timestamp), TIME(timestamp) , or perhaps even going as far as HOUR(timestamp), MINUTE(timestamp

b) grab the timestamp column and parse it out as needed with a loop in PHP

I feel like (a) is easier... but I know that I don't know anything. And it feels a little naughty to make my query hit the same column 2 or 3 times for output...

Is there a best-practice for this?

Answer 1

(a) is probably fine, if it is easier for your code base. I am a big fan of not having to write extra code that is not needed and I love only optimizing when necessary. To me pulling the whole date and then parsing seems like premature optimization.

Always remember that sql servers have a whole lot of smarts in them for optimizing queries so you don't have to.

So go with a), if you find it is dog slow or cause problems, then go to b). I suspect that a) will do all you want and you will never think about it again.

Answer 2

I would personally do (b). You're going to be looping the rows anyway, and PHP's strtotime() and date() functions are so flexible that they will handle most of the date/time formatting issues you run into.

I tend to try to keep my database result sets as small as possible, just so I don't have to deal with lots of array indexes after a database fetch. I'd much rather take a single timestamp out of a result row and turn it into several values in PHP than have to deal with multiple representations of the same data in a result row, or edit my SQL queries to get specific formatting.