4

I've had some trouble choosing an efficient way to insert a time in mysql database.

I know mysql has its own timestamp system but it is not what I'm looking for. I currently use date("Y-m-d H:i:s", time()); and it gets the job done.

But I would like to know what the standard is or what the best practices are to insert time that make it easier for retrieval later.

Thanks.

ps. I've searched the site for any dupe posts but I could not find any, if you think this is a dupe feel free to close.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
andrewk
  • 3,721
  • 4
  • 30
  • 37

3 Answers3

3

you insert it the way mysql likes to store it, and on retrieval, you format it how you like (DATE_FORMAT()). You don't store it in some other format because that is how you want to display it, as that then breaks all the functionality of the date\time.

2

The best thing to do is store dates and times in the database in UTC and convert them to the appropriate timezone when displaying them. MySQL even has a special function for "now in UTC": UTC_TIMESTAMP().

When you use PHP's date function to format a Unix timestamp, you are outputting the representation of the date and time in the web server's timezone. That is rarely what you want, and it causes all sorts of problems whenever the web server's timezone changes, such as during Daylight Saving Time or if the web server is physically relocated to a different timezone.

Daniel Trebbien
  • 38,421
  • 18
  • 121
  • 193
1

You can store them as a MySQL timestamp and then use UNIX_TIMESTAMP() to convert to unix time for use in PHP, you can also use FROM_UNIXTIME to convert from unix time to a MySQL timestamp when inserting.

SELECT UNIX_TIMESTAMP(`field`) AS `unix_timestamp` FROM `table`;
INSERT INTO `table` SET `field` = FROM_UNIXTIME(unix_timestamp);
Joseph W
  • 187
  • 7