21

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?

Kerem
  • 11,377
  • 5
  • 59
  • 58
Yeti
  • 5,628
  • 9
  • 45
  • 71

3 Answers3

16

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.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • 2
    This. Just use UNIX_TIMESTAMP(your_column) if you want to play with timestamp on the php end. But in your database, use the native date type to store dates. Moreover, the data in your database may be used by other applications which may prefer SQL date format than timestamps. – Arkh Jun 01 '10 at 09:39
  • Will there be any problem converting date to unix timestamps with respect to timezone? – Yeti Jun 01 '10 at 09:39
  • 1
    @Lost_in_code good question actually. There is a number of factors involved, the timezone setting of the mySQL server, and the system's timezone setting that PHP will use by default. In general, mySQL DATETIME fields are timezone insensitive. As long as you store UTC dates there, you should be able to convert them into timestamps easily, but be careful if mySQL and/or PHP have time zones different from UTC. Related: http://stackoverflow.com/questions/18449/dealing-with-php-server-and-mysql-server-in-different-time-zones – Pekka Jun 01 '10 at 11:08
5

I'd go for the mysql formats, just because mysql has a plenty of datetime function, to use which with timestamp will cost you another conversion.

but since the table will have millions of records over time, having both columns may affect the size of the database.

oh my.
are you really concerned in 4 additional megabytes of space?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • I don't really know how much space would that take (I'm new to large databases). If you say it's just gonna be couple of MBs then I don't see a problem having both. – Yeti Jun 01 '10 at 09:29
  • If you don't konw the number, you shouldn't mention it. No programmer should ever guess. It's weather forecasters job. http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html will tell you that datetime take 8 bytes and int for timestamp - 4. so, 4 * 1000000 = less than 4Mb – Your Common Sense Jun 01 '10 at 09:32
  • 1
    You'll probably want to use an index on both of the fields as well. That's not too expensive spacewise, but if you're using multiple field indices it could impact your performance. – Kaivosukeltaja Jun 01 '10 at 09:33
5

You can get the best of both worlds by using a unix timestamp and the MySQL from_unixtime() function to convert the result to datetime format where needed.

Kaivosukeltaja
  • 15,541
  • 4
  • 40
  • 70
  • 1
    You can also get the best of both worlds by using datetime and using the date functions to convert to unix timestamps when necessary. Given that using datetime data types makes many queries easier, I'd say it's better to go that way. – El Yobo Jun 01 '10 at 09:50
  • What El Yobo says. Potential performance loss is what would make me shy away from `from_unixtime()` in big tables (Don't have any benchmarks though.) – Pekka Jun 01 '10 at 09:53
  • Of course from_unixtime() shouldn't be used on queries that are expected to result large amounts of data, only ones that return result sets that are meant to be displayed. Also, datetime types can still be used to make queries more readable with unix_timestamp('2010-06-01 13:00'). The conversion is done only once per query so there's no performance loss. – Kaivosukeltaja Jun 01 '10 at 09:59