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.

Syntax

The syntax goes like this:

timeofday()

So it doesn’t accept any parameters.

Example

Here’s a basic example to demonstrate.

SELECT timeofday();

Result:

Thu Jul 02 10:00:27.068776 2020 AEST

Multiple Calls

Here’s a basic example to demonstrate how the results can differ when you call the function multiple times within a single SQL statement.

\x
SELECT 
  timeofday(),
  pg_sleep(5),
  timeofday(),
  pg_sleep(3),
  timeofday();

Result (using vertical output):

timeofday | Thu Jul 02 10:02:23.060770 2020 AEST
pg_sleep  | 
timeofday | Thu Jul 02 10:02:28.131195 2020 AEST
pg_sleep  | 
timeofday | Thu Jul 02 10:02:31.192749 2020 AEST

Here, I used the pg_sleep() function to delay execution for several seconds. The first call delays execution for 5 seconds and the second call delays execution for 3 seconds.

We can see that each time timeofday() was called, the actual time was slightly different. This is mostly due to the pg_sleep() function, however it could also be slightly different without it, depending on how fast the query runs.

Here it is again without the pg_sleep() calls.

SELECT 
  timeofday(),
  timeofday(),
  timeofday();

Result (using vertical output):

timeofday | Thu Jul 02 10:03:26.044065 2020 AEST
timeofday | Thu Jul 02 10:03:26.044076 2020 AEST
timeofday | Thu Jul 02 10:03:26.044080 2020 AEST

These examples use vertical output (usually called expanded display in psql) to make it easier to read the results.

You can toggle expanded display in psql with \x.