Speed?

Which is a faster/better way to store dates:
1)Using a datetime field type
or
2)Storing the unix timestamp in a int field

as far as speed goes, the time it takes to insert a piece of data in the database is so small that I doubt you could tell the difference. As far as the data types goes, if you need to use any of the MySQL built in Date manipulation functions nuse datetime, if you don't plan on using the date functions then you could store the date any way you like.

But which would you say is better?

I would personally use datetime. As to whether it is better I never really gave it much thought and don't think I will...

NOTE: The above statement is worded as to avoid starting an argument on the subject that I don't have time for...

I've used both methods, I prefer the datetime field also. I don't think there really is a huge advantage one way or the other though.

OK thanks for your comments guys.

i prefer (and recommend) use of an int field instead...

zombie,

out of curiosity why do you recommend using an int?
I used to use an int every time, but when I switched to using datetime I didn't notice any performance difference.

The reason I switched is because I was writing an application once and I had access to view the data as it was stored in the database. I was trying to debug some SQL statements and I couldn't determine if the date fields were correct without writing code to put it in a readable form, just glancing at the data didn't help me.

I use Postgresql, and in that database you have interval types, which means you can do all the date math in the database.

when MySQL gets around to adding interval types then the clear winner will be to use a "timestamp" type.

Note that MySQL decided timestamp types would be auto-updating for the current insert / update time, clearly against SQL spec, which lists it as the standard type for storing date/time, then MySQL created another type to handle date / time without the autoupdate.

They may have to reverse those two eventually, especially if they plan on integrating with SAPdb.

jaltiere: it has nothing to do with speed.

it is just plain simpler than with sql commands. i like to keep as much of my logic (as i can) in php.

also, because you often need to present the date/time info to the user in not-mysql format, like "March 9th 2000" or whatever. that is much more easy to do with unix (int) time than with mysql time (you have to convert this one to unix time first.. see the extra step?)


btw, for that debugging purposes, you can always convert it to "human readable format" like so: