28.8.2023

Difference between current_timestamp, now() and clock_timestamp() in postgresql

There are multiple methods to detect the current time in postgresql. Two of those methods are:

  1. Use the value current_timestamp
  2. Use the function now()
  3. Use the function clock_time()
INSERT INTO person (id, name, created_at) VALUES ('33333333-3333-3333-3333-333333333005', 'Max' , current_timestamp);
INSERT INTO person (id, name, created_at) VALUES ('33333333-3333-3333-3333-333333333005', 'Max' , now());
INSERT INTO person (id, name, created_at) VALUES ('33333333-3333-3333-3333-333333333005', 'Max' , clock_timestamp());

While all of these will insert a timestamp they work a little bit different.

  1. The first is a value defined in Standard SQL, you can find more standard values in the postgresql manual.
  2. The second and third are both postgresql functions The major difference is now() is adding the time at the beginning of the transactions, while, clock_timestamp() insert the time at the execution of the statement.
Jörg

CEO

LinkedIn
Zur Übersicht

Standort Hannover

newcubator GmbH
Bödekerstraße 22
30161 Hannover

Standort Dortmund

newcubator GmbH
Westenhellweg 85-89
44137 Dortmund