--- title: "Date and time formatting functions" url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/formatting-functions/" --- # Date and time formatting functions Describes the date and time formatting functions. [YSQL] 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](#date-time-template-patterns "Date-time template patterns"). The effects of these template patterns, again in turn, can be modified. See the section [Date-time template pattern modifiers](#date-time-template-pattern-modifiers "Date-time template pattern modifiers"). Here's a basic example. It uses all of the relevant functions. ```plpgsql 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: ```output 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](/stable/api/ysql/datatypes/type_datetime/functions/ "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*](#from-text-to-date-time "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: ```plpgsql \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: ```output 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: ```plpgsql 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: ```output 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()*: ```output 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: ```plpgsql 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: ```output "timestamptz" overload: 2021-03-15 00:00:00+00 ``` Now try this: ```plpgsql select f('15:00:00'::time); ``` ```output "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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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. ```plpgsql select to_char(make_interval(hours=>15), 'AM'); ``` This is the result: ```output 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()*: ```output Result data type | Argument data types ------------------+--------------------- date | text, text ``` Try this: ```plpgsql select ( to_date('22nd Apr, 2019', 'ddth Mon, yyyy') )::text; ``` This is the result: ```output 2019-04-22 ``` Here is the interesting part of the output from \\*df to\_timestamp()*: ```output 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](/stable/api/ysql/datatypes/type_datetime/functions/creating-date-time-values/#function-to-timestamp-returns-timestamptz "dedicated subsection") within the [General-purpose date and time functions](/stable/api/ysql/datatypes/type_datetime/functions/ "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: ```plpgsql 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: ```output pg_typeof | t -----------------------------+--------------------- timestamp without time zone | 2021-05-17 15:00:00 ``` See the subsection [*timestamptz* to plain *timestamp*](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-values/#timestamptz-to-plain-timestamp "timestamptz to plain timestamp") on the [Typecasting between values of different *date-time* data types](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-values/ "Typecasting between values of different date-time data types") 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: ```plpgsql 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](#date-time-template-pattern-modifiers "Date-time template pattern modifiers") This is the result: ```output 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: ```plpgsql 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: ```output d1 | d2 ------------+------------ 2021-06-15 | 2021-09-18 ``` Now do this exhaustive test: ```plpgsql 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: ```output » 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: ```plpgsql select z from test_results(1); ``` This is the new result: ```outout » 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](https://www.postgresql.org/docs/15/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE "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: ```outout 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](#the-effect-of-the-ms-and-us-template-patterns "The effect of the MS and US template patterns"). | | *MS* | Millisecond (000-999). See [The effect of the MS and US template patterns](#the-effect-of-the-ms-and-us-template-patterns "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](#the-effect-of-the-cc-template-pattern "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](https://www.postgresql.org/docs/15/datetime-julian-dates.html "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: ```plpgsql 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: ```output 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: ```plpgsql 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](#usage-notes "usage notes")) | FX Month DD Day | For more information on *lc\_time*, see the section [Typecasting between date-time values and text values](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-and-text/ "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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql set timezone = 'UTC'; select to_timestamp('2000 JUN', 'YYYY MON')::text; ``` It runs without error and produces the expected result: ```output 2000-06-01 00:00:00+00 ``` Now try this: ```plpgsql set timezone = 'UTC'; select to_timestamp('2000 JUN', 'FXYYYY MON')::text; ``` It causes the *22007* error because *to\_timestamp()* expects one space only: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql select to_date('21234-1123', 'YYYY-MMDD') ::text as d1, to_date('21231123', 'YYYYMMDD') ::text as d2; ``` This is the result: ```output 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: ```plpgsql select to_date('212341123', 'YYYYMMDD'); ``` It causes the *22008* error with this unhelpful message: ```output 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql select to_char(to_date('2006-42-4', 'IYYY-IW-ID'), 'Dy dd-Mon-YYYY'); ``` This is the result: ```output Thu 19-Oct-2006 ``` If you omit the weekday it is assumed to be 1 (Monday). Try this: ```plpgsql select to_char(to_date('2006-42', 'IYYY-IW'), 'Dy dd-Mon-YYYY'); ``` This is the result: ```output 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: ```plpgsql select to_char(to_date('2006-291', 'IYYY-IDDD'), 'Dy dd-Mon-YYYY'); ``` This is the result: ```output 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: ```plpgsql -- 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: ```output 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: ```plpgsql -- 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: ```plpgsql set timezone = 'UTC'; select z from effect_of_ms_and_us(); ``` This is the result: ```output 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: ```plpgsql 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: ```plpgsql 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: ```output 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: ```output 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: ```plpgsql select to_timestamp('2021-06-15 13:17:47:123:123456', 'YYYY-MM-DD HH24:MI:SS:MS:US')::text; ``` This is the result: ```output 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](#date-time-template-patterns "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](/stable/api/ysql/datatypes/type_datetime/functions/miscellaneous/extract/#list-of-keywords "List of keywords") on the [Function extract() | date\_part() returns double precision](/stable/api/ysql/datatypes/type_datetime/functions/miscellaneous/extract/ "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: ```plpgsql 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: ```output '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](/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-interval/interval-representation/ "internal representation of an interval value") can be very big. Try this: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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.