How to Ensure a Timestamp Is Always Unique

How to ensure a timestamp is always unique?

One way to get a strictly ascending sequence of timestamps with no duplicates is the following code.

Compared to the other answers here this one has the following benefits:

1. The values track closely with actual real-time values (except in extreme circumstances with very high request rates when they would get slightly ahead of real-time).
2. It's lock free and should perform better that the solutions using lock statements.
3. It guarantees ascending order (simply appending a looping a counter does not).

public class HiResDateTime
{
   private static long lastTimeStamp = DateTime.UtcNow.Ticks;
   public static long UtcNowTicks
   {
       get
       {
           long original, newValue;
           do
           {
               original = lastTimeStamp;
               long now = DateTime.UtcNow.Ticks;
               newValue = Math.Max(now, original + 1);
           } while (Interlocked.CompareExchange
                        (ref lastTimeStamp, newValue, original) != original);

           return newValue;
       }
   }
}

Also note the comment below that original = Interlocked.Read(ref lastTimestamp); should be used since 64-bit read operations are not atomic on 32-bit systems.

Is a timestamp in microseconds always unique?

Microsecond based ids are only guaranteed to be unique within limits. A single threaded scripts on a single computer is probably pretty safe in this regard. However, as soon as you start talking about parallel execution, be that simply on multiple CPUs within the same machine or especially across multiple machines, all bets are off.

So it depends on what you want to use this id for. If you're just using it to generate an id which is used only within the same script, it's probably safe enough. For example:

<?php $randomId = uniqid(); ?>
<div id="<?php echo $randomId; ?>"></div>
<script>
    var div = document.getElementById('<?php echo $randomId; ?>');
    ...
</script>

You very likely won't encounter any problems here with this limited use.

However, if you start generating file names using uniqid or other such uses which are shared with other external scripts, I wouldn't rely on it. For filenames, using a hash based on the file contents may be a good idea. For general purpose decentralised randomly generated ids, UUIDs are a good fit (because they've been designed for this purpose).

Is TimeStamp column unique?

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

---

For more info, check THIS article.

Timestamp as unique ID

If you use

System.currentTimeMillis()/1000

then if you save 2 files in the same second you will have 2 identical names for 2 different files.

So i suggest you to use

UUID.randomUUID().toString()

Creating a unique timestamp in Java

This will give a time as close the current time as possible without duplicates.

private static final AtomicLong LAST_TIME_MS = new AtomicLong();
public static long uniqueCurrentTimeMS() {
    long now = System.currentTimeMillis();
    while(true) {
        long lastTime = LAST_TIME_MS.get();
        if (lastTime >= now)
            now = lastTime+1;
        if (LAST_TIME_MS.compareAndSet(lastTime, now))
            return now;
    }
}

One way to avoid the limitation of one id per milli-second is to use a micro-second timestamp. i.e. multiply currentTimeMS by 1000. This will allow 1000 ids per milli-second.

Note: if time goes backwards, eg due to an NTP correction, the time will just progress at 1 milli-second per invocation until time catches up. ;)

Is row created timestamp is unique in a table?

No. Normally, you cannot be sure that there are no two rows that come in exactly the same time.

It would be more critical if you are using transactions where all rows are committed at the same time. This fiddle shows that the timestamps are equals for two rows: demo: db<>fiddle

Maybe you could use a combination of two columns as

PRIMARY KEY (order_number, current_timestamp)

But since you are not ensuring the uniqueness of each of the columns (or the combination) you would never be safe.

---

It is always better to add a column with type serial which is an automatically increasing (bigint)number.

Since Postgres 10 you could use the GENERATE IDENTITY instead of serial type:

id INT GENERATED ALWAYS AS IDENTITY

(http://www.postgresqltutorial.com/postgresql-identity-column/)

Javascript timestamp number is not unique

If you need uniqueness, use Math.random(), and not any of the Date() APIs.

Math.random returns an integer between and including 0 and 1. If you really want an semi-unique number based on the current time, you can combine the Date API with Math.random. For example:

var id = new Date().getTime() + Math.random();

In modern browsers, you can also use performance.now(). This API guarantees that every new call's return value is unique.

---

Related Topics