mysql - unix time vs datetime

Question

I'm writing a php/mysql application which is similar to website visitors tracking solutions. It will handle 100,000s of inserts a day and will be able to be queried by counting pageviews/day etc...

I wonder if from terms of performance its better to keep time as integer (unix time) or as mysql time. Any idea on effect on performance? Seems like reteriving the data is easier with mysql time since I can use mysql function like DAYOFYEAR() without FROM_UNIXTIME(). Any advice is most welcomed.

Answer 1

A TIMESTAMP will take slightly less storage space (4 bytes) than a DATETIME field. Performance-wise they should be roughly the same.

If you're thinking about doing GROUP BY DAYOFYEAR(...) then you might be better storing the DAYOFYEAR value in its own column to get better performance.