Typecasting between date-time values and text values
This section and its peer, Timezones and UTC offsets, are placed, with respect to the sequential reading order of the overall date-time time data types section that the table of contents presents, before the main treatment of the semantics of the date-time data types because the code examples in those subsequent sections rely on typecasting between date-time values and text values and on setting the timezone, either as a session parameter or as part of a date-time expression with the at time zone operator.
Introduction
Typecasting between date-time values and text values, rather than using explicit built-in functions like to_char(), to_timestamp(), or to_date() allows the demonstration code to be uncluttered and easy to understand. However, as this section shows, the typecast semantics is sensitive to the current settings of the DateStyle and IntervalStyle session parameters.
'Date-time' functions and operators in the PostgreSQL documentation.
PostgreSQL, and therefore YSQL, provide many functions and equivalent syntactical constructs that operate on, or produce, date-time values. These are documented in these dedicated sections within the main section Functions and operators and its children:
- Date and time operators.
- General-purpose date and time functions.
- Date and time formatting functions.
The following to_char_demo() code example uses the to_timestamp() function to produce a timestamptz value from a double precision value. The input represents the real number of seconds after, or before, the start of the Unix Epoch (a.k.a. the POSIX Epoch). See the Wikipedia article Unix time . The Unix Epoch begins at midnight on 1-January-1970 UTC. Try this:
set datestyle = 'ISO, DMY';
set timezone = 'UTC';
with a as (select to_timestamp(0::double precision) as v)
select
pg_typeof(v) as "data type",
v as "value"
from a;
See the Wikipedia article ISO 8601. The next section explains the significance of the DateStyle session parameter. And the section Timezones and UTC offsets explains the significance of the TimeZone session parameter.
This is the result:
data type | value
--------------------------+------------------------
timestamp with time zone | 1970-01-01 00:00:00+00
The to_char_demo() function casts the to_timestamp() result to a plain timestamp value that represents what a wall-clock located on the Greenwich Meridian would read. The immediately following code example casts it to what a wall-clock in Paris would read. Try this:
set datestyle = 'ISO, DMY';
deallocate all;
prepare stmt(timestamptz, text) as
with a as (select $1 at time zone $2 as v)
select
pg_typeof(v) as "data type",
v as "value"
from a;
execute stmt(to_timestamp(0::double precision), 'Europe/Paris');
This is the result:
data type | value
-----------------------------+---------------------
timestamp without time zone | 1970-01-01 01:00:00
The at time zone clause has function syntax equivalent:
timezone(timestamptz_value=>$1, timezone=>$2)
Create and execute the to_char_demo() function like this:
drop function if exists to_char_demo() cascade;
create function to_char_demo()
returns table(z text)
language plpgsql
as $body$
declare
-- Counted from midnight 1-Jan-1970 UTC.
secs constant double precision not null := 94996411200.456789;
t constant timestamp not null := to_timestamp(-secs) at time zone 'UTC';
fmt_1 constant text not null := 'TMDay / TMMonth';
fmt_2 constant text not null := 'TMDy dd-TMMon-yyyy hh24:mi:ss.us BC';
begin
set lc_time = 'en_US';
z := to_char(t, fmt_1); return next;
z := to_char(t, fmt_2); return next;
z := ''; return next;
set lc_time = 'it_IT';
z := to_char(t, fmt_1); return next;
z := to_char(t, fmt_2); return next;
z := ''; return next;
set lc_time = 'fi_FI';
z := to_char(t, fmt_1); return next;
z := to_char(t, fmt_2); return next;
z := ''; return next;
end;
$body$;
select z from to_char_demo();
Because this uses the to_char() function, and not typecasting, the result is not sensitive to the DateStyle setting. PostgreSQL documents the various components, like 'TMDay', 'TMMonth', 'yyyy', dd, and so on that define the format that to_char() produces in Table 9.24. Template Patterns for Date/Time Formatting .
And because to_char_demo() uses the at time zone operator, it is not sensitive to the current TimeZone setting. This is the result:
Friday / September
Fri 07-Sep-1042 11:59:59.543216 BC
Venerdì / Settembre
Ven 07-Set-1042 11:59:59.543216 BC
Perjantai / Syyskuu
Pe 07-Syy-1042 11:59:59.543216 BC
As you see, the lc_time session parameter determines the national language that is used for the spellings of the short and long day and month names. The PostgreSQL documentation describes this parameter in the section 23.1. Locale Support Notice that this section, in turn, references the section 23.1. 19.11.2. Locale and Formatting .
In short, a setting like 'fi_FI' is operating-system-dependent and may, or may not, be available according to what local support files have been installed. You can see what's available on a Unix-like system with this shell command:
locale -a
The 'TM' prefix, used in the function to_char_demo() above, is documented as "print localized day and month names based on lc_time" and so it works only in the to_char() output direction and not in the to_timestamp() input direction. This example makes the point without it:
-- Setting to something other than 'ISO, DMY', here, just to hint at the effect.
set datestyle = 'German, DMY';
select to_timestamp(
'07-09-1042 11:59:59.543216 BC',
'dd-mm-yyyy hh24:mi:ss.us BC') at time zone 'UTC';
This is the result:
07.09.1042 11:59:59.543216 BC
Two syntaxes for typecasting
Approach One: You can write the name of the target data type after the to-be-typecast value using the notation exemplified by ::timestamptz. Try these examples:
drop table if exists t cascade;
create table t(
c1 text primary key,
c2 timestamptz not null,
c3 timestamp not null,
c4 date not null,
c5 time not null,
c6 interval not null);
insert into t(c1, c2, c3, c4, c5, c6) values (
to_timestamp(1577200000) ::text,
'2019-12-24 16:42:47 UTC' ::timestamptz,
'2019-12-24 16:42:47' ::timestamp,
'2019-12-24' ::date,
'16:42:47' ::time,
'2 years 1 month' ::interval);
The test silently succeeds.
Approach Two: You can write the bare name of the target data type before the to-be-typecast value. Try these examples:
insert into t(c1, c2, c3, c4, c5, c6) values (
text (to_timestamp(1577300000)),
timestamptz '2019-12-24 16:42:47 UTC',
timestamp '2019-12-24 16:42:47',
date '2019-12-24',
time '16:42:47',
interval '2 years 1 month');
Again, the test silently succeeds. Notice that the parentheses are necessary for the text example. Try this:
select text to_timestamp(1577200000);
It causes this error:
42601: syntax error at or near "("
Approach One is used consistently throughout the whole of the Date and time data types section.
The DateStyle session parameter
See the PostgreSQL documentation section 19.11.2. Locale and Formatting. The DateStyle session parameter determines the format of the ::text typecast of a date-time value. It also, but in a subtle fashion, determines how a text value is interpreted when it's typecast to a date-time value. It has two orthogonal components: the style and the substyle. The style has these legal values:
ISO
SQL
PostgreSQL
German
And the substyle has these legal values:
DMY (with the synonyms Euro and European)
MDY (with the synonyms NonEuro, NonEuropean, and US)
YMD
The components can be set together, like this:
set datestyle = 'PostgreSQL, YMD';
show datestyle;
This is the result:
Postgres, YMD
Or they can be set separately like this:
set datestyle = 'German';
set datestyle = 'DMY';
show datestyle;
This is the result:
German, DMY
Create the DateStyle demo like this:
drop table if exists results;
create table results(
datestyle text primary key,
tstamp_as_text text not null,
tstamp timestamp);
drop procedure if exists datestyle_demo() cascade;
create procedure datestyle_demo()
language plpgsql
as $body$
declare
-- Counted from midnight 1-Jan-1970 UTC.
secs constant double precision not null := 94996411200.456789;
t constant timestamp not null := to_timestamp(-secs) at time zone 'UTC';
set_datestyle constant text not null := $$set datestyle = '%s, %s'$$;
d text not null := '';
s text not null := '';
d_shown text not null := '';
styles constant text[] not null := array['ISO', 'SQL', 'PostgreSQL', 'German'];
substyles constant text[] not null := array['DMY', 'MDY', 'YMD'];
begin
foreach d in array styles loop
foreach s in array substyles loop
execute format(set_datestyle, d, s);
show datestyle into d_shown;
insert into results(datestyle, tstamp_as_text) values (d_shown, t::text);
end loop;
end loop;
end;
$body$;
call datestyle_demo();
-- Set the same datestyle for the ::timestamp typecast of all the different text representations.
set datestyle = 'ISO, DMY';
update results set tstamp = tstamp_as_text::timestamp;
select datestyle, tstamp_as_text, tstamp::text
from results
order by datestyle;
This is the result:
German, DMY | 07.09.1042 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
German, MDY | 07.09.1042 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
German, YMD | 07.09.1042 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
ISO, DMY | 1042-09-07 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
ISO, MDY | 1042-09-07 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
ISO, YMD | 1042-09-07 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
Postgres, DMY | Fri 07 Sep 11:59:59.543216 1042 BC | 1042-09-07 11:59:59.543216 BC
Postgres, MDY | Fri Sep 07 11:59:59.543216 1042 BC | 1042-09-07 11:59:59.543216 BC
Postgres, YMD | Fri Sep 07 11:59:59.543216 1042 BC | 1042-09-07 11:59:59.543216 BC
SQL, DMY | 07/09/1042 11:59:59.543216 BC | 1042-09-07 11:59:59.543216 BC
SQL, MDY | 09/07/1042 11:59:59.543216 BC | 1042-07-09 11:59:59.543216 BC
SQL, YMD | 09/07/1042 11:59:59.543216 BC | 1042-07-09 11:59:59.543216 BC
The blank lines were added manually to improve the readability. Notice the following:
- For each of the four different style values, the 'YMD' substyle has the identical effect as does the 'MDY' substyle. It is therefore pointless, and directly confusing, to use the 'YMD' substyle. Yugabyte recommends that you simply avoid doing this.
- For the two style values 'ISO' and German', the 'MDY' substyle has the identical effect as does the 'DMY' substyle in both the ::text direction and the ::timestamp direction. Yugabyte recommends that you always use the 'DMY' substyle in this scenario because this corresponds to the order that is actually produced.
- For the two style values 'Postgres' and SQL', the 'MDY' substyle has the effect that the mnemonic suggests in the ::text direction. However, for the Postgres style, it has no effect in the ::timestamp direction. This is the only feasible behavior because the Postgres style renders the day numerically and the month alphabetically—so it's impossible to take 'Sep' as a day, even though '07' can be taken as a month. In contrast, because the 'SQL' style renders both the day and the month numerically, it's impossible to interpret '07/09/1042' and '09/07/1042' reliably unless 'DMY' or 'MDY' specify the rule.
Try this:
-- Test One.
set datestyle = 'SQL, MDY';
select 'The American exceptionalism way: '||to_char('07/09/2000 12:00:00'::timestamp, 'Mon-dd-yyyy');
This is the result:
The American exceptionalism way: Jul-09-2000
Now try it the other way round like this:
-- Test Two.
set datestyle = 'SQL, DMY';
select 'The sensible way: '||to_char('07/09/2000 12:00:00'::timestamp, 'dd-Mon-yyyy');
The result is now this:
The sensible way: 07-Sep-2000
The operand of the ::timestamp typecast is spelled the same in Test One as it is in Test Two. But the resulting dates are different—in the famously confusing way.
Even when a nominal month has an illegal number like '19', and this could be used for automatic disambiguation, the 'DMY' or 'MDY' substyle is taken as a non-negotiable directive. Try this:
-- Test Three.
set datestyle = 'SQL, MDY';
select to_char('19/09/2000 12:00:00'::timestamp, 'dd-mm-yyyy');
-- Test Four.
set datestyle = 'SQL, DMY';
select to_char('07/19/2000 12:00:00'::timestamp, 'dd-mm-yyyy');
Each of Test Three and Test Four produces the same error:
22008: date/time field value out of range ...
Never rely on typecasting between 'text' and 'date-time' values unless you set 'DateStyle' explicitly.
Yugabyte recommends that application code should convert between text values and date-time values using explicit conversion functions that use a format specification. You might be reading from a file that client-side code ingests that simply comes with a non-negotiable pre-determined format. Or you might be processing human input that comes from a UI that allows the user to choose the date-time format from a list.
- To produce a timestamptz value, use to_timestamp(text, text).
- To produce a plain timestamp value, use to_timestamp(text, text) with at time zone 'UTC'.
- To produce a date value, use to_date(text, text).
- To produce a plain time value, do what this code example models:
drop table if exists t cascade;
create table t(k int primary key, t1 time not null, t2 time not null);
insert into t(k, t1, t2) values(1, '00:00:00'::time, '00:00:00'::time);
deallocate all;
prepare s_1(text) as
update t set t1 = to_timestamp($1, 'hh24:mi:ss')::time
where k = 1;
prepare s_2(text) as
update t set t2 = to_timestamp($1, 'hh24:mi:ss')::time
where k = 1;
set timezone = 'UTC';
execute s_1('13:00:56');
set timezone = 'America/Los_Angeles';
execute s_2('13:00:56');
select (t1 = t2)::text from t where k = 1;
-
The result is true, showing that the method is insensitive to the current TimeZone setting.
-
To convert a date-time value to a text value, use the appropriate to_char() overload as has been illustrated above.
Of course, it's safe to use the typecasting approach in ad hoc tests where you can set DateStyle to whatever you want to without worrying that it might affect the behavior of existing application code that doesn't set the parameter explicitly. The same applies to small stand-alone code examples that support documentation.
The YSQL documentation assumes that the DateStyle style component is set to 'ISO' unless it's explicitly set otherwise. (The substyle setting has no effect with the 'ISO' style.)
The IntervalStyle session parameter
The IntervalStyle session parameter controls the format of the result of the ::text typecast operator on an interval value. It has no effect on the outcome of the ::interval typecast operator on a text value. There are just four legal choices. It's easy to see the list by making a deliberate error:
set intervalstyle = 'oops, I did a typo';
This is the result:
ERROR: 22023: invalid value for parameter "intervalstyle": "oops, I did a typo"
HINT: Available values: postgres, postgres_verbose, sql_standard, iso_8601.
The IntervalStyle demo is a straight copy, paste, and massage derivative of the DateStyle demo. Create it like this:
drop table if exists results;
create table results(
intervalstyle text primary key,
i_as_text text not null,
i interval);
drop procedure if exists intervalstyle_demo() cascade;
create procedure intervalstyle_demo()
language plpgsql
as $body$
declare
i constant interval not null := make_interval(
years => 1,
months => 2,
days => 3,
hours => 4,
mins => 5,
secs => 6.345678);
set_intervalstyle constant text not null := $$set intervalstyle = '%s'$$;
s text not null := '';
s_shown text not null := '';
styles constant text[] not null := array['postgres', 'postgres_verbose', 'sql_standard', 'iso_8601'];
begin
foreach s in array styles loop
execute format(set_intervalstyle, s);
show intervalstyle into s_shown;
insert into results(intervalstyle, i_as_text) values (s_shown, i::text);
end loop;
end;
$body$;
call intervalstyle_demo();
-- Set the same intervalstyle for the ::interval typecast of all the different text representations.
set intervalstyle = 'postgres';
update results set i = i_as_text::interval;
select intervalstyle, i_as_text, i::text
from results
order by intervalstyle;
This is the result:
intervalstyle | i_as_text | i
------------------+-----------------------------------------------------+--------------------------------------
iso_8601 | P1Y2M3DT4H5M6.345678S | 1 year 2 mons 3 days 04:05:06.345678
postgres | 1 year 2 mons 3 days 04:05:06.345678 | 1 year 2 mons 3 days 04:05:06.345678
postgres_verbose | @ 1 year 2 mons 3 days 4 hours 5 mins 6.345678 secs | 1 year 2 mons 3 days 04:05:06.345678
sql_standard | +1-2 +3 +4:05:06.345678 | 1 year 2 mons 3 days 04:05:06.345678
The results are consistent with the fact that the IntervalStyle setting has no effect on the outcome of the ::interval typecast operator on a text value. This is because the syntax rules for each of the four different IntervalStyle settings allow automatic disambiguation.
Never rely on typecasting from 'interval' values to 'text' values unless you set 'IntervalStyle' explicitly.
Yugabyte recommends that application code should convert between text values and interval values using explicit conversion functions.
The make_interval() built-in function creates an interval value using explicitly specified values in the units that you prefer—like years, days, hours, or weeks. Yugabyte recommends always using this approach and never using the ::interval typecast. This advice rests on ideas developed in the section Interval arithmetic. The recommended approach is formalized in the section Custom domain types for specializing the native interval functionality.
The extract SQL functionality lets you assign values like the years or days components of an interval value to dedicated destinations. This approach is used in the definition of function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t, described in the User-defined interval utility functions section.
Of course, it's safe to use the typecasting approach in ad hoc tests where you can set IntervalStyle to whatever you want to without worrying that it might affect the behavior of existing application code that doesn't set the parameter explicitly. The same applies to small stand-alone code examples that support documentation.
The YSQL documentation assumes that IntervalStyle is set to 'postgres' unless it's explicitly set otherwise.