CODEWITHSUNDEEP
mysqldatetime
Nir
Nir

Reputation: 25379

Mysql - unix time vs datetime

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 retrieving the data is easier with mysql time since I can use mysql function like DAYOFYEAR() without FROM_UNIXTIME().

Any advice is most welcomed.

Upvotes: 3

Views: 1859

Answers (1)

Greg
Greg

Reputation: 321854

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.

Upvotes: 1

Related Questions