Unix timestamp vs datetime

php mysql sql datetime unix-timestamp
Yeti picture Yeti · Jun 1, 2010 · Viewed 10.8k times · Source

Possible Duplicate:
Datetime vs Timestamp?

I have a Mysql table, which has a column add_date. It tracks the date/time when the record was added to the database.

Queries based on this table:

  1. Display when the record was added in the format: 2 hours ago, 4 weeks ago, 1 year ago etc
  2. Allow users to search records inserted in any day/month/year. So the user may be allowed to choose to see the records inserted in 2009 only.

Which would be better in this case - unix timestamp or datetime?

Right now I'm using both, but since the table will have millions of records over time, having both columns may affect the size of the database.

Unix timestamp seem to be better for conversion to 2 hours ago format in PHP and also it is timezone independent. But datetime has better readability and making a query for a particular date/time/year seems easier.

Your suggestion?

Answer

Pekka picture Pekka · Jun 1, 2010

When you have the choice, I'd say go for mySQL dates.

  • You won't have to take care of date range issues

  • You can easily query time spans using mySQL's date functions (BETWEEN(), DATE_ADD etc.)

  • Date related queries will be much faster, especially when you have millions of records, because you won't have to use FROM_UNIXTIME() which can be expensive in large queries

  • It's child's play to convert DATE fields into UNIX timestamps when necessary.

Related questions

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: mysql_query(" SELECT * FROM `table` WHERE `column`.contains(…

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More