[ACCEPTED]-Best practice for storing the date in MySQL from PHP-unix-timestamp

Accepted answer
Score: 34

If you store dates as Unix timestamps in 15 the database, you're giving yourself the 14 heavy lifting. You have to convert them 13 to the formats you want to use, you have 12 to do the calculations between date ranges, you 11 have to build the queries to get data in 10 a range. This seems counter-intuitive- surely 9 your "programmer time" is best 8 spent solving real problems?

It seems much 7 better practice to store dates and times 6 in the proper format that MySQL has available, then 5 use the database functions to create the 4 queries for the data you want. The time 3 you would waste doing all the convertions 2 and mucking about is massive compared to 1 the afternoon spent reading (and understanding) 11.6 MySQL Date and Time Functions

Score: 9

I've also been a huge fan of the unix timestamp 16 all my life. But I think the correct answer 15 is: "depends". I recently did 14 a single table database where I wanted to 13 only list URLs. There would be a date field, but 12 the date field is purely for sorting. I.e 11 order by last_crawled. Which means I will 10 never use any built-in date functions on 9 that field. It is merely an easy way to 8 get the oldest entries first and I will 7 never apply date functions to this field. Now, had 6 I made this a date field, I would have lost 5 out on two things:

  1. A datetime field is twice the size of an integer
  2. Sorting by an integer is faster (not 100% sure of this, pending outcome of this question)

However, for another system 4 I had to store transactional information. This 3 made using internal mysql date functions possible which turned out to 2 be very useful when we had to start doing 1 reports.

Score: 7

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

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

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

Score: 6

@Smita V, the inefficient query to which 15 you refer is only so because you're applying 14 your conversion function incorrectly to 13 every table row, where you should apply 12 it to the condition itself. So instead of

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

, which 11 converts every row on the table to compare 10 it to the date you've got. You should use

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

Doing 9 it this way WILL use the appropriate table 8 indexes.

@treznik a while ago I moved from 7 a uts integer to a datetime or timestamp 6 data types, for the reasons mentioned above, in 5 that they're much easier to read and manipulate 4 (I do quite a lot of direct table access). However 3 I've lately started to re-think this approach 2 for two reasons:

  1. There is no time zone location stored, so you're inferring the time zone based on your location. This may or may not be an issue for you.
  2. It ignores daylight saving time. So when the clocks go back at 2am, you will get 1:30am twice, and saying 2011-10-30 01:30 doesn't let you know this, whereas 1319938200 does. I don't think there's a native way in mysql to store date including time zone, except as a string (2011-10-30 01:30 BST).

I'm still trying to figure 1 out the answer to this, myself.

Score: 3

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

say my query is:

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

I would need to run:

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

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

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

Score: 2

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

select * from table where ...

is pretty nice.

Score: 1

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

More Related questions