5

Is there a possibility of getting a unique timestamp value for for each record in MySQL??..

I created a sample table 

CREATE TABLE t1 (id int primary key, name varchar(50), 
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

and ran some sample INSERTIONS and seems to be timestamp values are duplicated.

e.g  insert into t1(id,name) values(1,"test");
NULL_USER
  • 125
  • 1
  • 2
  • 8
  • 3
    Unless you prevent records from being inserted at the same time, there can be duplicate timestamps. – Michael Mior Apr 25 '12 at 17:26
  • 1
    @MichaelMior Can't I increase the "TIMESTAMP sensivity"??.. using milliseconds etc? Marcus Just Using a Select query. through naked eye I can see it. – NULL_USER Apr 25 '12 at 17:30
  • MySQL doesn't support microseconds (yet, 5.6.4 will) so i'd suggest going with an integer field. – Kostis Apr 25 '12 at 17:30
  • @Kostis You mean just using an Integer field and keep incrementing?.. How can I cope with an update??.. Find the largest integer value and add one? – NULL_USER Apr 25 '12 at 17:33
  • @Ashan What about your use case isn't solved by an auto-incrementing primary key? – Michael Mior Apr 25 '12 at 17:34
  • @MichaelMior then there will be a problem for updates?? Am I correct??.. If a field is updated increment value won't change, I want to identify the order where records have changed. – NULL_USER Apr 25 '12 at 17:36
  • @Ashan, a simpler solution would be to use PHP microtime() function http://php.net/manual/en/function.microtime.php – Kostis Apr 25 '12 at 17:37
  • @Kostis My Application is in java. sorry I forgot to mention that :( – NULL_USER Apr 25 '12 at 17:40
  • @Ashan :-) I'm not familiar with Java but i pretty sure that it offers a similar method of getting the systems' micro/nano time – Kostis Apr 25 '12 at 17:42
  • @Kostis You mean get the system time using java code and store it other than using TIMESTAMP in MySQL. That's a good idea and I will be able to get a unique value.Thanks for the great tip. Sorry I can't up vote you.. :(.. really appreciate your help.:) – NULL_USER Apr 25 '12 at 17:48
  • @Ashan glad i could be of assistance :-) – Kostis Apr 25 '12 at 17:51

2 Answers2

6

Some day soon (5.6.4), MySQL will provide fractional seconds in TIMESTAMP columns, however, even fractional seconds aren't guaranteed to be unique. Though theoretically, they'd most often be unique, especially if you limited MySQL to a single thread.

You can use a UUID if you need a unique number that is ordered temporally.

SELECT UUID(); yields something like:

45f9b8d6-8f00-11e1-8920-842b2b55ce56

And some time later:

004b721a-8f01-11e1-8920-842b2b55ce56

The first three portions of a UUID consist of the time, however, they're in order from highest precision to least, so you'd need to reverse the first three portions using SUBSTR() and CONCAT() like this:

SELECT CONCAT(SUBSTR(UUID(), 16, 3), '-', SUBSTR(UUID(), 10, 4),
  '-', SUBSTR(UUID(), 1, 8))

Yields:

1e1-8f00-45f9b8d6

You obviously couldn't use a function like this as a default value, so you'd have to set it in code, but it's a guaranteed unique temporally ordered value. UUID() works at a much lower level than seconds (clock cycles), so it's guaranteed unique with each call and has low overhead (no locking like auto_increment).

Using the UUID() on the database server may be preferred to using a similar function, such as PHP's microtime() function on the application server because your database server is more centralized. You may have more than one application (web) server, which may generate colliding values, and microtime() still doesn't guarantee unique values.

Useful reading for understanding the components of UUID

informatik01
  • 16,038
  • 10
  • 74
  • 104
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • +1 great info. Few questions however arised. How does the three first numbers guarantee temporal order? Manual says just vaguely: "The first three numbers are generated from a timestamp." Should the full `UUID` be always used (just the first three values reversed for ordering)? Where to find more info about this function. Manual didn't mention the "highest precision to least" order? – ZZ-bb Apr 25 '12 at 21:01
  • @ZZ-bb, The MySQL manual doesn't include the details because the UUID format that they use is a [standard](http://pubs.opengroup.org/onlinepubs/9629399/toc.pdf). The fourth part of the UUID will change whenever you set the clock back. This is to ensure that the value as a whole is unique. As long as you don't mess with your clock, the first three parts are fine. – Marcus Adams Apr 25 '12 at 21:45
  • CORRECTION: the **third portion** of the UUID contains also a 4-bit "version" in the most significant bits, followed by the high 12 bits of the time (see ["UUID record layout"](https://en.wikipedia.org/wiki/Universally_unique_identifier#Format)). So in your example you incorrectly selected the _highest bits_ of the timestamp: instead of 16 you need to select **12** bits (i.e. select not 4, but 3 HEX digits). The correct selection of the _highest_ timestamp portion (from the _third chunk_ of the UUID) is `SUBSTRING(UUID(), 16, 3)` and the result will then be `1e1-8f00-45f9b8d6`. – informatik01 Jan 16 '21 at 02:52
  • The **correct** example with **thorough explanations** about how to extract timestamp from UUIDs can be found in the following article: [Extracting timestamp and MAC address from UUIDs](https://rpbouman.blogspot.com/2014/06/mysql-extracting-timstamp-and-mac.html). – informatik01 Feb 04 '21 at 19:26
2

Yes if you don't do two or more inserts or edits during one second. Only problem is that a lot stuff can be done during a second, i.e. multiple inserts or automatic updates using a where clause. That rules out the simple solution to force unique timestamps: to add unique constraint into timestamp column.

Why should a timestamp be unique? Use auto increment or something else if you need unique index etc.

If you need more precise time values than timestamp, see:

Community
  • 1
  • 1
ZZ-bb
  • 2,157
  • 1
  • 24
  • 33
  • thanks for the descriptive answer. Auto increment won't work for my problem. Since an update query executed. Then I can't detect the order of the records have changed. That's why I tried to use a TIMESTAMP. – NULL_USER Apr 25 '12 at 17:43
  • @Ashan When inserting records `AI` reweals the order. But editing is a different story. Why edit order matters? I believe the main function of `timestamp` is to mark the time when changes made into a row so you know that the row has or has not changed after insert. Not to reveal the order of changes. Storing `microtime` into some column could be an answer but I don't think there's any guarantee that even that value is always unique. You could insert `date` or `timestamp` + some sort of count value of current date's edits. But then again, how to keep track of the edits? – ZZ-bb Apr 25 '12 at 17:54