Best practice for storing the date in MySQL from PHP

mysql datetime unix-timestamp
treznik picture treznik · Jul 12, 2009 · Viewed 23.8k times · Source

I've been using the unix timestamp all my life.

I like it because it's easy to compare, it's fast because I store it as an integer. And since I'm using PHP, I can get any date/time format with date() function from the unixtimestamp.

Now, some people are saying that it's best to use the DATETIME format. But besides the more suited name, I don't see any advantages.

Is it indeed better to use DATETIME, if so, what are the advantages?

Thanks.

Answer

Gav picture Gav · Jul 12, 2009

If you store dates as Unix timestamps in the database, you're giving yourself the heavy lifting. You have to convert them to the formats you want to use, you have to do the calculations between date ranges, you have to build the queries to get data in a range. This seems counter-intuitive- surely your "programmer time" is best spent solving real problems?

It seems much better practice to store dates and times in the proper format that MySQL has available, then use the database functions to create the queries for the data you want. The time you would waste doing all the convertions and mucking about is massive compared to the afternoon spent reading (and understanding) 11.6 MySQL Date and Time Functions

Related questions

MySQL convert date string to Unix timestamp

How do I convert the following format to unix timestamp? Apr 15 2012 12:00AM The format I get from DB seems to have AM at the end. I've tried using the following but it did not work: CONVERT(DATETIME, Sales.SalesDate, 103) AS …

Read More
How should unix timestamps be stored in int columns?

I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I …

Read More
How to Insert custom date into mysql timestamp field?

I have tried to insert date and time string formatted into mysql timestamp field by using following two methods but both shows me 0000-00-00 00:00:00 INSERT INTO test VALUES ( UNIX_TIMESTAMP('2013-08-05 18:19:03') ) INSERT INTO test VALUES ( UNIX_…

Read More