Timestamp synchronization - do it in MySQL or in PHP?

Question

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

You personally:

a), for example, DATE(timestamp), TIME(timestamp) , or possibly even until HOUR(timestamp), MINUTE(timestamp

b) take the timestamp column and parse it as needed using a loop in PHP

It seems to me that (a) easier ... but I know that I know nothing. And it seems a little mischievous for my query to hit the same column 2 or 3 times for output ...

Is there any best practice for this?

Answer 1

(a) probably great if your code base is simpler. I am a big fan of not writing extra code that is not needed, and I like only optimizing when necessary. I pull the whole date, and then the parsing seems like premature optimization.

Always remember that sql servers have a lot of skills to optimize queries, so you do not need to.

So go with a) if you find that the dog is slow or causing problems, then go to b). I suspect that a) he will do whatever he wants, and you will never think about it again.

Answer 2

I personally did (b). In any case, you will cycle strtotime() lines, and the PHP functions strtotime() and date() so flexible that they will handle most of the date and time formatting problems you encounter.

I try to keep my database result sets as small as possible, so I don't have to deal with a lot of array indexes after retrieving the database. I would rather take one timestamp from the result string and turn it into multiple values ​​in PHP, than to deal with multiple representations of the same data in the result string 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 to control how you want it to appear on your front end. Think about it: if you follow a) and want to make changes, you will need to manually change all the queries. But if you use b), you can just call the function by that value (from the database), and you're good to go. If you ever need to change something, just change it inside this function and viola! Doesn't that sound like a time saver?

Hope this helps.

Answer 4

I would also use b). I think it is important if at some point I have to use names in days or months in another language. I can use the support of the PHP language standard to translate it into a given language, which will not be the case in a).

Answer 5

If you need it in the SQL query itself (for example, in WHERE , GROUP BY , ORDER BY , etc.), then method a) is preferred. If you need it in the logic of the code (PHP or something else), it is preferable to use method b).

If your PHP code really does a task that can be just as well done with SQL, then I would go for that too. In other words, method b) is preferred only if you are going to format the date only for purely displayed purposes.

Answer 6

I think it comes down to this, do you feel at home, write php code or mysql queries?

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