CODEWITHSUNDEEP
mysqldatabase-design
Goro
Goro

Reputation: 10249

MySQL: Using DATETIME as primary key

My database will be storing a large number of data points, so I am using an unsigned BIGINT as the primary key.

Would it ever make sense to use a DATETIME object as the primary key?

Thanks,

Upvotes: 3

Views: 17124

Answers (5)

nvogel
nvogel

Reputation: 25526

Yes of course it makes sense for a date/time to be a key or part of a key if you need to uniquely identify discrete points or periods of time. I can't say if that applies to your scenario but as a general rule there's no fundamental reason why keys can't be based on time - almost any data warehouse does it.

Upvotes: 20

aaz
aaz

Reputation: 5196

It makes sense if your data comes from a single time-ordered set. Say, a record of financial transactions. If you have multiple data points which naturally occurred at different instants, but have the same timestamp due to rounding, change the low-order bits to discriminate them.

This is more problematic in MySQL than in other databases, because timestamps are stored with only 1-second precision. (Edit: as of 5.6.4, MySQL has microseconds precision on time types)

Upvotes: 3

Jesse Cohen
Jesse Cohen

Reputation: 4040

If you happen to have multiple observations per second this will fail. For this reason it's probably better not to unless you can guarantee that there will never be more than one point per second.

Upvotes: 2

Alessandro
Alessandro

Reputation: 1336

It wouldn't make sense, as you would be limited to one record per second without any actual reason for that.

Upvotes: 3

user114600
user114600

Reputation:

No because it can't be guaranteed to be unique. Stick with BIGINT. You can put a nice index on the DateTime for querying and it will be good enough.

Upvotes: 5

Related Questions