Date, what is best 00:00:00 DD-MM-YY or Timestamp?

[TeddyKiller]

What you reckon? what is best, 00:00:00 DD MM YY format, or Timestamp.

Thanks

[Mchl]

For what?

[premiso]

I prefer to go with this:

 

YYYY-MM-DD HH:MM:SS

 

As two digit years can be easily confused with months and or confused with milleniums.

 

Timestamps I do not care for as they have a limit of something like 1969 - 2032. So they do not work for birthdates and other historical / furturistic dates.

[TeddyKiller]

Ah.. yeah I was thinking timestamps for everything in the database, people DOB, last login etc. Though if theres a limit then.. wouldn't be that great to use for DOB atleast.

[Mchl]

It's pretty much useless for DOBs or other historic events. It works pretty well for... well timestamping (i.e. recording when events in your application happen). Remember that MySQL's DATETIME takes 8 bytes, while TIMESTAMP is only 4 bytes. DATE however is only 3 bytes (that's for MyISAM, but I suspect it's quite similar for InnoDB)

 

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

[salathe]

I'm going to play devil's advocate and vote for the Unix timestamp, at least that value is constant(ly incrementing) whereas a formatted string like the one in the OP leaves plenty of room for errors: of the timezone variety, and lack of range (YY).

[Mchl]

I'm going to play devil's advocate and vote for the Unix timestamp, at least that value is constant(ly incrementing) whereas a formatted string like the one in the OP leaves plenty of room for errors: of the timezone variety, and lack of range (YY).

 

That's why it (UNIX time)'s so good for timestamping

[jmajeremy]

premiso and salathe both have good ideas. I tend to use the YYYY-MM-DD HH:MM:SS format for compatibility with MySQL, but it could also be a good idea to take note of the UNIX timestamp as a backup, or unique identifier. On a high-traffic website it's possible to have more than one transaction occur within the same second, but it's unlikely you'll ever get two identical UNIX timestamps.

[Mchl]

On a high-traffic website it's possible to have more than one transaction occur within the same second, but it's unlikely you'll ever get two identical UNIX timestamps.

 

Actually, if transactions happen within one second there is a very high probability they'll get same timestamp. After all it's number of SECONDS since Epoch.