What is the best way to store date and time in a MySQL database for later display with PHP?

Question

I want to save the date and time that the user completed on my website in a MySQL database. I would like to be able to do the following with ease:

• Save date and time as one field in the database
• Use the built-in PHP or MySQL function to generate the action time-date
• Keep date-time based on my server time and don’t worry about user time zones.
• Order By date and time field when querying MySQL
• Later, show the date-time in many different formats using PHP built-in methods.

Here are my questions:

• What data type should I use in MySQL (e.g. timestamp , datetime ...)?
• What method should I use to create a date-time (e.g. MySQL now() , PHP date() ...)?
• What PHP method should I use to format the date and time in various ways (e.g., 04/23/2012, 5:00 PM on Monday, July 2012)?

Answer 1

I would save it as a date, not a timestamp.

I usually use the PHP date function, and if you ever want to keep the time relative to the user time zone, you can simply change the time zone based on user settings.

When you pull it out of the database, use strtotime () to convert it, then you can use all date () functions to display it however you want. Example:

 echo date('F j, Y',strtotime($db_datetime)); //Displays as 'March 5, 2012' 

Answer 2

If you set the mysql data type to a non-zero timestamp, then save the rows with a null value for this column, mysql will automatically update the timestamp for you.

As for reading it again, you can just use php strtotime and a date object to get it in the format you need.

Answer 3

For your requirement, you should use the datetime data type. It will store both the date and time from your input field based on your request.

To get the date and time, you can use the mysql date_format () or PHP date () function. Datetime will always be stored based on server time, not client time.