Date and time formatting functions
This page describes all of the date-time formatting functions, both in the direction date-time value to text value and in the direction text value to date-time value. The functions use a so-called template to determine, in the to text value direction, how the date-time value will be rendered as a text value and, in the to date-time value direction, how the to-be-converted text value is to be interpreted. The template, in turn, is made up of a mixture of pre-defined so-called template patterns and free text, intermingled in a user-defined order. See the section Date-time template patterns. The effects of these template patterns, again in turn, can be modified. See the section Date-time template pattern modifiers.
Here's a simple example. It uses all of the relevant functions.
set timezone = 'Asia/Kathmandu';
select
to_char('2021-05-17 15:00:00'::timestamp, 'hh24:mi "on" dd-Mon-yyyy')::text as "timestamp to text",
to_timestamp('15:00 17-May-2021', 'hh24:mi dd-Mon-yyyy' )::text as "text to timestamptz",
to_date('17-May-2021', 'dd-Mon-yyyy' )::text as "text to date";
This is the result:
timestamp to text | text to timestamptz | text to date
----------------------+---------------------------+--------------
15:00 on 17-May-2021 | 2021-05-17 15:00:00+05:45 | 2021-05-17
The general-purpose date-time functions are described in the separate dedicated General-purpose functions section.
From date-time to text
Free text that you might include in a template has a specific effect only in the to text direction. But you might need to use dummy free text when you convert in the to date-time value direction and the to-be-converted text value contains uninterpretable substrings. See the subsection From text to date-time.
There are very many template patterns, and these are interpreted in preference to the free text. Whitespace that might surround such pattern characters makes no difference to how they are detected. For example, the single letter I calls for the last digit of the ISO 8601 week-numbering year. This means that the phrase In the kitchen sink, rendered bare in the template, would not be interpreted as you're likely to want. You can escape an individual free text character by surrounding it with double quotes: "I"n the k"i"tchen s"i"nk. But the usual approach is simply to surround every free text string, no matter what its length is, with double quotes: "In the kitchen sink".
Try this:
\x on
with c as (select '2019-04-22 09:00:00'::timestamp as t)
select
to_char(t, 'Started at Yugabyte in yyyy at HH AM on Dy ddth Mon') as "proving the point",
to_char(t, 'Starte"d" at "Y"ugab"y"te "i"n yyyy at HH AM on Dy ddth Mon') as "cumbersome",
to_char(t, '"Started at Yugabyte in" yyyy "at" HH AM "on" Dy ddth Mon') as "conventional"
from c;
\x off
This is the result:
proving the point | Starte2 at 9ugab9te 9n 2019 at 09 AM on Mon 22nd Apr
cumbersome | Started at Yugabyte in 2019 at 09 AM on Mon 22nd Apr
conventional | Started at Yugabyte in 2019 at 09 AM on Mon 22nd Apr
If you want to output the double quote character within the free text, then you must escape it with a backslash. The same applies, by extension, if you want to output a backslash. The backslash has no effect when it precedes any other characters. Try this:
select
to_char('2021-05-17'::timestamp, '"Here is the \"year\" c\o\m\p\o\n\e\n\t of a date\\time value:" yyyy');
This is the result:
Here is the "year" component of a date\time value: 2021
There is only one function for this conversion direction, to_char(). Here is the interesting part of the output from \df to_char():
Name | Result data type | Argument data types
---------+------------------+-----------------------------------
to_char | text | interval, text
to_char | text | timestamp with time zone, text
to_char | text | timestamp without time zone, text
(There are also overloads to convert a bigint, double precision, integer, numeric, or real value to a text value. But these are outside the scope of the present date-time major section.)
Notice that there are no overloads to convert a date value or a time value to a text value. But this has no practical consequence because the overload resolution rules look after this. Try this:
drop function if exists f(interval) cascade;
drop function if exists f(timestamp) cascade;
drop function if exists f(timestamptz) cascade;
create function f(t in interval)
returns text
language plpgsql
as $body$
begin
return '"interval" overload: '||t::text;
end;
$body$;
create function f(t in timestamp)
returns text
language plpgsql
as $body$
begin
return 'plain "timestamp" overload: '||t::text;
end;
$body$;
create function f(t in timestamptz)
returns text
language plpgsql
as $body$
begin
return '"timestamptz" overload: '||t::text;
end;
$body$;
set timezone = 'UTC';
select f('2021-03-15'::date);
This is the result:
"timestamptz" overload: 2021-03-15 00:00:00+00
Now try this:
select f('15:00:00'::time);
"interval" overload: 15:00:00
(You can see the rules that bring these outcomes by querying pg_type and pg_cast.)
A date value simply cannot be converted to an interval value. (The attempt causes the 42846 error.) So the interval overload of to_char() can have no effect on the overload selection when a date actual argument is used. Therefore, because to_char() has two moment overloads, one with a plain timestamp formal parameter and the other with a timestamptz formal parameter, if you invoke it with a date actual argument, then the timestamptz overload is selected. As it happens, the overload selection makes no difference to the outcome when you use a natural template for a date value. Try this:
with c as (
select
'2021-06-15'::date as d,
'dd-Mon-yyyy' as fmt)
select
to_char(d, fmt) as "implicit cast to timestamptz",
to_char(d::timestamptz, fmt) as "explicit cast to timestamptz",
to_char(d::timestamp, fmt) as "explicit cast to plain timestamp"
from c;
This is the result:
implicit cast to timestamptz | explicit cast to timestamptz | explicit cast to plain timestamp
------------------------------+------------------------------+----------------------------------
15-Jun-2021 | 15-Jun-2021 | 15-Jun-2021
Now try this:
with c as (
select
'15:00:00'::time as t,
'hh12-mi-ss AM' as fmt)
select
to_char(t, fmt) as "implicit cast to interval",
to_char(t::interval, fmt) as "explicit cast to interval"
from c;
This is the result:
implicit cast to interval | explicit cast to interval
---------------------------+---------------------------
03-00-00 PM | 03-00-00 PM
You might think that it's strange that an interval value can be rendered with AM or PM. But it is what it is.
select to_char(make_interval(hours=>15), 'AM');
This is the result:
PM
Prefer uncluttered code to explicit typecasting with 'to_char()' for 'date' and 'time' values.
Usually, users avoid writing code that brings an implicit typecast and prefer, instead, to write the typecast that they want explicitly. The tests above show that in the case that to_char() is to be used to render a date value or a time value, the result is the same when the implicit typecast (respectively to timestamptz or to interval) is allowed and when the explicit typecast is written. More carefully stated, writing the explicit typecast of a date value either to a plain timestamp value or to a timestamptz value has the same effect as not writing a typecast; and writing the explicit typecast of a time value to an interval value has the same effect as not writing the typecast.
Yugabyte recommends that, for the special case of invoking to_char() with a date or time value, simply to allow the implicit typecast to have its innocent effect. In other words, you should prefer uncluttered code, here, to fastidious adherence to the normal rule of good practice.
From text to date-time
There are just two functions for this conversion direction, to_date() and to_timestamp(). Here is the interesting part of the output from \df to_date():
Result data type | Argument data types
------------------+---------------------
date | text, text
Try this:
select ( to_date('22nd Apr, 2019', 'ddth Mon, yyyy') )::text;
This is the result:
2019-04-22
Here is the interesting part of the output from \df to_timestamp():
Result data type | Argument data types
--------------------------+---------------------
timestamp with time zone | double precision
timestamp with time zone | text, text
The double precision overload is not a formatting function in the sense that this section uses the term. That overload is described in this dedicated subsection within the General-purpose date and time functions section. Arguably, this functionality would have been better implemented as overloads of make_timestamp() and make_timestamptz(), each with a single double precision parameter. But this, simply, is not how it was done.
Notice that there's no built-in function to convert a text value directly to a plain timestamp value. If you want this conversion, then you have to code it explicitly:
set timezone = 'Europe/Helsinki';
with c as (select to_timestamp('15:00 17-May-2021', 'hh24:mi dd-Mon-yyyy')::timestamp as t)
select
pg_typeof(t)::text, t::text
from c;
This is the result:
pg_typeof | t
-----------------------------+---------------------
timestamp without time zone | 2021-05-17 15:00:00
See the subsection timestamptz to plain timestamp on the Typecasting between values of different date-time datatypes page. It explains that, in the exact scenario that's shown here where a text value is converted first to a timestamptz value and then, in the same statement, typecast to a plain timestamp value, the result is insensitive to the value of the session timezone.
In to_date() and to_timestamp(), free text asks simply to skip as many characters as it contains before resuming parsing for actual template patterns in the normal way. The characters that are used to specify the free text, in the to date-time direction are insignificant as long as the first and last are not whitespace. It's easy to show this by a test. First, do this, in the to text direction:
with c as (select '"The date is:"FMDay, DD, FMMonth, Y,YYY'::text as fmt)
select
to_char('2021, 06-15'::date, fmt) as d1,
to_char('2021, 09-18'::date, fmt) as d2
from c;
The FM modifier is explained in the subsection Date-time template pattern modifiers
This is the result:
d1 | d2
--------------------------------------+--------------------------------------------
The date is:Tuesday, 15, June, 2,021 | The date is:Saturday, 18, September, 2,021
Now do it in reverse, in the to date direction:
drop function if exists f(text) cascade;
create function f(d in text)
returns text
language plpgsql
as $body$
begin
-- The date is:
return to_date(d, '"1234567890ab"Day, DD, Month, Y,YYY')::text;
end;
$body$;
select
f('The date is:Tuesday, 15, June, 2,021' ) as d1,
f('The date is:Saturday, 18, September, 2,021') as d2;
This is the result:
d1 | d2
------------+------------
2021-06-15 | 2021-09-18
Now do this exhaustive test:
drop function if exists f(text) cascade;
create function f(d in text)
returns text
language plpgsql
as $body$
declare msg constant text not null := rpad(chr(187)||d||chr(171), 65, '.');
begin
assert
to_date(d, '"1234567890ab"Day, DD, Month, Y,YYY') = '2021-06-15'::date;
return msg||' OK';
exception when invalid_datetime_format then
return msg||' failed';
end;
$body$;
drop function if exists test_results(int) cascade;
create function test_results(pad_len in int)
returns table(z text)
language plpgsql
as $body$
declare
pad constant text not null := rpad(' ', pad_len, ' ');
payload constant text not null := pad||'Tuesday , 15 , June , 2,021 ';
begin
z := f(' 1234567890ab' ||payload); return next;
z := f(' 1 ' ||payload); return next;
z := ''; return next;
z := 'Too long.'; return next;
z := f(' 1234567890abc' ||payload); return next;
z := f(' 1 c' ||payload); return next;
z := ''; return next;
z := 'Too short.'; return next;
z := f(' 1234567890a' ||payload); return next;
z := f(' 1 ' ||payload); return next;
end;
$body$;
select z from test_results(0);
This is the result:
» 1234567890abTuesday , 15 , June , 2,021 «...... OK
» 1 Tuesday , 15 , June , 2,021 «...... OK
Too long.
» 1234567890abcTuesday , 15 , June , 2,021 «..... failed
» 1 cTuesday , 15 , June , 2,021 «..... failed
Too short.
» 1234567890aTuesday , 15 , June , 2,021 «....... failed
» 1 Tuesday , 15 , June , 2,021 «....... failed
You can see three things immediately:
- A run of zero or more spaces at the start of the to-be-interpreted text value has no effect.
- The parsing of the payload starts immediately following the length of the defined free text, counting from its first non-space character.
- The payload may start with a run of zero or more spaces before its first non-space character.
You can confirm this with this new invocation of the test:
select z from test_results(1);
This is the new result:
» 1234567890ab Tuesday , 15 , June , 2,021 «..... OK
» 1 Tuesday , 15 , June , 2,021 «..... OK
Too long.
» 1234567890abc Tuesday , 15 , June , 2,021 «.... failed
» 1 c Tuesday , 15 , June , 2,021 «.... failed
Too short.
» 1234567890a Tuesday , 15 , June , 2,021 «...... OK
» 1 Tuesday , 15 , June , 2,021 «...... OK
The "Too long" tests still fail. but the "Too short" tests now succeed.
Avoid using 'years' substring values less than one to specify BC in 'to_date()' and 'to_timestamp()'.
The section "Usage notes for date/time formatting" on the page "9.8. Data Type Formatting Functions" just under Table 9.25. Template Pattern Modifiers for Date/Time Formatting says this:
In to_timestamp and to_date, negative years are treated as signifying BC. If you write both a negative year and an explicit BC field, you get AD again. An input of year zero is treated as 1 BC.
This seems to be a suspect solution looking for a problem for these reasons:
-
Nobody ever talks about "year zero" in the Gregorian calendar because there's no such year. So code that exploits the feature that "zero is taken to mean 1 BC" will be obscure and, at the very least, will require extensive comments to explain choosing this approach.
-
Nobody ever talks about dates by saying "the year minus 42". They always say "42 BC". So, code that exploits the feature that "year -X means X BC" is, here too, going to need extensive commenting.
-
If you invoke make_timestamp() or make_timestamptz() with a negative (or zero) argument for "year", then you get the 22008 error. (This is the case in PostgreSQL Version 11.2 (the earliest version of interest for users of YugabyteDB). It remains the case through PostgreSQL Version 13. But Version 14 newly allows a negative argument for "year" but still causes the 22008 error with a zero argument for "year".
-
The text-to-date typecast '-2021-06-15'::date gets the 22009 error, as it does with -0000 for the year. This holds through PostgreSQL Version 14.
-
The unary minus operator is shorthand for subtract the operand from zero — i.e. -x means 0 - x. But there is no year zero. And anyway, the difference between two date values is an integer value; and the difference between two timestamp or timestamptz values is an interval value.
-
Finally, the implementation is buggy. This is also the case in PostgreSQL through Version 14—but in subtly different ways on crossing some version boundaries. Try this:
\x on select to_date( '15/06/-2021', 'DD/MM/YYYY' ) as a1, to_date( '15/06/-2021 BC', 'DD/MM/YYYY/AD' ) as a2, '' as "-", to_date( '15 06 -2021', 'DD MM YYYY' ) as b1, to_date( '15 06 -2021 BC', 'DD MM YYYY AD' ) as b2; \x off
Notice that the difference between the first two expressions (that produce the values a1 and a2) and the second two expressions (that produce the values b1 and b2) is how the to-be-converted substrings for DD, MM, and YYYY are separated. Otherwise, they express the same intention. So b1 should be the same as a1 and b2 should be the same as a2. This is the result:
a1 | 2022-06-15 BC
a2 | 2022-06-15 BC
- |
b1 | 2021-06-15
b2 | 2021-06-15 BC
b1 differs from a1 and b2 differs from a2.
Yugabyte therefore recommends that you simply avoid using "years" substring values less than one to specify BC in to_date() and to_timestamp().
Date-time template patterns
This table lists about fifty distinct template patterns. They are ordered in rough order of increasingly coarse granularity.
| Pattern | Description |
|---|---|
| US | Microsecond (000000-999999). See The effect of the MS and US template patterns. |
| MS | Millisecond (000-999). See The effect of the MS and US template patterns. |
| SS | Second (00-59). |
| SSSS | Seconds past midnight (0-86399). |
| MI | Minute (00-59). |
| HH, HH12 | Hour of day (01-12). |
| HH24 | Hour of day (00-23). |
| AM, am, PM, pm | meridiem indicator (without periods). |
| A.M., a.m., P.M., p.m. | Meridiem indicator (with periods). |
| DD | Day of month (01-31). |
| DY | Abbreviated upper case day name (3 chars in English, localized lengths vary). |
| Dy | Abbreviated capitalized day name (3 chars in English, localized lengths vary). |
| dy | Abbreviated lower case day name (3 chars in English, localized lengths vary). |
| DAY | Full upper case day name (blank-padded to 9 chars). |
| Day | Full capitalized day name (blank-padded to 9 chars). |
| day | Full lower case day name (blank-padded to 9 chars). |
| D | Day of the week, Sunday (1) to Saturday (7). |
| ID | ISO 8601 day of the week, Monday (1) to Sunday (7). |
| DDD | Day of year (001-366). |
| IDDD | Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week). |
| W | Week of month (1-5) (the first week starts on the first day of the month). |
| WW | Week number of year (1-53) (the first week starts on the first day of the year). |
| IW | Week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1). |
| MM | Month number (01-12). |
| MON | Abbreviated upper case month name (3 chars in English, localized lengths vary). |
| Mon | Abbreviated capitalized month name (3 chars in English, localized lengths vary). |
| mon | Abbreviated lower case month name (3 chars in English, localized lengths vary). |
| MONTH | Full upper case month name (blank-padded to 9 chars). |
| Month | Full capitalized month name (blank-padded to 9 chars). |
| month | Full lower case month name (blank-padded to 9 chars). |
| RM | Month in upper case Roman numerals (I-XII; I=January). |
| rm | Month in lower case Roman numerals (i-xii; i=January). |
| Q | Quarter. |
| YYYY | Year (4 or more digits). |
| Y,YYY | Year (4 or more digits) with commas. |
| IYYY | ISO 8601 week-numbering year (4 or more digits). |
| YYY | Last 3 digits of year. |
| IYY | Last 3 digits of ISO 8601 week-numbering year. |
| YY | Last 2 digits of year. |
| IY | Last 2 digits of ISO 8601 week-numbering year. |
| Y | Last digit of year. |
| I | Last digit of ISO 8601 week-numbering year. |
| BC, bc, AD, ad | Era indicator (without periods). |
| B.C., b.c., A.D., a.d. | Era indicator (with periods). |
| CC | Century (2 digits) (the twenty-first century starts on 2001-01-01). See The effect of the CC template pattern. |
| TZ | Upper case time-zone abbreviation—supported only in to_char(). |
| tz | Lower case time-zone abbreviation—supported only in to_char(). |
| TZH | Time-zone hours. |
| TZM | Time-zone minutes. |
| OF | Time-zone offset from UTC—supported only in to_char(). |
| J | Julian Date (integer days since November 24, 4714 BC at local midnight; see B.7. Julian Dates in Appendix B. Date/Time Support of the PostgreSQL documentation). |
Create and execute the template_pattern_results() table function to demonstrate the effect of almost all of the template patterns. When a few different template patterns simply produce different upper/lower case mixtures of their resulting text, only the one that produces the init-cap variant is used. The function uses three overloads of a formatting function f(). This is a simple technique to reduce repetition and clutter in the template_pattern_results() function itself.
Do this:
drop function if exists template_pattern_results() cascade;
drop function if exists f(text) cascade;
drop function if exists f(timestamp, text) cascade;
drop function if exists f(timestamptz, text) cascade;
create function f(str in text)
returns text
language plpgsql
as $body$
begin
return rpad(str||':', 20);
end;
$body$;
create function f(t in timestamp, tmplt in text)
returns text
language plpgsql
as $body$
begin
return rpad(to_char(t, tmplt), 9);
end;
$body$;
create function f(t in timestamptz, tmplt in text)
returns text
language plpgsql
as $body$
begin
return rpad(to_char(t, tmplt), 9);
end;
$body$;
create function template_pattern_results()
returns table(z text)
language plpgsql
as $body$
declare
t constant text not null := '2021-09-17 15:47:41.123456';
ts constant timestamp not null := t;
tstz constant timestamptz not null := t||' UTC';
set_timezone constant text not null := $$set timezone = '%s'$$;
tz_on_entry constant text not null := current_setting('timezone');
begin
execute format(set_timezone, 'Asia/Kathmandu');
z := f('Seconds') ||
' US: '||f(ts, 'US') ||
' | MS: '||f(ts, 'MS') ||
' | SS: '||f(ts, 'SS') ||
' | SSSS: '||f(ts, 'SSSS') ||
' |' ; return next;
z := f('Minutes, hours') ||
' MI: '||f(ts, 'MI') ||
' | HH: '||f(ts, 'HH') ||
' | HH12: '||f(ts, 'HH12') ||
' | HH24: '||f(ts, 'HH24') ||
' | AM: '||f(ts, 'AM') ||
' | SSSS: '||f(ts, 'SSSS') ||
' |' ; return next;
z := f('Day') ||
' DD: '||f(ts, 'DD') ||
' | Dy: '||f(ts, 'Dy') ||
' | Day: '||f(ts, 'Day') ||
' | D: '||f(ts, 'D') ||
' | ID: '||f(ts, 'ID') ||
' | DDD: '||f(ts, 'DDD') ||
' | IDDD: '||f(ts, 'IDDD') ||
' |' ; return next;
z := f('Week') ||
' W: '||f(ts, 'W') ||
' | WW: '||f(ts, 'WW') ||
' | IW: '||f(ts, 'IW') ||
' |' ; return next;
z := f('Month and quarter') ||
' MM: '||f(ts, 'MM') ||
' | Mon: '||f(ts, 'Mon') ||
' | Month: '||f(ts, 'Month') ||
' | RM: '||f(ts, 'RM') ||
' | Q: '||f(ts, 'Q') ||
' |' ; return next;
z := f('Year and century') ||
' Y,YYY: '||f(ts, 'Y,YYY') ||
' | IYYY: '||f(ts, 'IYYY') ||
' | YYY: '||f(ts, 'YYY') ||
' | YY: '||f(ts, 'YY') ||
' | Y: '||f(ts, 'Y') ||
' | BC: '||f(ts, 'BC') ||
' | CC: '||f(ts, 'CC') ||
' |' ; return next;
z := f('Timezone') ||
' TZ: '||f(tstz, 'TZ') ||
' | TZH: '||f(tstz, 'TZH') ||
' | TZM: '||f(tstz, 'TZM') ||
' | OF: '||f(tstz, 'OF') ||
' |' ; return next;
z := f('Julian Date') ||
' J: '||f(tstz, 'J') ||
' |' ; return next;
execute format(set_timezone, tz_on_entry);
end;
$body$;
select z from template_pattern_results();
This is the result:
Seconds: US: 123456 | MS: 123 | SS: 41 | SSSS: 56861 |
Minutes, hours: MI: 47 | HH: 03 | HH12: 03 | HH24: 15 | AM: PM | SSSS: 56861 |
Day: DD: 17 | Dy: Fri | Day: Friday | D: 6 | ID: 5 | DDD: 260 | IDDD: 257 |
Week: W: 3 | WW: 38 | IW: 37 |
Month and quarter: MM: 09 | Mon: Sep | Month: September | RM: IX | Q: 3 |
Year and century: Y,YYY: 2,021 | IYYY: 2021 | YYY: 021 | YY: 21 | Y: 1 | BC: AD | CC: 21 |
Timezone: TZ: +0545 | TZH: +05 | TZM: 45 | OF: +05:45 |
Julian Date: J: 2459475 |
If you use the timezone template patterns with a plain timestamp value, then they silently show a zero offset. Try this:
deallocate all;
prepare stmt as
select
current_setting('timezone') as "session timezone",
to_char('2021-06-15 12:00:00'::timestamp, 'dd-Mon-yyyy hh24:mi:ss TZH:TZM') as "ts";
set timezone = 'America/Los_Angeles';
execute stmt;
set timezone = 'Europe/Helsinki';
execute stmt;
Both executions report +00:00 for TZH:TZM.
Date-time template pattern modifiers
| Modifier | Description | Example |
|---|---|---|
| FM prefix | fill mode (suppress leading zeroes and padding blanks) | FMMonth |
| TM prefix | translation mode (print localized day and month names based on lc_time) | TMMonth |
| TH suffix | upper case ordinal number suffix | DDTH, e.g., 12TH |
| th suffix | lower case ordinal number suffix (always in Emglish form) | DDth, e.g., 12th |
| FX prefix | fixed format global option (see usage notes) | FX Month DD Day |
For more information on lc_time, see the section Typecasting between date-time values and text values. Briefly, the list of acceptable values is sensitive to your operating system environment. On Unix-like systems, this command:
locale -a
lists the values that are defined there.
Usage notes
This section explains clarifying detail for the use of those template patterns and template pattern modifiers whose effect isn't self-evident.
The FM modifier
FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. Try this:
with c as (select '0020-05-03 BC'::timestamp as t)
select
to_char(t, 'MMth "month ("Month")", DDth "day", YYYY AD') as "plain",
to_char(t, 'FMMMth "month ("FMMonth")", FMDDth "day", FMYYYY AD') as "using FM"
from c;
This is the result:
plain | using FM
-------------------------------------------+---------------------------------
05th month (May ), 03rd day, 0020 BC | 5th month (May), 3rd day, 20 BC
The TM modifier
The TM modifier affects only the rendering of the full and abbreviated day and month names, in the to text direction using to_char(). Because these fields don't affect the resulting value in the to date-time direction, TM is ignored by to_date() and to_timestamp(). Not only does TM determine the national language that is used for day and month names and abbreviations, but it also has the side effect of suppressing trailing blanks. Notice that TM has no effect on how the ordinal number suffix is rendered. This is inevitably the English rendition (-st, -nd, or -th). Try this:
deallocate all;
prepare stmt as
select
to_char('2021-02-01'::timestamp, 'Day, ddth Month, y,yyy') as "plain",
to_char('2021-02-01'::timestamp, 'TMDay, ddth TMMonth, y,yyy') as "with TM";
set lc_time = 'en_US';
execute stmt;
set lc_time = 'it_IT';
execute stmt;
set lc_time = 'fr_FR';
execute stmt;
set lc_time = 'fi_FI';
execute stmt;
Here are the results:
Monday , 01st February , 2,021 | Monday, 01st February, 2,021
Monday , 01st February , 2,021 | Lunedì, 01st Febbraio, 2,021
Monday , 01st February , 2,021 | Lundi, 01st Février, 2,021
Monday , 01st February , 2,021 | Maanantai, 01st Helmikuu, 2,021
The FX modifier
The to_date() and to_timestamp() functions treat runs of spaces as a single space in the to-be-converted text value unless the FX modifier is used. Try this:
set timezone = 'UTC';
select to_timestamp('2000 JUN', 'YYYY MON')::text;
It runs without error and produces the expected result:
2000-06-01 00:00:00+00
Now try this:
set timezone = 'UTC';
select to_timestamp('2000 JUN', 'FXYYYY MON')::text;
It causes the 22007 error because to_timestamp() expects one space only:
invalid value " " for "MON"
Template patterns with just YYY, YY, or Y
With to_date() and to_timestamp(), if the putative "years" substring in the to-be-converted text value is fewer than four digits and the template pattern is YYY, YY, or Y, then the "years" substring is left-padded with digits to produce the four-digit string that, among all possible choices, is closest to 2020. In the case of the tie between 1970 and 2070, 1970 is deemed to be closest.
Try this:
with c as (
select '15-Jun-100'::text as t1, '15-Jun-999'::text as t2)
select
to_date(t1, 'dd-Mon-YYYY') ::text as "t1 using YYYY",
to_date(t2, 'dd-Mon-YYYY') ::text as "t2 using YYYY",
to_date(t1, 'dd-Mon-YYY') ::text as "t1 using YYY",
to_date(t2, 'dd-Mon-YYY') ::text as "t2 using YYY"
from c;
This is the result:
t1 using YYYY | t2 using YYYY | t1 using YYY | t2 using YYY
---------------+---------------+--------------+--------------
0100-06-15 | 0999-06-15 | 2100-06-15 | 1999-06-15
Among the candidate values for 100, 2100 is closer to 2020 than is 1100 or 3100. And among the candidate values for 999, 1999 is closer to 2020 than is 0999 or 2999.
The result is the same when either YY or Y is used in place of YYY.
Notice that this brings a challenge if you want to interpret '6781123'::text as '0678-11-23'::date. Try this:
select
to_date('6781123', 'YYYYMMDD') as "1st try",
to_date('6781123', 'YYYMMDD') as "2nd try",
to_date('678-11-23', 'YYYY-MM-DD') as "workaround";
This is the result:
1st try | 2nd try | workaround
------------+------------+------------
6781-12-03 | 1678-11-23 | 0678-11-23
Neither the first nor the second attempt brings what you want. Your only option is to require (or to get this by pre-parsing yourself) that the input has separators between the substrings that are to be interpreted as YYYY, MM, and DD, like the workaround shows.
Interpreting text values where the "years" substring has more than four digits
You can use to_date() and to_timestamp() to convert a text value like '21234-1123' with the template 'YYYY-MMDD' without error. And you can also use to_date() and to_timestamp() to convert a text value like '21231123' with the template 'YYYYMMDD' without error. Try this:
select
to_date('21234-1123', 'YYYY-MMDD') ::text as d1,
to_date('21231123', 'YYYYMMDD') ::text as d2;
This is the result:
d1 | d2
-------------+------------
21234-11-23 | 2123-11-23
But you simply cannot write a template that lets
you use to_date() and to_timestamp() to convert a text value like '212341123' . Try this:
select to_date('212341123', 'YYYYMMDD');
It causes the 22008 error with this unhelpful message:
date/time field value out of range: "212341123"
And it doesn't help if you use an extra Y to change 'YYYYMMDD' to 'YYYYYMMDD'. Now it complains about the template with the 22007 thus:
conflicting values for "Y" field in formatting string
This value contradicts a previous setting for the same field type.
Your only option is to ensure that the "years" substring in the to-be-converted text value is terminated with a non-numeric character and that the template matches this—like in the previous code example that used to_date('21234-1123', 'YYYY-MMDD').
The effect of the CC template pattern
With to_date() and to_timestamp(), the CC template pattern is accepted but ignored if there is a YYY, YYYY or Y,YYY pattern. If CC is used with either of the YY or the Y patterns, then the result is computed as that year in the specified century. If the century is specified but the year is not, the first year of the century is assumed.
Try this:
select
to_date('19 2021', 'CC YYYY') as "result 1",
to_date('19 21', 'CC YY') as "result 2",
to_date('19', 'CC') as "result 3";
This is the result:
result 1 | result 2 | result 3
------------+------------+------------
2021-01-01 | 1821-01-01 | 1801-01-01
The templates used in to_timestamp() and to_date() allow mutually contradictory overspecification
Try this:
with
c1 as (
select
'DD-Mon-YYYY' as input_tmplt_1,
'Day, DD-Mon-YYYY' as input_tmplt_2,
'FMDay, DD-Mon-YYYY' as output_tmplt),
c2 as (
select
to_date( '20-May-2019', input_tmplt_1) as d1,
to_date('Wednesday, 20-May-2019', input_tmplt_2) as d2,
output_tmplt
from c1)
select
to_char(d1, output_tmplt) "result date",
(d1 = d2)::text as "d1 = d2"
from c2;
This is the result:
Monday, 20-May-2019 result date | d1 = d2
---------------------+---------
Monday, 20-May-2019 | true
Notice that the input text value has Wednesday but 20-May-2019 is actually a Monday— a mutually contradictory overspecification. Further, the input Wednesday has nine characters but the template pattern Day prescribes values of a variable length from the shortest, Monday (six characters) to the longest, Wednesday. Nevertheless, the input is accepted without error.
The rule that mutually contradictory overspecification is acceptable holds for all such substrings whose values are determined by the date itself (like Day, D, ID, Mon, Month, and Q). You will need to rely on this tolerance, and on the character count tolerance, if you're ingesting text that happens to include, for example full day names or full month names because these substrings have a variable length and so simply using fixed-length dummy free text like "?????" won't work. Tty this:
with c as (select 'DD-Month-YYYY' as templt)
select
to_date('27-May-2021', templt)::text as d1,
to_date('18-September-2021', templt)::text as d2
from c;
This is the result:
d1 | d2
------------+------------
2021-05-27 | 2021-09-18
Avoid mixing "ISO 8601 week-numbering" patterns and "Gregorian date" patterns in the same template
With to_date() and to_timestamp(), an ISO 8601 week-numbering date (as distinct from a Gregorian date) can be specified in one of two ways:
-
You can specify the ISO 8601 week-numbering year, the week number of the ISO 8601 week-numbering year, and the ISO 8601 day of the week. Try this:
select to_char(to_date('2006-42-4', 'IYYY-IW-ID'), 'Dy dd-Mon-YYYY');This is the result:
Thu 19-Oct-2006If you omit the weekday it is assumed to be 1 (Monday). Try this:
select to_char(to_date('2006-42', 'IYYY-IW'), 'Dy dd-Mon-YYYY');This is the result:
Mon 16-Oct-2006 -
Specifying just the ISO 8601 week-numbering year and the day of the ISO 8601 week-numbering year is also sufficient to determine the resulting date value.
Try this:
select to_char(to_date('2006-291', 'IYYY-IDDD'), 'Dy dd-Mon-YYYY');This is the result:
Thu 19-Oct-2006
The attempt to interpret a text value as a date-time value using a mixture of ISO 8601 week-numbering patterns and Gregorian date patterns is nonsensical. Try this:
-- ISO "2006-291" is the same as Gregorian "2006-10-19"
select to_date('2006-291 2006-10-19', 'IYYY-IDDD YYYY-MM-DD');
It causes the 22007 error:
invalid combination of date conventions
Do not mix Gregorian and ISO week date conventions in a formatting template.
In the context of the ISO 8601 year-, week- and day-numbering scheme, the concept of a "month" or a "day of the month" has no meaning; and in the context of the Gregorian calendar, the ISO scheme has no meaning.
The effect of the MS and US template patterns in the to date-time direction
Create a table function to demonstrate the effect of the MS and US template patterns:
-- Trivial wrapper for "rpad(t, 30)" to reduce clutter in the main code.
drop function if exists w(text) cascade;
create function w(t in text)
returns text
language plpgsql
as $body$
begin
return rpad(t, 30);
end;
$body$;
drop function if exists effect_of_ms_and_us() cascade;
create function effect_of_ms_and_us()
returns table(z text)
language plpgsql
as $body$
declare
t0 constant text not null := '2021-06-15 13:17:47';
t1 constant text not null := t0||'.1';
t2 constant text not null := t0||'.12';
t3 constant text not null := t0||'.123';
t4 constant text not null := t0||'.1234';
t5 constant text not null := t0||'.12345';
t6 constant text not null := t0||'.123456';
x constant text not null := 'YYYY-MM-DD HH24:MI:SS';
x_0 constant text not null := x;
x_ms constant text not null := x||'.MS';
x_us constant text not null := x||'.US';
begin
z := 'Input "text" value Resulting "date" value'; return next;
z := '-------------------------- -----------------------------'; return next;
z := w(t0)||to_timestamp(t0, x_0); return next;
z := w(t1)||to_timestamp(t1, x_ms); return next;
z := w(t2)||to_timestamp(t2, x_ms); return next;
z := w(t3)||to_timestamp(t3, x_ms); return next;
z := w(t4)||to_timestamp(t4, x_us); return next;
z := w(t5)||to_timestamp(t5, x_us); return next;
z := w(t6)||to_timestamp(t6, x_us); return next;
end;
$body$;
Execute it thus:
set timezone = 'UTC';
select z from effect_of_ms_and_us();
This is the result:
Input "text" value Resulting "date" value
-------------------------- -----------------------------
2021-06-15 13:17:47 2021-06-15 13:17:47+00
2021-06-15 13:17:47.1 2021-06-15 13:17:47.1+00
2021-06-15 13:17:47.12 2021-06-15 13:17:47.12+00
2021-06-15 13:17:47.123 2021-06-15 13:17:47.123+00
2021-06-15 13:17:47.1234 2021-06-15 13:17:47.1234+00
2021-06-15 13:17:47.12345 2021-06-15 13:17:47.12345+00
2021-06-15 13:17:47.123456 2021-06-15 13:17:47.123456+00
This certainly honors the input text values.
Now edit the source text of effect_of_ms_and_us() to change the definitions if x_0 and x_ms to make all three definitions identical thus:
x_0 constant text not null := x||'.US';
x_ms constant text not null := x||'.US';
x_us constant text not null := x||'.US';
and re-execute the table function. The result is unchanged.
You can simply use the same template for both the text value to date-time value conversion and for the date-time value to text value conversion. It's just that the conversion from text value needs more explanation than the conversion to text value. Try this:
with c as (
select
'YYYY-MM-DD HH24:MI:SS.US' as tmplt)
select
to_char(to_timestamp('2021-06-15 13:17:47.123456', tmplt), tmplt)
from c;
This is the result:
2021-06-15 13:17:47.123456
The input text value has been exactly recreated in the output.
Always use 'SS.US' in to_timestamp().
Yugabyte recommends that you Always use SS.US in to timestamp() when your input text value has a "seconds" substring. The demonstration above shows that specifying US (for microseconds) has no harmful effect over all range of possible trailing digits after the decimal point, from zero through the maximum supported precision of six.
If you use SS.MS throughout, then input values that have four, five, or six digits after the decimal point for the "seconds" substring will cause the 22008 error:
date/time field value out of range: ...
And if you use plain SS throughout, then all values for the "seconds" substring will be accepted; but the digits after the decimal point will be ignored.
It's very hard to see how using anything other than SS.US can be useful. But if you're convinced that your use-case needs this, then you should document your reasons carefully.
Notice that, with contrived input text values, you can use both MS and US in the same template. Try this:
select to_timestamp('2021-06-15 13:17:47:123:123456', 'YYYY-MM-DD HH24:MI:SS:MS:US')::text;
This is the result:
2021-06-15 13:17:47.246456+00
The milliseconds substring, 123, and the microseconds substring, 123346, have simply been added together to produce 246456 microseconds. It's harder still to see how using both MS and US in the same template might be useful.
D in to_char() is a very similar idea to dow in extract() but they're out of step by one day
Notice the descriptions of the D and ID patterns from the table in the subsection Date-time template patterns above:
| Pattern | Description |
|---|---|
| D | Day of the week, Sunday (1) to Saturday (7). |
| ID | ISO 8601 day of the week, Monday (1) to Sunday (7). |
And notice the descriptions of the dow and isodow keywords from the table in the subsection List of keywords on the Function extract() | date_part() returns double precision page:
| Keyword | Description |
|---|---|
| dow | The day of the week as Sunday (0) to Saturday (6). |
| isodow | The day of the week as Monday (1) to Sunday (7). |
The template pattern D for the built-in functions to_char(), to_date() and to_timestamp(), and the keyword dow for the built-in function extract() and its alternative date_part() syntax, express the same idea. But they differ in convention. In the former, the range is [1, 7] where Sunday is day number 1; and in the latter, the range is [0, 6], and Sunday is day number 0. You have no choice but to know this and to beware.
In contrast, the template pattern ID and the keyword isodow express the same idea and agree in convention.
Try this:
with c as (select to_date('19-Oct-2006', 'dd-Mon-yyyy') as d)
select
to_char(d, 'D') as "'D' value",
date_part('dow', d)::text as "'dow' value",
to_char(d, 'ID') as "'ID' value",
date_part('isodow', d)::text as "'isodow' value"
from c;
This is the result:
'D' value | 'dow' value | 'ID' value | 'isodow' value
-----------+-------------+------------+----------------
5 | 4 | 4 | 4
Don't use HH or HH12 in to_char() with an interval value
The ss field of the internal representation of an interval value can be very big.
Try this:
with c as (
select
make_interval(hours=>10000) as i1,
make_interval(hours=>-15) as i2)
select
to_char(i1, 'HH24:MI:SS') as "Natural use of HH24",
to_char(i1, 'HH12:MI:SS') as "Strange use of HH12",
to_char(i2, 'HH24:MI:SS') as "Natural use of HH24",
to_char(i2, 'HH12:MI:SS') as "Strange use of HH12"
from c;
This is the result:
Natural use of HH24 | Strange use of HH12 | Natural use of HH24 | Strange use of HH12
---------------------+---------------------+---------------------+---------------------
10000:00:00 | 04:00:00 | -15:00:00 | -03:00:00
Now try this:
with c as (
select
'00:00:00.000001'::interval as delta,
'01:00:00.000000'::interval as one,
'12:59:59.999999'::interval as almost_thirteen,
'13:00:00.000000'::interval as thirteen)
select
to_char((one - delta), 'HH12:MI:SS.US') as "one - delta",
to_char(one, 'HH12:MI:SS.US') as one,
to_char(almost_thirteen, 'HH12:MI:SS.US') as almost_thirteen,
to_char(thirteen, 'HH12:MI:SS.US') as thirteen
from c;
You can see that using HH12 constrains the absolute value of the rendered "hours", h to satisfy this rule:
1 ≤ h < 13
This is the closed-open range [1, 13). Such is the convention of the twelve-hour clock!
In other words, it's very hard to see how the HH12 template pattern (and its synonym HH pattern) can be useful for rendering an interval value. In contrast, the HH24 template pattern is exactly what you need.