Typecasting between values of different date-time data types

See the table at the start of the overall "Date and time data types" section. It lists six data types, but quotes the PostgreSQL documentation that recommends that you avoid using the timetz data type. This leaves five date-time data types that are recommended for use. Each of the two axes of the Summary table below lists these five data types along with the text data type—so there are thirty-six cells.

The cells on the diagonal represent the typecast from some_type to the same type—and so they are tautologically uninteresting. This leaves thirty cells and therefore thirty rules to understand. See the section Code to fill out the thirty interesting table cells. This shows that ten of the remaining typecasts are simply unsupported. The attempts cause the 42846 error. This maps in PL/pgSQL code to the cannot_coerce exception. You get one of these messages:

cannot cast type date to time without time zone
cannot cast type date to interval
cannot cast type time without time zone to date
cannot cast type time without time zone to timestamp without time zone
cannot cast type time without time zone to timestamp with time zone
cannot cast type timestamp without time zone to interval
cannot cast type timestamp with time zone to interval
cannot cast type interval to date
cannot cast type interval to timestamp without time zone
cannot cast type interval to timestamp with time zone

Each of the messages simply spells out the logical conundrum. For example, how could you possibly derive a date value from a time value in a way that was useful?

There remain therefore only twenty typecasts whose semantics you need to understand. These are indicated by the twenty links in the table to the subsections that explain the rules.

  • Thirteen of the typecasts do not involve timestamptz—neither as the source or the target data type. The rules for these are insensitive to the reigning UTC offset.
  • There remain, therefore, seven typecasts that do involve timestamptz as either the source or the target data type. These are shown, of course, by the non-empty cells in the row and the column labeled with that data type. Here, the rules are sensitive to the reigning UTC offset.
    • The two typecasts, plain timestamp to timestamptz and timestamptz to plain timestamp, are critical to defining the rules of the sensitivity to the reigning UTC offset.
    • The UTC offset-sensitivity rules for the remaining five typecasts can each be understood in terms of the rules for the mutual plain timestamp to/from timestamptz typecasts.

This is the way to see the final point:

To timestamptz:

data_type_x_value::timestamptz == (data_type_x_value::timestamp)::timestamptz

From timestamptz:

timestamptz_value::data_type_x == (timestamptz_value::timestamp)::data_type_x

This rests on these critical facts:

  • If data_type_x to timestamptz is supported, then data_type_x to timestamp is supported too.

  • If timestamptz to data_type_x is supported, then timestamp to data_type_x is supported too.

You can see the correctness of these critical facts from the summary table. The five typecasts in question are found: three in vertically adjacent cells in the plain timestamp and timestamptz rows; and two in horizontally adjacent cells in the plain timestamp and timestamptz columns.

The account on this page demonstrates that the decomposition rules for data_type_x to timestamptz and timestamptz to data_type_x hold; it demonstrates the outcomes for the typecasts between plain timestamp values and timestamptz values; and it shows that they are the same as when you use the at time zone operator.

The semantics for the mutual conversions between 'plain timestamp' and 'timestamptz' is defined elsewhere.

The outcomes depend mandatorily on specifying the value of the UTC offset. There's more than one way to specify this. See the section Four ways to specify the UTC offset. The actual conversion semantics is explained in the section Sensitivity of converting between timestamptz and plain timestamp to the UTC offset.

The outcomes of the typecasts between date-time values and text values, corresponding to all the cells in the bottom row and the right-most column, depend on the current setting of the DateStyle or IntervalStyle session parameters. This is explained in the section Typecasting between date-time values and text values.

Summary table

The twenty supported typecasts

From date

Before doing the "from date" tests, do this:

drop function if exists date_value() cascade;
create function date_value()
  returns date
  language sql
as $body$
  select make_date(2021, 6, 1);
$body$;

date to plain timestamp

Try this

select date_value()::timestamp;

This is the result:

2021-06-01 00:00:00

Notice that what you see is actually the result of a plain timestamp to text typecast.

The typecast date value becomes the date component of the timestamptz value; and the time-of-day component of the timestamptz value is set to 00:00:00.

date to timestamptz

Try this:

set timezone = 'UTC';
select  date_value()::timestamptz;

This is the result:

 2021-06-01 00:00:00+00

You're actually seeing the ::text typecast of the resulting timestamptz value.

This best defines the semantics:

select
  (
    (select  date_value()::timestamptz) =
    (select (date_value()::timestamp)::timestamptz)
  )::text;

The result is true. The outcome of typecasting a date value to a timestamptz value is the same as that of typecasting a date value to a plain timestamp value and then typecasting that intermediate value to a timestamptz value. If you know the rules for the date to plain timestamp typecast and for the plain timestamp to timestamptz typecast, then you can predict the outcome of a date to timestamptz typecast.

date to text

Try this:

set datestyle = 'ISO, DMY';
select date_value()::text;

This is the result:

 2021-06-01

See the section Typecasting between date-time values and text values. Briefly, the typecast outcomes are sensitive to the current setting of the 'datestyle' parameter. Yugabyte recommends that you always use 'ISO, DMY'.

From time

Before doing the "from time" tests, do this:

drop function if exists time_value() cascade;
create function time_value()
  returns time
  language sql
as $body$
  select make_time(12, 13, 42.123456);
$body$;

time to interval

Try this:

select time_value()::interval;

This is the result:

 12:13:42.123456

You're actually seeing the ::text typecast of the resulting interval value.

The rule here is trivial. The to-be-typecast time value is taken as a real number of seconds—counting, of course, from midnight. Suppose that the answer is n. The resulting interval value is given by make_interval(n). test the rule like this:

select (
    (select time_value()::interval) =
    (
      select make_interval(secs=>
        (
          extract(hours   from time_value())*60.0*60.0 +
          extract(minutes from time_value())*60.0 +
          extract(seconds from time_value())
        ))
    )
  )::text;

The result is true.

time to text

Try this:

set datestyle = 'ISO, DMY';
select time_value()::text;

This is the result:

 12:00:00.123456

See the section Typecasting between date-time values and text values for more detail.

From plain timestamp

Before doing the "from timestamp" tests, do this:

drop function if exists plain_timestamp_value() cascade;
create function plain_timestamp_value()
  returns timestamp
  language sql
as $body$
  select make_timestamp(2021, 6, 1, 12, 13, 19.123456);
$body$;

plain timestamp to date

Try this:

select plain_timestamp_value()::date;

This is the result:

 2021-06-01

You're actually seeing the ::text typecast of the resulting date value.

The typecast simply ignores the time-of-day component of the to-be-typecast timestamp value and uses its date component to set the resulting date value.

You might prefer to understand it like this:

select (
    (select plain_timestamp_value()::date) =
    (
      select (
        extract(year  from plain_timestamp_value())::text||'-'||
        extract(month from plain_timestamp_value())::text||'-'||
        extract(day   from plain_timestamp_value())::text
        )::date
    )
  )::text;

The result is true.

plain timestamp to time

Try this:

select plain_timestamp_value()::time;

This is the result:

12:13:19.123456

You're actually seeing the ::text typecast of the resulting time value.

The typecast simply ignores the date component of the to-be-typecast timestamp value and uses its time-of-day component to set the resulting time value.

You might prefer to understand it like this:

select (
    (select plain_timestamp_value()::time) =
    (
    select (
      extract(hours   from plain_timestamp_value())::text||':'||
      extract(minutes from plain_timestamp_value())::text||':'||
      extract(seconds from plain_timestamp_value())::text
      )::time
    )
  )::text;

The result is true.

plain timestamp to timestamptz

Try this:

set timezone = 'UTC';
select plain_timestamp_value()::timestamptz;

This is the result:

 2021-06-01 12:13:19.123456+00

You're actually seeing the ::text typecast of the resulting timestamptz value.

This best defines the semantics:

set time zone interval '-7 hours';
select (
    (select plain_timestamp_value()::timestamptz) =
    (select plain_timestamp_value() at time zone interval '-7 hours')
  )::text;

The result is true. See the section Sensitivity of converting between timestamptz and plain timestamp to the UTC offset for the full explanation of the semantics.

plain timestamp to text

Try this:

select plain_timestamp_value()::text;

This is the result:

 2021-06-01 12:13:19.123456

See the section Typecasting between date-time values and text values for more detail.

From timestamptz

Before doing the "from timestamptz" tests, do this:

drop function if exists timestamptz_value() cascade;
create function timestamptz_value()
  returns timestamptz
  language sql
as $body$
  select make_timestamptz(2021, 6, 1, 20, 13, 19.123456, 'America/Los_Angeles');
$body$;

timestamptz to date

Try this:

set timezone = 'UTC';
select timestamptz_value()::date;

This is the result:

 2021-06-02

You're actually seeing the ::text typecast of the resulting date value.

Notice that the date displayed here, 2-June, is later that the date that defines the timestamptz value. The test was contrived to produce this result. You need to have a solid mental model of the joint semantics of the plain timestamp and the timestamptz data types in order confidently to predict this outcome.

This best defines the semantics:

set timezone = 'UTC';
select
  (
    (select  timestamptz_value()::date) =
    (select (timestamptz_value()::timestamp)::date)
  )::text;

The result is true. In other words, if you understand the rules for the timestamptz to plain timestamp typecast, and the rules for the plain timestamp to date typecast, then you understand the rules for the timestamptz to date typecast.

timestamptz to time

Try this:

set timezone = 'UTC';
select timestamptz_value()::time;

This is the result:

 03:13:19.123456

You're actually seeing the ::text typecast of the resulting time value.

This best defines the semantics:

set timezone = 'UTC';
select
  (
    (select  timestamptz_value()::time) =
    (select (timestamptz_value()::timestamp)::time)
  )::text;

The result is true. In other words, if you understand the rules for the timestamptz to plain timestamp typecast, and the rules for the plain timestamp to time typecast, then you understand the rules for the timestamptz to time typecast.

timestamptz to plain timestamp

Try this:

set timezone = 'UTC';
select timestamptz_value()::timestamp;

This is the result:

 2021-06-02 03:13:19.123456

You're actually seeing the ::text typecast of the resulting plain timestamp value.

This best defines the semantics:

set time zone interval '13 hours';
select
  (
    (select  timestamptz_value()::timestamp) =
    (select  timestamptz_value() at time zone interval '13 hours')
  )::text;

The result is true.

Notice that the date displayed here, 2-June, is later that the date that defines the timestamptz value. This is the effect that was referred to in the section timestamptz to date. See the section Sensitivity of converting between timestamptz and plain timestamp to the UTC offset for the full explanation of the semantics.

timestamptz to text

Try this:

set timezone = 'UTC';
select timestamptz_value()::text;

This is the result:

 2021-06-02 03:13:19.123456+00

Create the 'tz(timestamp)' function

The code that follows

  • "This best defines the semantics of the ::text typecast of a timestamptz value" below, here, and
  • "Think of it like this" below, here

depends on the user-defined function tz(timestamp). It shows the session's current UTC offset as a text string. It turns out that the ::text typecast that the present subsection addresses uses rather whimsical rules to format the UTC offset. Briefly, the sign is always shown, even when it is positive; and when the minutes component of the offset is zero, this is elided. (A UTC offset never has a non-zero seconds component.)

drop function if exists tz(timestamp) cascade;

create function tz(t in timestamp)
  returns text
  language plpgsql
as $body$
declare
  i  constant interval not null := to_char((t::text||' UTC')::timestamptz, 'TZH:TZM')::interval;
  hh constant int      not null := extract(hours   from i);
  mm constant int      not null := extract(minutes from i);
  t  constant text     not null := to_char(hh, 's00')||
                                     case
                                       when mm <> 0 then ':'||ltrim(to_char(mm, '00'))
                                       else               ''
                                     end;
begin
  return t;
end;
$body$;

Notice that this

to_char((t::text||' UTC')::timestamptz, 'TZH:TZM')::interval

is borrowed from this:

to_char('2021-01-01 12:00:00 UTC'::timestamptz, 'TZH:TZM')::interval

in the implementation of the jan_and_jul_tz_abbrevs_and_offsets() table function function.

Test it like this:

set timezone = 'America/Los_Angeles';
select tz('2021-01-01 12:00:00'::timestamp);
select tz('2021-07-01 12:00:00'::timestamp);

set timezone = 'UTC';
select tz('2021-01-01 12:00:00'::timestamp);
select tz('2021-07-01 12:00:00'::timestamp);

set timezone = 'Australia/Lord_Howe';
select tz('2021-01-01 12:00:00'::timestamp);
select tz('2021-07-01 12:00:00'::timestamp);

These are the results—just as is required:

 -08
 -07

 +00
 +00

 +11
 +10:30

This best defines the semantics of the ::text typecast of a timestamptz value:

set timezone = 'Asia/Tehran';
select
  (
    (select  timestamptz_value()::text) =
    (select (timestamptz_value()::timestamp)::text||tz(timestamptz_value()::timestamp))
  )::text;

This code asks to see the timestamptz value expressed as the local date-time in the reigning timezone decorated with the UTC offset of that reigning timezone.

The result is true. In other words, if you understand the rules for the timestamptz to plain timestamp typecast, then you understand the rules for the timestamptz to text typecast.

See the section Typecasting between date-time values and text values for more detail.

From interval

Before doing the "from interval" tests, do this:

drop function if exists interval_value() cascade;
create function interval_value()
  returns interval
  language sql
as $body$
  select make_interval(hours=>10, mins=>17);
$body$;

interval to time

Try this:

select interval_value()::time;

This is the result:

 10:17:00

You're actually seeing the ::text typecast of the resulting time value.

The rule here is trivial. But to see it as such you have to understand what the section How does YSQL represent an interval value? explains. The reason is that the values of the mm and dd fields of the internal [mm, dd, ss] tuple are simply ignored by the interval to time typecast. You can confirm that like this:

select ('7 years 5 months 13 days 10:17:00'::interval)::time;

The result is 10:17:00, just as it was for the typecast of the interval value created as make_interval(hours=>10, mins=>17).

The time value is derived simply by treating the_ss_ value from the interval value's [hh, mm, ss] internal tuple as the number of seconds since midnight. The arithmetic is easiest to implement by using the SQL extract operator and the make_Time() built-in function.

Test the rule like this:

select (
    (select interval_value()::time) =
    (
      with v as (
        select
          extract(hours   from interval_value())::int as hh,
          extract(minutes from interval_value())::int as mi,
          extract(seconds from interval_value())      as ss)
      select make_time(hh, mi, ss)
      from v
    )
  )::text;

The result is true.

interval to text

Try this:

select make_interval(years=>2, months=>7)::text;

This is the result:

 2 years 7 mons

See the section Typecasting between date-time values and text values for more detail.

From text

All of the text to date, text to time, text to plain timestamp, text to timestamptz, and text to interval typecasts can be demonstrated with a single query:

\x on
select
  '2021-06-01'                    ::date        as "date value",
  '12:13:42.123456'               ::time        as "time value",
  '2021-06-01 12:13:19.123456'    ::timestamp   as "plain timestamp value value",
  '2021-06-02 03:13:19.123456+03' ::timestamptz as "timestamptz value value",
  '10 hours 17 minutes'           ::interval    as "interval value value";
\x off

This is the result:

date value                  | 2021-06-01
time value                  | 12:13:42.123456
plain timestamp value value | 2021-06-01 12:13:19.123456
timestamptz value value     | 2021-06-02 00:13:19.123456+00
interval value value        | 10:17:00

In all cases but the text to timestamptz typecast, the rules are obvious:

  • either you specify every datum that's needed to define the target value (for example, the year, month, and date for a date value or the hours, minutes, and seconds for a time value);
  • or you leave out any such field for which a default is defined.

Here's an example of defaulting:

\x on
select
  (select '23:00'::time)            as "Partically defaulted 'time' value ",
  (select '17':: interval)          as "Partically defaulted 'interval' value",
  (select '1984-03-01'::timestamp)  as "Partically defaulted 'timestamp' value";
  \x off

This is the result:

Partically defaulted 'time' value      | 23:00:00
Partically defaulted 'interval' value  | 00:00:17
Partically defaulted 'timestamp' value | 1984-03-01 00:00:00

The third example is better seen as an example of typecasting from date to timestamp:

select (
    (select '1984-03-01'::timestamp) =
    (select ('1984-03-01'::date)::timestamp)
  )::text;

The result is true.

text to timestamptz

As you'd expect, the text to timestamptz typecast needs some special discussion. A UTC offset value is logically required. But you can elide this within the text literal and leave it to be defined using the rule that maps the current timezone to a UTC offset value. The rules that determine the outcomes of the examples below are underpinned by the semantics of the sensitivity of converting between timestamptz and plain timestamp to the UTC offset.

Think of it like this:

deallocate all;
prepare qry as
with
  txt as (
    select
      '2021-01-01 12:00:00'::text as winter_txt,
      '2021-07-01 12:00:00'::text as summer_txt
    ),

  tz as (
    select
       tz((select winter_txt from txt)::timestamp) as winter_tz,
       tz((select summer_txt from txt)::timestamp) as summer_tz
    )

select
  (  (  (select winter_txt from txt)||(select winter_tz from tz)  )::timestamptz  ) as "winter",
  (  (  (select summer_txt from txt)||(select summer_tz from tz)  )::timestamptz  ) as "summer";

First try this:

set timezone = 'Asia/Tehran';
execute qry;

This is the first result:

          winter           |          summer
---------------------------+---------------------------
 2021-01-01 12:00:00+03:30 | 2021-07-01 12:00:00+04:30

Now try this:

set timezone = 'Europe/Helsinki';
execute qry;

This is the second result:

         winter         |         summer
------------------------+------------------------
 2021-01-01 12:00:00+02 | 2021-07-01 12:00:00+03

(See Create the tz(timestamp) function for its definition.)

Code to fill out the thirty interesting table cells

The following anonymous block executes the typecast that corresponds to each of the table's thirty interesting table cells in rows-first order. When the code executes an unsupported typecast, the exception is handled and the error text is checked to confirm that it is what is expected.

do $body$
declare
  msg                           text        not null := '';

  d0                   constant date        not null := make_date(2021, 6, 1);
  t0                   constant time        not null := make_time(12, 13, 42.123456);
  ts0                  constant timestamp   not null := make_timestamp(2021, 6, 1, 12, 13, 19.123456);
  tstz0                constant timestamptz not null := make_timestamptz(2021, 6, 1, 20, 13, 19.123456, 'America/Los_Angeles');
  i0                   constant interval    not null := make_interval(hours=> 5, mins=>17, secs=>42.123456);

  d                             date        not null := d0;
  t                             time        not null := t0;
  ts                            timestamp   not null := ts0;
  tstz                          timestamptz not null := tstz0;
  i                             interval    not null := i0;
  txt                           text        not null := '';
begin
  -- Cell 1.2.
  begin
    t := d0::time;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type date to time without time zone', 'Unexpected error';
  end;

  -- Cell 1.3.
  begin
    ts := d0::timestamp;
  end;

  -- Cell 1.4.
  begin
    tstz := d0::timestamptz;
  end;

  -- Cell 1.5.
  begin
    i := d0::interval;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type date to interval', 'Unexpected error';
  end;

  -- Cell 1.6.
  begin
    txt := d0::text;
  end;

  -- Cell 2.1.
  begin
    d := t0::date;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type time without time zone to date', 'Unexpected error';
  end;

  -- Cell 2.3.
  begin
    ts := t0::timestamp;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type time without time zone to timestamp without time zone', 'Unexpected error';
  end;

  -- Cell 2.4.
  begin
    ts := t0::timestamptz;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type time without time zone to timestamp with time zone', 'Unexpected error';
  end;

  -- Cell 2.5.
  begin
    i := t0::interval;
  end;

  -- Cell 2.6.
  begin
    txt := t0::interval;
  end;

  -- Cell 3.1.
  begin
    d := ts0::date;
  end;

  -- Cell 3.2.
  begin
    t := ts0::time;
  end;

  -- Cell 3.4.
  begin
    tstz := ts0::timestamptz;
  end;

  -- Cell 3.5.
  begin
    i := ts0::interval;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type timestamp without time zone to interval', 'Unexpected error';
  end;

  -- Cell 3.6.
  begin
    txt := ts0::text;
  end;

  -- Cell 4.1.
  begin
    d := tstz0::date;
  end;

  -- Cell 4.2.
  begin
    t := tstz0::time;
  end;

  -- Cell 4.3.
  begin
    ts := tstz0::timestamp;
  end;

  -- Cell 4.5.
  begin
    i := tstz0::interval;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type timestamp with time zone to interval', 'Unexpected error';
  end;

  -- Cell 4.6.
  begin
    txt := tstz0::text;
  end;

  -- Cell 5.1.
  begin
    d := i0::date;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type interval to date', 'Unexpected error';
  end;

  -- Cell 5.2.
  begin
    t := i0::time;
  end;

  -- Cell 5.3.
  begin
    ts := i0::timestamp;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type interval to timestamp without time zone', 'Unexpected error';
  end;

  -- Cell 5.4.
  begin
    tstz := i0::timestamptz;
    assert false, 'Unexpected success';
  exception when cannot_coerce then
    get stacked diagnostics msg = message_text;
    assert msg = 'cannot cast type interval to timestamp with time zone', 'Unexpected error';
  end;

  -- Cell 5.6.
  begin
    txt := i0::text;
  end;

  -- Cell 6.1.
  begin
    d := '2021-06-01'::date;
  end;

  -- Cell 6.2.
  begin
    t := '12:00:00'::time;
  end;

  -- Cell 6.3.
  begin
    ts := '2021-06-01 12:00:00'::timestamp;
  end;

  -- Cell 6.4.
  begin
    tstz := '2021-06-01 12:00:00 UTC'::timestamptz;
  end;

  -- Cell 6.5.
  begin
    i := '2 hours 3 minutes 4.56 seconds'::interval;
  end;
end;
$body$;

The block finishes without error confirming that the typecasts are, or are not, supported as the table above lists.