Best practices for storing & selecting time/date with php & mysql?

Question

I often find myself storing data in a mysql database, and then wanting to display all sorts of stats about my data, specifically stuff like 'how many rows do I have for this date, or that date'.

Does anyone know of (or could write) a good tutorial on this subject? Ideally a good tutorial would overview:

1. Best practices when storing the data (i.e. what formats to use, how to use them servertime vs. generated time, etc.)

2. Best practices when selecting data from the database with php (i.e. how to sort rows by date, how to retrieve only rows from a certain date, or a certain hour, etc)..

3. Timezones and other issues that might come up.

Thanks in advance.

Answer 1

Nothing will help you more than mysql documentation.
Just google for mysql date and time column types, mysql date and time functions and mysql timezone settings.

There are no "best practices" to choose from. Just basic general rules. E.g. there is no "best practice" to sort rows by date. You just order it by date field, like any other column. That's it.