Re: Looking for a doc section that presents the overload selection rules

On 10/22/21 10:26 AM, Bryn Llewellyn wrote:
> //

> I’ll make this my final turn on this thread. Yes, I accept that 
> everything to do with the date-time story is tough stuff. And I do 
> understand that this is, to a large extent, just a reflection of the 
> fact that the terrain is inevitably affected by genuine 
> astronomical facts together with the history of technology and human 
> thought. I accept, too, that the PostgreSQL implementation in this space 
> is constrained, to some extent, by decisions taken by the SQL Standard 
> folks, over the years, some of which were arguably questionable. 
> Then PostgreSQL brings its own quirks (esp. e.g. everything to do with 
> intervals and their use). So I do see that application code will need a 
> lot of commenting to make the programmer’s intent clear.
> 
> Having said all this, the following example seems to me to make an 
> unassailable point:
> 
> deallocate all;
> prepare s as
> with c as (
>    select
>      '2021-06-15'         ::date        as   d,
>      '2021-06-15 12:00:00'::timestamp   as   ts,
>      '2021-06-15 12:00:00'::timestamptz as tstz)
> select
>    rpad(current_setting('timezone'), 20)  as "timezone",
>    d   ::text,
>    ts  ::text,
>    tstz::text
> from c;
> 
> \t on
> set timezone = 'Europe/Helsinki';
> execute s;
> 
> set timezone = 'America/Los_Angeles';
> execute s;
> \t
> 
> This is the result:
> 
>   Europe/Helsinki      | 2021-06-15 | 2021-06-15 12:00:00 | 2021-06-15 
> 22:00:00+03
> 
>   America/Los_Angeles  | 2021-06-15 | 2021-06-15 12:00:00 | 2021-06-15 
> 12:00:00-07
> 
> So the “::text” typecast operator understands that “date” values, plain 
> “timestamp” values, and “timestamptz” values each bear different 
> information—and it takes account, in each case, only of the relevant 
> information.
> 
> There could, so easily, have been three “to_char()” overloads for these 
> three data types that honored the spirit of the “::text” typecast by 
> rendering only what’s meaningful, despite what the template asks for. I 
> could write these myself and use “extract()” to get the meaningful 
> elements, and only these, before rendering them, silently refusing the 
> request to render meaningless fields.

Why? You asked for information in the template that is available and it 
provides it.

It does not seem to be that out of line.

 From different realm(Python):

from datetime import date

today_date = date.today()
print(today_date) 
 

2021-10-22

today_date.strftime('%c') 
 

'Fri Oct 22 00:00:00 2021'



It might also be useful to know that to_char() and friends are modeled 
after the Oracle ones:

src/backend/utils/adt/formatting.c

  The PostgreSQL routines for a timestamp/int/float/numeric formatting,
  inspired by the Oracle TO_CHAR() / TO_DATE() / TO_NUMBER() routines.

> 
> However, reality is different. “to_char()” uses defaults when the value 
> at hand doesn’t represent these and then renders them as the template 
> specifies.
> 
> prepare s as
> with c as (
>    select
>      '2021-06-15 12:00:00'::text as t,
>      'dd-Mon-yyyy TZH:TZM'       as fmt)
> select
>    to_char(t::timestamp,   fmt) as "plain timestamp",
>    to_char(t::timestamptz, fmt) as "timestamptz"
> from c;
> 
> This always shows the “TZH:TZM” component of the plain “timestamp” as 
> “00:00”. And for the “timestamptz” value, it shows this to reflect the 
> session’s timezone setting.
> 
> This informs what you get when you want to render a “date” value—and how 
> to code it. Of course, when I do this, I wouldn’t ask to see time-of-day 
> or timezone fields. So fair enough, I suppose. Anyway, so it is—and so 
> it ever will be.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com