date functions

How Now() Works in PostgreSQL

by

In PostgreSQL, the now() function returns the current date and time (including the time zone offset), at the start of the current transaction.

It’s the equivalent of the transaction_timestamp() function.

It’s also similar to the current_timestamp function (when called without an argument).

The now() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.

Also, now() is non-SQL-standard (as is the transaction_timestamp() function).

Read more

How timeofday() Works in PostgreSQL

by

In PostgreSQL, timeofday() is a non-SQL-standard time function that returns the current date and time, with the time zone abbreviation.

It’s similar to the clock_timestamp() function, except that it returns its result as a formatted text string rather than a timestamp with time zone value. 

The result of both functions changes throughout the execution of a statement. Therefore, you could get a different result in different parts of the statement if you call the functions multiple times within a single statement.

Read more

How to_timestamp() Works in PostgreSQL

by

According to the PostgreSQL documentation, there are two functions called to_timestamp():

  • One converts the Unix epoch to a timestamp. The Unix epoch is the number of seconds since 1970-01-01 00:00:00+00.
  • The other converts a string to a timestamp.

More specifically, each function converts the value to a timestamp with time zone value.

Although the Postgres documentation presents them as two separate to_timestamp() functions, I present them as if they’re one function that accepts either one argument, or two.

Read more