---
title: "Typecasting between values of different date-time data types"
url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-values/"
---

# Typecasting between values of different date-time data types

Describes how to typecast date-time values of different date-time data types. [YSQL]

See the [table](/stable/api/ysql/datatypes/type_datetime/#synopsis "table") at the start of the overall "Date and time data types" section. It lists six data types, but quotes the [PostgreSQL documentation](https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-DATETIME-TABLE "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](#summary-table "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](#code-to-fill-out-the-thirty-interesting-table-cells "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:

```output
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*:

```output
data_type_x_value::timestamptz == (data_type_x_value::timestamp)::timestamptz
```

From *timestamptz*:

```output
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*](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/ "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*](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "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](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "Typecasting between date-time values and text values").

## Summary table

|                     |                                                                                   |                                                                                   |                                                                                                        |                                                                                                        |                                                              |                                                                                   |
|---------------------|-----------------------------------------------------------------------------------|-----------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------|--------------------------------------------------------------|-----------------------------------------------------------------------------------|
| *from\\to*          | **DATE**                                                                          | **TIME**                                                                          | **PLAIN TIMESTAMP**                                                                                    | **TIMESTAMPTZ**                                                                                        | **INTERVAL**                                                 | **TEXT**                                                                          |
| **DATE**            |                                                                                   |                                                                                   | [*date* to plain *timestamp*](#date-to-plain-timestamp "date to plain timestamp")                      | [*date* to *timestamptz*](#date-to-timestamptz "date to timestamptz")                                  |                                                              | [*date* to *text*](#date-to-text "date to text")                                  |
| **TIME**            |                                                                                   |                                                                                   |                                                                                                        |                                                                                                        | [*time* to *interval*](#time-to-interval "time to interval") | [*time* to *text*](#time-to-text "time to text")                                  |
| **PLAIN TIMESTAMP** | [plain *timestamp* to *date*](#plain-timestamp-to-date "plain timestamp to date") | [plain *timestamp* to *time*](#plain-timestamp-to-time "plain timestamp to time") |                                                                                                        | [plain *timestamp* to *timestamptz*](#plain-timestamp-to-timestamptz "plain timestamp to timestamptz") |                                                              | [plain *timestamp* to *text*](#plain-timestamp-to-text "plain timestamp to text") |
| **TIMESTAMPTZ**     | [*timestamptz* to *date*](#timestamptz-to-date "timestamptz to date")             | [*timestamptz* to *time*](#timestamptz-to-time "timestamptz to time")             | [*timestamptz* to plain *timestamp*](#timestamptz-to-plain-timestamp "timestamptz to plain timestamp") |                                                                                                        |                                                              | [*timestamptz* to *text*](#timestamptz-to-text "timestamptz to text")             |
| **INTERVAL**        |                                                                                   | [*interval* to *time*](#interval-to-time "interval to time")                      |                                                                                                        |                                                                                                        |                                                              | [*interval* to *text*](#interval-to-text "interval to text")                      |
| **TEXT**            | [*text* to *date*](#from-text "text to date")                                     | [*text* to *time*](#from-text "text to time")                                     | [*text* to plain *timestamp*](#from-text "text to plain timestamp")                                    | [*text* to *timestamptz*](#from-text "text to timestamptz")                                            | [*text* to *interval*](#from-text "text to interval")        |                                                                                   |

## The twenty supported typecasts

### From *date*

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

```plpgsql
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

```plpgsql
select date_value()::timestamp;
```

This is the result:

```output
2021-06-01 00:00:00
```

Notice that what you see is actually the result of a [plain *timestamp* to *text*](#plain-timestamp-to-text "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:

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

This is the result:

```output
 2021-06-01 00:00:00+00
```

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

This best defines the semantics:

```plpgsql
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:

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

This is the result:

```output
 2021-06-01
```

See the section [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "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:

```plpgsql
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:

```plpgsql
select time_value()::interval;
```

This is the result:

```output
 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:

```plpgsql
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:

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

This is the result:

```output
 12:00:00.123456
```

See the section [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "Typecasting between date-time values and text values") for more detail.

### From plain *timestamp*

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

```plpgsql
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:

```plpgsql
select plain_timestamp_value()::date;
```

This is the result:

```output
 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:

```plpgsql
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:

```plpgsql
select plain_timestamp_value()::time;
```

This is the result:

```output
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:

```plpgsql
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:

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

This is the result:

```output
 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:

```plpgsql
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*](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "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:

```plpgsql
select plain_timestamp_value()::text;
```

This is the result:

```output
 2021-06-01 12:13:19.123456
```

See the section [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "Typecasting between date-time values and text values") for more detail.

### From *timestamptz*

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

```plpgsql
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:

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

This is the result:

```output
 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:

```plpgsql
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*](#timestamptz-to-plain-timestamp "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:

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

This is the result:

```output
 03:13:19.123456
```

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

This best defines the semantics:

```plpgsql
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*](#timestamptz-to-plain-timestamp "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:

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

This is the result:

```output
 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:

```plpgsql
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*](#timestamptz-to-date "timestamptz to date"). See the section [Sensitivity of converting between *timestamptz* and plain *timestamp* to the *UTC offset*](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "Sensitivity of converting between timestamptz and plain timestamp to the UTC offset") for the full explanation of the semantics.

#### *timestamptz* to *text*

Try this:

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

This is the result:

```output
 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](#uses-tz-1 "here"), and
- "Think of it like this" below, [here](#text-to-timestamptz "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.)

```plpgsql
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

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

is borrowed from this:

```output
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*](/stable/api/ysql/datatypes/type_datetime/timezones/catalog-views/#the-jan-and-jul-tz-abbrevs-and-offsets-table-function " jan_and_jul_tz_abbrevs_and_offsets() table function") function.

Test it like this:

```plpgsql
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:

```output
 -08
 -07

 +00
 +00

 +11
 +10:30
```

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

```plpgsql
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*](#timestamptz-to-plain-timestamp "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](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "Typecasting between date-time values and text values") for more detail.

### From *interval*

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

```plpgsql
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:

```plpgsql
select interval_value()::time;
```

This is the result:

```output
 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?](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-representation/ "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:

```plpgsql
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=&gt;10, mins=&gt;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:

```plpgsql
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:

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

This is the result:

```output
 2 years 7 mons
```

See the section [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "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:

```plpgsql
\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:

```output
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:

```plpgsql
\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:

```output
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*:

```plpgsql
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*](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/ "sensitivity of converting between timestamptz and plain timestamp to the UTC offset").

Think of it like this:

```plpgsql
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:

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

This is the first result:

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

Now try this:

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

This is the second result:

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

(See [Create the *tz(timestamp)* function](#tz-function "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.

```plpgsql
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.