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 Built-in functions and operators and its children:

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