--- title: "Typecasting between date-time values and text values" url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/" --- # Typecasting between date-time values and text values Describes how to typecast date-time values to text values, and vice versa. [YSQL] This section and its peer, [Timezones and *UTC offsets*](/stable/api/ysql/datatypes/type_datetime/timezones/ "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](/stable/api/ysql/datatypes/type_datetime/toc/ "table of contents") presents, before the main treatment of the [semantics of the *date-time* data types](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/ "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](/stable/api/ysql/exprs/ "Built-in functions and operators") and its children: - [Date and time operators](/stable/api/ysql/datatypes/type_datetime/operators/ "Date and time operators"). - [General-purpose date and time functions](/stable/api/ysql/datatypes/type_datetime/functions/ "General-purpose date and time functions"). - [Date and time formatting functions](/stable/api/ysql/datatypes/type_datetime/formatting-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](https://en.wikipedia.org/wiki/Unix_time "Unix time"). The Unix Epoch begins at midnight on 1-January-1970 *UTC*. Try this: ```plpgsql 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](https://en.wikipedia.org/wiki/ISO_8601 "ISO 8601"). The [next section](#the-datestyle-session-parameter "next section") explains the significance of the *DateStyle* session parameter. And the section [Timezones and *UTC offsets*](/stable/api/ysql/datatypes/type_datetime/timezones/ "Timezones and UTC offsets") explains the significance of the *TimeZone* session parameter. This is the result: ```output 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: ```plpgsql 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: ```output data type | value -----------------------------+--------------------- timestamp without time zone | 1970-01-01 01:00:00 ``` The *at time zone* clause has *function syntax* equivalent: ```output timezone(timestamptz_value=>$1, timezone=>$2) ``` Create and execute the *to\_char\_demo()* function like this: ```plpgsql 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](https://www.postgresql.org/docs/15/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE "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: ```output 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](https://www.postgresql.org/docs/15/locale.html "23.1. Locale Support"). Notice that this section, in turn, references the section [19.11.2. Locale and Formatting](https://www.postgresql.org/docs/15/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT "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: ```output 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: ```plpgsql -- 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: ```output 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: ```postgresql 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: ```plpgsql 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: ```plpgsql select text to_timestamp(1577200000); ``` It causes this error: ```output 42601: syntax error at or near "(" ``` Approach One is used consistently throughout the whole of the [Date and time data types](/stable/api/ysql/datatypes/type_datetime/ "Date and time data types") section. ## The DateStyle session parameter See the PostgreSQL documentation section [19.11.2. Locale and Formatting](https://www.postgresql.org/docs/15/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT "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: ```output ISO SQL PostgreSQL German ``` And the *substyle* has these legal values: ```output DMY (with the synonyms Euro and European) MDY (with the synonyms NonEuro, NonEuropean, and US) YMD ``` The components can be set together, like this: ```plpgsql set datestyle = 'PostgreSQL, YMD'; show datestyle; ``` This is the result: ```output Postgres, YMD ``` Or they can be set separately like this: ```plpgsql set datestyle = 'German'; set datestyle = 'DMY'; show datestyle; ``` This is the result: ```output German, DMY ``` Create the *DateStyle* demo like this: ```plpgsql 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: ```output 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: ```plpgsql -- 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: ```output The American exceptionalism way: Jul-09-2000 ``` Now try it the other way round like this: ```plpgsql -- 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: ```output 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: ```postgresql -- 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: ```output 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: ```plpgsql 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: ```postgresql set intervalstyle = 'oops, I did a typo'; ``` This is the result: ```output 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: ```postgresql 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: ```output 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](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-arithmetic/ "Interval arithmetic"). The recommended approach is formalized in the section [Custom domain types for specializing the native *interval* functionality](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/custom-interval-domains/ "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](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/#function-interval-mm-dd-ss-interval-returns-interval-mm-dd-ss-t "function interval_mm_dd_ss (interval) returns interval_mm_dd_ss_t"), described in the [User-defined *interval* utility functions](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-utilities/ "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.