Typecasting between values of different date-time datatypes
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 datatype. 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 datatype. 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 datatype. 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
| from\to | DATE | TIME | PLAIN TIMESTAMP | TIMESTAMPTZ | INTERVAL | TEXT |
| DATE | date to plain timestamp | date to timestamptz | date to text | |||
| TIME | time to interval | time to text | ||||
| PLAIN TIMESTAMP | plain timestamp to date | plain timestamp to time | plain timestamp to timestamptz | plain timestamp to text | ||
| TIMESTAMPTZ | timestamptz to date | timestamptz to time | timestamptz to plain timestamp | timestamptz to text | ||
| INTERVAL | interval to time | interval to text | ||||
| TEXT | text to date | text to time | text to plain timestamp | text to timestamptz | text to interval |
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.