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.

Answer 3

b) is what I follow and I use it every time. It also gives you the flexibility of being able to control how you want it to appear in your front end. Think about this: If you are following a) and you want to do a change, you will need to change all the queries manually. But if you are using b) you can just call a function on this value (from the DB) and you are good to go. If you ever need to change anything, just change it within this function and viola! Doesn't that sound like a time saver to you ???

Hope that helps.

Answer 4

I would also use b). I think it is important that, if I at some point need to use names on the days, or the months in another language. I can use PHP locale support to translate it to the given language, that wouldn't be the case in a).

Answer 5

I think it boils down to this, do you feel more at home writing php code or mysql queries?

I think this is more a question of coding style than technical feasibility, and you get to choose your style.

Answer 6

If you need it in the SQL query itself (e.g. in a WHERE, GROUP BY, ORDER BY, etc), then way a) is preferred. If you rather need it in the code logic (PHP or whatever), then way b) is preferred.

If your PHP code actually does a task which can be as good done with SQL, then I'd go for that as well. In other words, way b) is only preferred if you are going to format the date for pure display purposes only.