MySQL Forums
Forum List  »  Newbie

Re: Milli and Micro seconds in mysql
Posted by: Rick James
Date: November 18, 2009 04:48PM

Sorry, no such thing.

Even if there were, it is risky to assume you can have a data-time with enough precision to be UNIQUE.

If you are using MyISAM, this would work:

ts TIMESTAMP NOT NULL,
seq SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY (ts, seq)

For each new value of the first part of the PK, namely ts, the auto_increment (second part, seq) will start over at 1 and automatically increment as needed. This will give you a 6-byte PRIMARY KEY, and allow up to 65K collisions on any second -- probably quite sufficient.

Alternatively, you could calculate a precise time (eg, PHP's microtime(true), Perl's Time::HiRes::time()) and store it as a DOUBLE. But you should also check for duplicate PRIMARY KEY and recalculate the time.

Please do note that it is possible to insert a row with a smaller PK that the "previous" insert. How?? Consider this timeline:
1. Thread A calculates time
2. Thread B calculates time
3. Thread B INSERTs
4. Thread A INSERTs
If you use the timestamp as a "highwater" mark, you will have a bug.

Options: ReplyQuote


Subject
Written By
Posted
Re: Milli and Micro seconds in mysql
November 18, 2009 04:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.