CODEWITHSUNDEEP
postgresqldatetime
TheWildHealer
TheWildHealer

Reputation: 1616

Is there a difference between PostgreSQL now() and transaction_timestamp() functions?

In the official documentation, both functions have the same description:

Current date and time (start of current transaction)

Is there a difference between the two functions, and if not, why do both exist? Thanks.

Upvotes: 4

Views: 6094

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28253

now and transaction_timestamp are equivalent to the SQL standard current_timestamp. All report the start time of the transaction.

In terms of transactions, there are two timestamps to think of. the start of the transaction & the time each individual statement is executed. Conceptually, there is also the end time of the transaction which one can get by running a select statement_timestamp() trx_end_timestamp at the very end, just before the commit / rollback.

If you run the following in psql [copy the whole line & paste into psql shell]

BEGIN; SELECT PG_SLEEP(5); SELECT NOW(), CURRENT_TIMESTAMP, TRANSACTION_TIMESTAMP(), STATEMENT_TIMESTAMP(); COMMIT;

I got this output:

              now              |       current_timestamp       |     transaction_timestamp     |      statement_timestamp
-------------------------------+-------------------------------+-------------------------------+-------------------------------
 2019-04-23 11:15:18.676855-04 | 2019-04-23 11:15:18.676855-04 | 2019-04-23 11:15:18.676855-04 | 2019-04-23 11:15:23.713275-04
(1 row)

You can see clearly that NOW, CURRENT_TIMESTAMP, TRANSACTION_TIMESTAMP are equivalent, and STATEMENT_TIMESTAMP has a 5 second offset because we slept for 5 seconds.

BTW, CURRENT_TIMESTAMP is in the sql standard. The others are postgresql specific, though other databases may also implement them

Upvotes: 7

JGH
JGH

Reputation: 17846

The answer is in the doc your mention:

now() is a traditional PostgreSQL equivalent to transaction_timestamp().

So, they are the same, and they are here for historical / backward compatibility, and some could argue for the simplicity of the function name.

Upvotes: 2

Related Questions