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.
- Creating date-time values
- Manipulating date-time values
- Current date-time moment
- Delaying execution
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|
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|
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.
-- 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