PostgreSQL – Difference Between Date Extraction Methods

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

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.