Is ok to use datetime as primary key in MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BornForCode
Forum Contributor
Posts: 147
Joined: Mon Feb 11, 2008 1:56 am

Is ok to use datetime as primary key in MySQL?

Post by BornForCode »

As i know MS SQL keeps the date in float so making an index on such field is not a problem, i would like to know if the same physical representation of datetime is applied in MySQL case.

Can you please provide me some details about this? I tried to find the information in MySQL doc but it was impossible.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Is ok to use datetime as primary key in MySQL?

Post by onion2k »

Not something I'd do. MySQL stores a datetime as two sets of 4 byte integers in a packed format ... years*10000 + months*100 + days and hours*10000 + minutes*100 + seconds. If you were certain you'd never need to store two records with the same key then it would work, but in my experience no matter how certain you are now whatever it is will throw up a problem in the future.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Is ok to use datetime as primary key in MySQL?

Post by Inkyskin »

Agreed, the PK should really be as unique as humanly possible - and with a field thats also used as a variable, you could easily get messed up. I'd stick with an auto incrementing standard integer.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Is ok to use datetime as primary key in MySQL?

Post by pickle »

It's OK to use if you are sure you want to have the restriction of only having at most one entry per second. If the time needs to be unique, making it the primary key is alright. If, though, you just want a unique key per row, do as ~Inkyskin says & make it an auto incrementing integer column.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
BornForCode
Forum Contributor
Posts: 147
Joined: Mon Feb 11, 2008 1:56 am

Re: Is ok to use datetime as primary key in MySQL?

Post by BornForCode »

The table is an aggregation one and i won't be concurrent inserts or so on, the finest value that i need is up to hour (not minutes and secs).

If i make a pk using an auto_increment i will have to create unique on the date column to and this is somehow redundant :roll:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Is ok to use datetime as primary key in MySQL?

Post by pickle »

Redundancy can be a good thing. Your currently relying on best practice or a cron job to ensure your data makes sense. Why not put a hard rule in the database (the primary key) to ensure data integrity? If you're only writing once an hour, there won't be any serious write delays due to indexing, and your reads will be a bit faster.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply