General-purpose date and time functions [YSQL]

General-purpose date and time functions

This page lists all of the general-purpose date-time functions. They are classified into groups according to the purpose.

Notice that the so-called date-time formatting functions, like:

  • to_date() or to_timestamp(), that convert a text value to a date-time value

  • and to char(), that converts a date-time value to a text value

are described in the dedicated Date and time formatting functions section.

Functions without trailing parentheses

Normally in PostgreSQL, and therefore in YSQL, a function invocation must be written with trailing parentheses—even when the invocation doesn't specify any actual arguments. These five date-time functions are exceptions to that rule:

  • current_date, current_time, current_timestamp, localtime, and localtimestamp.

Notice that the \df meta-command produces no output for each of these five functions.

Each of these is in the group functions that return the current date-time moment. If you invoke one of these using empty trailing parentheses, then you get the generic 42601 syntax error. Each of these five names is reserved in SQL. For example, if you try to create a table with a column whose name is one of these five (without trailing parentheses in this case, of course), then you get the same 42601 error. Notice that within this set of five exceptional functions that must not be invoked with empty trailing parentheses, these four have a variant that has a single precision parameter: current_time(precision), current_timestamp(precision), localtime(precision), and localtimestamp(precision). This specifies the precision of the seconds value. (This explains why current_date has no precision variant.)

All of the other date-time functions that this page lists must be written with trailing parentheses—conforming to the norm for function invocation. (Without trailing parentheses, it is taken as a name for a column in a user-created table or for a variable in PL/pgSQL.

You should regard the exceptional status of the current_date, current_time, current_timestamp, localtime, and localtimestamp date-time functions simply as a quirk. There are other such quirky functions. See this note in the section 9.25. System Information Functions in the PostgreSQL documentation:

current_catalog, current_role, current_schema, current_user, session_user, and user have special syntactic status [in the SQL Standard]: they must be called without trailing parentheses. In PostgreSQL, parentheses can optionally be used with current_schema, but not with the others.

The following tables list all of the general purpose date_time built-in functions, classified by purpose.

Functions for creating date-time values

return data type
make_date() date
make_time() (plain) time
make_timestamp() (plain) timestamp
make_timestamptz() timestamptz
to_timestamp() timestamptz
make_interval() interval

Functions for manipulating date-time values

return data type
date_trunc() plain timestamp | timestamptz | interval
justify_days() | justify_hours() | justify_interval() interval

Functions that return the current date-time moment

There are several built-in SQL functions for returning the current date-time moment because there are different notions of currency:

  • right now at the instant of reading, independently of statements and transactions;
  • as of the start of the current individual SQL statement within an on-going transaction;
  • as of the start of the current transaction.
return data type Moment kind
current_date date start of transaction
localtime time start of transaction
current_time timetz start of transaction
localtimestamp plain timestamp start of transaction
transaction_timestamp() | now() | current_timestamp timestamptz start of transaction
statement_timestamp() timestamptz start of statement
clock_timestamp() timestamptz instantaneous
timeofday() text instantaneous

Notice that timeofday() has the identical effect to to_char(clock_timestamp(),'Dy Mon dd hh24:mi:ss.us yyyy TZ'). But notice that the use of plain 'Dy' and plain 'Mon', rather than 'TMDy' and 'TMMon', calls specifically for the English abbreviations—in other words, timeofday() non-negotiably returns an English text value.

Try this:

-- Because "fmt" uses the plain forms "Dy" and "Mon", the test is insensitve to the value of "lc_time".
-- Setting it here to Finnish simply emphasizes this point.
set lc_time = 'fi_FI';

set timezone = 'America/Los_Angeles';

drop procedure if exists assert_timeofday_semantics() cascade;
create procedure assert_timeofday_semantics()
  language plpgsql
as $body$
declare
  clk_1         timestamptz not null := clock_timestamp();
  clk_2         timestamptz not null := clk_1;

  tod_1         text        not null := '';
  tod_2         text        not null := '';
  dummy         text        not null := '';
  fmt constant  text        not null := 'Dy Mon dd hh24:mi:ss.us yyyy TZ';
begin
  select
    clock_timestamp(), timeofday(), pg_sleep(2), clock_timestamp(), timeofday() into
    clk_1,             tod_1,       dummy,       clk_2,             tod_2;

  assert tod_1 = to_char(clk_1, fmt), 'Assert #1 failed';
  assert tod_2 = to_char(clk_2, fmt), 'Assert #2 failed';
end;
$body$;

call assert_timeofday_semantics();

Presumably, because it takes time to execute each individual PL/pgSQL statement, the moment values returned by the first calls to clock_timestamp() and timeofday(), and then by the second calls to these two functions, will not be pairwise identical. However, they are the same to within a one microsecond precision. This is fortunate because it does away with the need to implement a tolerance notion and therefore simplifies the design of the test.

Don't use 'timeofday()'.

Using clock_timestamp(), and formatting the result to text, can bring the identical result to using timeofday()—if this meets your requirement. However, you might well want a different formatting notion and might want to render day and month names or abbreviations in a language other than English. Moreover, you might want to do arithmetic with the moment value, for example by subtracting it from some other moment value. Yugabyte recommends, therefore, that you simply avoid ever using timeofday() and, rather, always start with clock_timestamp().

For this reason, this section won't say any more about the timeofday() builtin function.

Functions for delaying execution

return data type
pg_sleep() void
pg_sleep_for() void
pg_sleep_until() void

Miscellaneous functions

return data type
isfinite() boolean
age() interval
extract() | date_part() double-precision
timezone() | at time zone operator time | timetz | timestamp | timestamptz
overlaps operator boolean