Functions that return the current date-time moment

Each of the nine functions in this group returns a moment value of the specified data type that honors the "moment kind" semantics. (See the note "Don't use "timeofday()" above. For the reasons that it explains, that function is not included in the count of ten that the table in the subsection Functions that return the current date-time moment presents. This is why the present paragraph starts with "Each of the nine functions...")

Except for the variants current_time(precision), current_timestamp(precision), localtime(precision), and localtimestamp(precision), the functions have no formal parameters. The note Functions without trailing parentheses, at the start of the parent page, lists these four together with current_date (which has no optional precision parameter) and calls out their exceptional status. There is no need, therefore, to document each "current date-time moment" function individually.

The following anonymous PL/pgSQL block tests that the return data types are as expected.

do $body$
begin
  assert
    -- date
        pg_typeof(current_date)            ::text = 'date'                        -- start of transaction

    -- plain time
    and pg_typeof(localtime)               ::text = 'time without time zone'      -- start of transaction

    -- timetz
    and pg_typeof(current_time)            ::text = 'time with time zone'         -- start of transaction

    -- plain timestamp
    and pg_typeof(localtimestamp)          ::text = 'timestamp without time zone' -- start of transaction

    -- timestamptz
    and pg_typeof(transaction_timestamp()) ::text = 'timestamp with time zone'    -- start of transaction
    and pg_typeof(now())                   ::text = 'timestamp with time zone'    -- start of transaction
    and pg_typeof(current_timestamp)       ::text = 'timestamp with time zone'    -- start of transaction
    and pg_typeof(statement_timestamp())   ::text = 'timestamp with time zone'    -- start of statement
    and pg_typeof(clock_timestamp())       ::text = 'timestamp with time zone'    -- instantaneous
    ,
  'assert failed';
end;
$body$;

It finishes without error showing that all the assertions hold.

There are five moment data types. (This number includes timetz. But the PostgreSQL documentation, and therefore the YSQL documentation, recommend against using this. See this note on the "Date and time data types" major section's main page.) So, with three kinds of moment, you might expect every cell in the implied five-by-three matrix to be filled with a differently named function. However, there are only nine differently named functions (not counting timeofday()). This reflects two facts:

• Only the functions that return a timestamptz value have "start of statement" and "instantaneous" variants.

• There are three differently named "start of transaction" functions that return a timestamptz value: transaction_timestamp(), now(), and current_timestamp.

The semantics of "start of transaction", "start of statement", and "instantaneous"

• "Start of transaction" means literally this. The function transaction_timestamp() implements this semantics. It returns the same value on successive invocations within the same transaction.

• "Start of statement" means literally this. The function statement_timestamp() implements this semantics. It returns the same value on successive invocations only within the same SQL statement. Invocations in successive different SQL statements within the same transaction will report different values for each statement.

• "Instantaneous" means what a wall clock reads at the instant of invocation irrespective of progress during an explicitly started transaction and during individual SQL statement execution. The function clock_timestamp() implements this semantics.

Notice that applications typically run with "autocommit" set to "on". In the rare case that a single SQL statement is issued in this mode without surrounding transaction control calls, then invocations of transaction_timestamp() and statement_timestamp() will return the same value. Try this:

-- self-doc
\set AUTOCOMMIT 'on'

select
  ''''''''||transaction_timestamp() ::text||''''''''||'::timestamptz' as txn,
  ''''''''||statement_timestamp()   ::text||''''''''||'::timestamptz' as stm
\gset r_

select
  (
    extract(epoch from :r_stm) =
    extract(epoch from :r_txn)
  )::text as "txn and stmt times are identical";

The result is true.

Usually, when an application makes only pure SQL calls (and no PL/pgSQL calls), business transaction atomicity is implemented like this:

• Execute the set transaction statement before executing two or several data-changing calls.
• Then execute commit (or rollback if an error occurs) after these data-changing calls.

In this case, there is a clear distinction between the "start of transaction" moment and subsequent "start of statement" moments. Try this:

-- self-doc
\set AUTOCOMMIT 'on'

start transaction;

\! sleep 5

select
  ''''''''||transaction_timestamp() ::text||''''''''||'::timestamptz' as txn,
  ''''''''||statement_timestamp()   ::text||''''''''||'::timestamptz' as stm
\gset r1_

\! sleep 5

select
  ''''''''||transaction_timestamp() ::text||''''''''||'::timestamptz' as txn,
  ''''''''||statement_timestamp()   ::text||''''''''||'::timestamptz' as stm
\gset r2_

commit;

Confirm that transaction_timestamp() returns the same value throughout the transaction:

select
  (
    extract(epoch from :r2_txn) =
    extract(epoch from :r2_txn)
  )::text as "txn times are identical";

The result is true.

Confirm that the first invocation of statement_timestamp() returns a value that's about 5 seconds (the sleep time) after the value that transaction_timestamp() returns:

select to_char(
  (
    extract(epoch from :r1_stm) -
    extract(epoch from :r1_txn)
  ),
  '9.999') as "stmt #1 time minus txn time";

You'll see that "stmt #1 time minus txn time" differs from 5.0 seconds by maybe as much as 0.03 seconds. This reflects the inevitable time that it takes for the client-server round trips and for the \! meta-command to launch a new shell and invoke the operating system sleep utility. (psql and therefore ysqlsh have no intrinsic sleep function. And if you use pg_sleep(), then you complicate the discussion because this requires a top-level SQL call of its own.)

Notice that this observation confirms that it is the start transaction explicit call that determines the moment at which the transaction starts—and not the start moment of the first subsequent database call within that started transaction.

Confirm that the second invocation of statement_timestamp() returns a value that's about 10 seconds (the sum of the two sleep times) after the value that transaction_timestamp() returns:

select to_char(
  (
    extract(epoch from :r2_stm) -
    extract(epoch from :r1_txn)
  ),
  '99.999') as "stmt #2 time minus txn time";

You'll see "stmt #2 time minus txn time" differs from 10.0 seconds by maybe as much as 0.03 seconds.

Now that the difference in semantics between transaction_timestamp() and statement_timestamp() is clear, it's sufficient to demonstrate the difference in semantics between statement_timestamp() and clock_timestamp() within a single statement. Try this:

-- self-doc
\set AUTOCOMMIT 'on'

select
  ''''''''||statement_timestamp()   ::text||''''''''||'::timestamptz' as stm_1,
  ''''''''||clock_timestamp()       ::text||''''''''||'::timestamptz' as clk_1,
  ''''''''||pg_sleep(5)             ::text||''''''''||'::timestamptz' as dummy,
  ''''''''||statement_timestamp()   ::text||''''''''||'::timestamptz' as stm_2,
  ''''''''||clock_timestamp()       ::text||''''''''||'::timestamptz' as clk_2
\gset r_

select
  (
    extract(epoch from :r_stm_1) =
    extract(epoch from :r_stm_1)
  )::text as "stm_1 time and stm_2 times are identical";

The result is true.

Now check how the values returned by clock_timestamp() differ as the statement executes:

select to_char(
  (
    extract(epoch from :r_clk_2) -
    extract(epoch from :r_clk_1)
  ),
  '9.999') as "clk_2 time minus clk_1 time";

You'll see that "clk_2 time minus clk_1 time" differs from 5.0 seconds by maybe just a couple of milliseconds. The smaller noise discrepancies in this test than in the previous tests reflect the fact that all the measurements are made within the server-side execution of a single top-level call.

select (now() = 'now'::timestamptz)::text as "now() = 'now'";

deallocate all;
prepare stmt as
select to_char(
  (
    extract(epoch from now()) -
    extract(epoch from 'now'::timestamptz)
  ),
  '99.999') as "now() [immediate] - 'now' [at prepare time]";

\! sleep 5
execute stmt;

\! sleep 5
execute stmt;

drop table if exists t cascade;
create table t(k int primary key, t_at_table_creation_time timestamptz default 'now', t_at_insert_time timestamptz default now());
\! sleep 5
insert into t(k) values(1);

select to_char(
  (
    extract(epoch from t_at_insert_time) -
    extract(epoch from t_at_table_creation_time)
  ),
  '99.999') as "t_at_insert_time - t_at_table_creation_time"
from t
where k = 1;

Consider user-defined functions rather than 'today', 'tomorrow', and 'yesterday'

(You can simply use the built-in function now() rather than the special constant 'now'.)

The PostgreSQL documentation defines the meanings of these three constants thus:

• 'today': midnight (00:00) today

• 'tomorrow': midnight (00:00) tomorrow

• 'yesterday': midnight (00:00) yesterday

Consider these alternatives for the special constants:

-- 'today'
drop function if exists today() cascade;
create function today()
  returns timestamptz
  volatile
  language plpgsql
as $body$
begin
  return date_trunc('day', clock_timestamp());
end;
$body$;

-- 'tomorrow'
drop function if exists tomorrow() cascade;
create function tomorrow()
  returns timestamptz
  volatile
  language plpgsql
as $body$
begin
  return today() + make_interval(days=>1);
end;
$body$;

-- 'yesterday'
drop function if exists yesterday() cascade;
create function yesterday()
  returns timestamptz
  volatile
  language plpgsql
as $body$
begin
  return today() - make_interval(days=>1);
end;
$body$;

set timezone = 'UTC';
select
  today()     as "today()",
  tomorrow()  as "tomorrow()",
  yesterday() as "yesterday()";

Of course, the result will depend on when you do this. Here's the result when it was done at about 20:00 local time on 30-Sep-2021 in the America/Los_Angeles timezone.

        today()         |       tomorrow()       |      yesterday()
------------------------+------------------------+------------------------
 2021-10-01 00:00:00+00 | 2021-10-02 00:00:00+00 | 2021-09-30 00:00:00+00