In PostgreSQL, I use the now()
and current_timestamp
function and I see no difference:
# SELECT now(), current_timestamp;
now | now
--------------------------------+--------------------------------
04/20/2014 19:44:27.215557 EDT | 04/20/2014 19:44:27.215557 EDT
(1 row)
Am I missing something?
Best Answer
There is no difference. Three quotes from the manual:
1)
2)
3)
Bold emphasis mine.
CURRENT_TIMESTAMP
,transaction_timestamp()
andnow()
do exactly the same.CURRENT_TIMESTAMP
is a syntactical oddity for a function, having no trailing pair of parentheses. That's according to the SQL standard.If you don't declare a column alias for a function call in an SQL statement, the alias defaults to the name of the function. Internally, the standard-SQL
CURRENT_TIMESTAMP
is implemented withnow()
. Up to Postgres 9.6 that shows in the resulting column name, which was "now", but changed to "current_timestamp" in Postgres 10.transaction_timestamp()
does the same, but this one is a proper Postgres function, so the default alias has always been "transaction_timestamp".Do not confuse either of these functions with the special input constant
'now'
. That's just one of several notational shorthands for specific date/time/timestamp values, quoting the manual:It may add to the confusion that (up to at least Postgres 12) any number of leading and trailing spaces and brackets (
{[( )]}
) are trimmed from those special input values. So'now()'::timestamptz
- or just'now()'
where no explicit type cast is required - is also valid and happens to evaluate to the same timestamp as the functionnow()
in most contexts. But those are constants and typically not what you want as column default for instance.db<>fiddle here
Old SQL fiddle
Notable alternatives are
statement_timestamp()
andclock_timestamp()
. The manual:Note:
statement_timestamp()
isSTABLE
as the above (always returns the same value within the same SQL command). Butclock_timestamp()
necessarily is onlyVOLATILE
. The difference may be significant.