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.

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;

        data type         |         value
--------------------------+------------------------
 timestamp with time zone | 1970-01-01 00:00:00+00

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');

          data type          |        value
-----------------------------+---------------------
 timestamp without time zone | 1970-01-01 01:00:00

timezone(timestamptz_value=>$1, timezone=>$2)

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();

 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

locale -a

-- 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';

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

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