Best practice for storing the date in MySQL from PHP

Question

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 1

One advantage of using the MySQL date/time types is to be able to more simply use the date/time functions in MySQL.

The DATE type also has the advantage in that its only storing day, month and year so there is no space wasted or comparison complication that a seconds since epoch time would have for situations where you only cared about the day and not the time.

Personally I tend to use a database as just a dump for data so such functions are of little interest. In PHP I tend to just store the date in integer format for pretty much the reasons you state.

Answer 2

Easier to compare, and mysql provides a lot of date functions.

Answer 3

Easier maintenance is a plus. Seeing the actual date when you do:

select * from table where ...

is pretty nice.

Answer 4

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

Answer 5

Using database datetime is more efficient because every time you need to query you would need to apply from_unixtime() function to extract data from unix datetime col of the table. Using this function in where clause will completely ignore any index usage.

say my query is:

select col1,col2,colUnixdatetime from table where colUnixdatetime between wtvdate1 and wtvdate2

I would need to run:

select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2

This above query will completely ignore any indexes, well there is no use of indexes here as they will never be used coz I will always have to use a function to get the real date time.

Any in-built function used on LHS of condition in a where clause would not use any indexes and if you have a HUGE table, your query will take longer.