Unix timestamp VS datetime
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:
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?
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.
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?
通过使用unix时间戳和MySQL from_unixtime()函数将结果转换为需要的日期时间格式，您可以获得两全其美的效果。
上一篇: PHP，MySQL
下一篇: Unix时间戳VS日期时间