Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I want to store the date and time that a user performs an action on my website into a MySQL database. I'd like to be able to do the following with ease:

  • Store the date and time as one field in the database
  • Use a built in PHP or MySQL function to generate the date-time of the action
  • Store the date-time based on my server's time, and not worry about user timezones.
  • Order By the date-time field when I query MySQL
  • Later, display the date-time in many different formats using built in PHP methods

Here are my questions:

  • What data type should I use in MySQL ( eg. timestamp, datetime ... )?
  • What method should I use to generate the date-time ( eg. MySQL's now(), PHP's date() ... )?
  • What PHP method should I later use to format the date-time in various pretty ways ( eg. 23/4/2012, 5pm on Monday, July 2012 ... )?
like image 665
T. Brian Jones Avatar asked Aug 02 '12 18:08

T. Brian Jones


People also ask

How do you display time and date in MySQL?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

Which data type is best to store the date and time?

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

How does MySQL store data and time?

MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.

How can store current time in PHP variable?

PHP time() Functionecho($t . "<br>"); echo(date("Y-m-d",$t));


2 Answers

I've struggled with this question for years, and I'm beginning to think that the best way might be to store the time as an integer that represents Unix time (number of seconds from Jan 1, 1970). I've done this and it works fine.

Personally I've never used datetime, and I can't think of a situation when I ever would use this. It just carries too many problems with it.

Timestamp is a lot better, but in MySQL it can't store a date later than 2032.

I would love to hear some serious discussion on this topic, but Stack Overflow might not be the best place for this.

like image 132
Magmatic Avatar answered Oct 24 '22 05:10

Magmatic


I would store it as a datetime, not a timestamp.

I normally use the PHP date function and that way if you ever want to store the time relative to the user's timezone you can simply change the timezone based off the user's settings.

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

echo date('F j, Y',strtotime($db_datetime)); //Displays as 'March 5, 2012'
like image 5
Pitchinnate Avatar answered Oct 24 '22 04:10

Pitchinnate