Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

There is no difference. Three quotes from the documentation:

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.

So, basically, 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 (where one is required), the alias defaults to the name of the function. Internally, the standard-SQL CURRENT_TIMESTAMP is implemented with now(), and that shows when you look at the default column alias.

transaction_timestamp() does the same, but this one is a proper Postgres function, so the default alias transaction_timestamp is assigned.

SQL Fiddle.

Careful when working with JDBC (like SQL Fiddle does). Unlike psql or pgAdmin it can't handle multiple columns with the same name in the result set and only returns the first one for each set ...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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