Unix timestamp vs datetime [duplicate] – Here in this article, we will share some of the most common and frequently asked about PHP problem in programming with detailed answers and code samples. There’s nothing quite so frustrating as being faced with PHP errors and being unable to figure out what is preventing your website from functioning as it should like php and mysql . If you have an existing PHP-based website or application that is experiencing performance issues, let’s get thinking about Unix timestamp vs datetime [duplicate].
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:
- Display when the record was added in the format: 2 hours ago, 4 weeks ago, 1 year ago etc
- 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?
Solution :
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_ADDetc.) -
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?
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.