44

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?

53

There is no difference. Three quotes from the manual:

1)

These SQL-standard functions all return values based on the start time of the current transaction:
...
CURRENT_TIMESTAMP
...

2)

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns.

3)

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

Bold emphasis mine. CURRENT_TIMESTAMP, transaction_timestamp() and now() 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 with now(). 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:

... that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.

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 function now() 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() and clock_timestamp(). The manual:

statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). [...]
clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

Note: statement_timestamp() is STABLE as the above (always returns the same value within the same SQL command). But clock_timestamp() necessarily is only VOLATILE. The difference may be significant.

  • but, does it make a difference for query optimization? will now() be executed for every row in: where items.createddate > now() ? – santiago arizti Feb 21 '17 at 0:20
  • 3
    @santiagoarizti: No. now() is defined STABLE because it evaluates to the same value (the start time of the current transaction) within the same transaction. I your example, now() is executed once only (as opposed to clock_timestamp() for instance). – Erwin Brandstetter Feb 21 '17 at 9:26
3

In addition that they have no functional difference when you use them properly, they are casted differently:

'now()' recongnized (just like 'today' or 'now'):

b=# select 'now()'::timestamptz;
          timestamptz
-------------------------------
 2016-12-09 16:31:35.942243+00
(1 row)

'CURRENT_TIMESTAMP' gives funny error from dark edges

Note: As of PostgreSQL version 7.2, 'current' is no longer supported as a date/time constant

b=# select 'CURRENT_TIMESTAMP'::timestamptz;
ERROR:  date/time value "current" is no longer supported
LINE 1: select 'CURRENT_TIMESTAMP'::timestamptz;
               ^

and 'transaction_timestamp()' is just not recongized as timestamp with tz value:

b=# select 'transaction_timestamp()'::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: "transaction_timestamp()"
LINE 1: select 'transaction_timestamp()'::timestamptz;
               ^

Please don't ask why would you cast 'now()' as timestamp. I've seen where timestamp_column = 'now()' instead of where timestamp_column = now() in people code, so thought this clarification would be funny fact and good addition to Erwin's answer.

  • This is a misunderstanding. The input string 'now()' looks similar to the function now() on the surface, but is not directly related otherwise. 'now' is a constant evaluating to the current transaction's start time. Trailing parens are ignored. The attempt to cast the strings 'CURRENT_TIMESTAMP' or 'transaction_timestamp()' to timestamp in similar fashion fails, because that's just nonsense. Neither is related to the corresponding functions. – Erwin Brandstetter Feb 2 '18 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.