PostgreSQL — When To Use clock_timestamp(), current_timestamp and Unique Timestamps

PostgreSQL offers multiple ways to get the current time of a database. These are similar in intent but differ in usage. It’s possible to generate a unique timestamp, but we’re not sure when you would use it.

Photo by Aron Visuals.

The Opinionated TLDR

  • Use CLOCK_TIMESTAMP() when non-SQL standard functions are acceptable. Note that CURRENT_TIMESTAMP is about 1.28x faster. See Current Date/Time.
  • Using CURRENT_TIMESTAMP when counting the approximate number of records changed in a transaction is required noting that a call to CURRENT_TIMESTAMP does not assure a unique timestamp.
  • Favor using the timestamptz over the timestamp type. See “Don’t use timestamp (without timezone).”
  • It’s possible to generate a unique timestamp, but most likely not necessary.

Introduction

CLOCK_TIMESTAMP() and CURRENT_TIMESTAMP differ as follows (see Current Date/Time for details):

  • CLOCK_TIMESTAMP() — A non-SQL standard function that returns the start time of the current statement.
  • CURRENT_TIMESTAMP — A SQL standard function that returns a value based on the start time of the current transaction.

We’ll consider when to use CLOCK_TIMESTAMP() and when to use CURRENT_TIMESTAMP. First, let’s see an example usage:

Example Usage

Running the following SQL DDL:

SELECT CLOCK_TIMESTAMP(), CURRENT_TIMESTAMP
FROM generate_series(1,10);

Results in:

clock_timestamp               | current_timestamp
-------------------------------------------------------------
2022-09-09 17:34:42.960276+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960297+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960298+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960300+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960301+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960301+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960302+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960302+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960303+00 | 2022-09-09 17:34:42.959754+00
2022-09-09 17:34:42.960304+00 | 2022-09-09 17:34:42.959754+00

The timestamp value returned by CLOCK_TIMESTAMP is increasing ever so slightly. The CURRENT_TIMESTAMP value does not change.

Note that two values are not unique in the CLOCK_TIMESTAMP column!!! We CAN NOT rely on CLOCK_TIMESTAMP( ) being unique: especially on faster databases.

When to Use CLOCK_TIMESTAMP()

Use CLOCK_TIMESTAMP() when:

  • You want granular time stamps. A granular timestamp is useful for features like ingesting, where you select all the records since the last ingest time.
  • For columns like updated_at, created_at, modified_at, etc.
  • You want to know the closest sql statement execution time.

NOTE: Don’t rely on CLOCK_TIMESTAMP() returning unique timestamp values.

When to Use CURRENT_TIMESTAMP

Use CURRENT_TIMESTAMP when:

  • You’re interested in the approximate number of records changed during a given transaction. We say approximately because two transactions could potentially have the same timestamp. Note: Use a transaction_id column of type UUID if you need to get the exact number of records updated in a given transaction.
  • Speed is of the utmost importance. CLOCK_TIMESTAMP() takes about 6% longer to call than CURRENT_TIMESTAMP.

NOTE: Don’t rely on CURRENT_TIMESTAMP returning unique timestamp values. Two different transactions can end up with the same CURRENT_TIMESTAMP.

CLOCK_TIMESTAMP() vs CURRENT_TIMESTAMP Execution Time

See benchmark gist for benchmarking code.

Running the benchmark:

DO $$
DECLARE d_exec_count int = 20;
DECLARE d_row_count int = 3e6;
BEGIN
CALL benchmark.execute(
'current_timestamp',
'clock_timestamp',
d_row_count,
FORMAT(
'SELECT CURRENT_TIMESTAMP
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT CLOCK_TIMESTAMP()
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
);
END;
$$;

Results of running the above code three separate times:

left      -   right   | Times  | left (micro-sec)| right (micro-sec)
--------------------------------------------------------------------
curr_ts - clock_ts | 1.28x | 0.1224 ± 0.0002 | 0.1562 ± 0.0006
curr_ts - clock_ts | 1.28x | 0.1225 ± 0.0003 | 0.1569 ± 0.0022
curr_ts - clock_ts | 1.28x | 0.1226 ± 0.0002 | 0.1564 ± 0.0010

Note:

  • Benchmarks Truncated (upper/lower 10th percentile).
  • Bold/Italic items had the fastest execution time. If neither is bolded, then execution times are considered equivalent.

Interestingly, we also see a performance cost using CLOCK_TIMESTAMP() with CURRENT_TIMESTAMP being 1.28x faster; which makes sense as PostgreSQL probably needs to make a function call for each row (each sql statement).

Can We Get a Unique Timestamp

We can’t rely on CLOCK_TIMESTAMP() or CURRENT_TIMESTAMP to return a unique timestamp.

However, generating a unique timestamp that is accurate to one second is possible using a SEQUENCE in conjunction with CLOCK_TIMESTAMP().

CREATE SCHEMA IF NOT EXISTS perf;-- NOTE: There may be faster ways to make a timestamp.
CREATE OR REPLACE FUNCTION perf.unique_timestamp (
p_sequence_name varchar
) RETURNS timestamptz LANGUAGE plpgsql AS
$$
DECLARE d_ct timestamptz = CLOCK_TIMESTAMP();
BEGIN
RETURN make_timestamptz(
date_part('year', d_ct)::int,
date_part('month', d_ct)::int,
date_part('day', d_ct)::int,
date_part('hour', d_ct)::int,
date_part('minute', d_ct)::int,
FLOOR(date_part('seconds', d_ct))
+ MOD(nextval(p_sequence_name),1000*1000) /
(1000*1000::double precision)
);
END;
$$;
COMMENT ON FUNCTION perf.unique_timestamp IS
'Generate a unique timestamp leveraging a sequence.';

NOTE: This approach does not work if we insert more than a million records a second into a given table. We can also increase the resolution of the timestamp at the cost of inserts per second.

Used as follows:

CREATE SEQUENCE IF NOT EXISTS
perf.unique_at_seq AS int INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS perf.unique_at (
unique_at_id timestamptz NOT NULL PRIMARY KEY
DEFAULT perf.unique_timestamp('perf.unique_at_seq')
);

Testing it:

INSERT INTO perf.unique_at
SELECT perf.unique_timestamp('perf.unique_at_seq')
FROM generate_series(1,1000000);
SELECT *
FROM perf.unique_at
LIMIT 10;

Unique Timestamp Benchmarking

See benchmark gist for benchmarking code.

Running the benchmark:

DO $$
DECLARE d_exec_count int = 20;
DECLARE d_row_count int = 3e6;
BEGIN
CALL benchmark.execute(
'current_timestamp',
'unique_timestamp',
d_row_count,
FORMAT(
'SELECT CURRENT_TIMESTAMP
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT perf.unique_timestamp(''perf.unique_at_seq'')
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
);
CALL benchmark.execute(
'clock_timestamp',
'unique_timestamp',
d_row_count,
FORMAT(
'SELECT CLOCK_TIMESTAMP()
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
FORMAT(
'SELECT perf.unique_timestamp(''perf.unique_at_seq'')
FROM generate_series(%s,%s) AS series;', 1, d_row_count
),
d_exec_count
);
END;
$$;

Results of running the above code three separate times:

left      -   right   | Times  | left (micro-sec)| right (micro-sec)
--------------------------------------------------------------------
curr_ts - uniqe_ts | 12.4x | 0.1212 ± 0.0003 | 1.5065 ± 0.0047
clock_ts - uniqe_ts | 9.64x | 0.1558 ± 0.0006 | 1.5018 ± 0.0028

Note:

  • Benchmarks Truncated (upper/lower 10th percentile).
  • Bold/Italic items had the fastest execution time. If neither is bolded, then execution times are considered equivalent.

CLOCK_TIMESTAMP() is about 9.64x faster than perf.unique_timestamp.

When to Use a Unique Timestamp?

We’re not quite sure when to use a unique timestamp. Some thoughts are:

  • You would like to save storage space for a log by using a timestamp as a primary key. However, you could use a UUID v1 which is both unique and has a timestamp that can be extracted (see extract timestamp from v1 UUID in PostgreSQL).

If you have any ideas on when to use a unique timestamp, please leave a comment.

Conclusion

We’ve gone over different ways in PostgreSQL to get the current timestamp and considered a way to generate a unique timestamp for a given table.

There are many articles on the differences between a CLOCK_TIMESTAMP() and CURRENT_TIMESTAMP. What we try to do with The Opinionated Architect is provide examples of when to use a particular feature of PostgreSQL. We hope we have done that with this article.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Eric Hosick

Eric Hosick

74 Followers

Creator, entrepreneur, software architect, software engineer, lecturer, and technologist.