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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

adrian.klaver@xxxxxxxxxxx wrote:

Bryn wrote:

adrian.klaver@xxxxxxxxxxx wrote:

Bryn wrote:

Adrian Klaver wrote:
...

You've lost me entirely here, I'm afraid.
My question was simple: why is there no "to_char ( date, text ) → text" overload?

Because there is:

to_char ( timestamp with time zone, text ) → text

and date is a subset of timestamp(tz) where timestamptz is the preferred form.  This allows you to do things like:

select '10/30/21 22:00:00'::timestamptz - '10/22/21'::date;
   ?column?
-----------------
8 days 22:00:00

Without this, and as long as the good practice rule is followed to code so that implicit conversion is never invoked, then using "to_char()" on a "date" value requires writing an explicit typecast. There are only two possible choices: cast to plain "timestamp" or cast to "timestamptz". And for reasons that I'm sure you'd explain better than I would, the choice makes no actual difference to the outcome when a template is used that's natural for a "date" value.
So the consequence is that you have to write cluttered code and a fairly elaborate comment to say what your intention is.

Welcome to date/times. As I have said before if you are looking for end to end consistency you are in the wrong place. It is an area where specific comments are needed to explain your choices.

I am not understanding the template reference as there is a difference in output between timestamp and timestamptz.

What would you do here? Would you break the rule of practice so that you simply invoke "to_char()" on a "date" value _without writing a typecast_ and then letting the implicit conversion (which we know is to "timestamptz") have its innocent effect?

Working in timestamptz is a benefit so I'm fine with that being the default.

The bottom line is that working correctly with date/times is difficult and requires effort spent studying the underlying structure. More so when you switch between systems e.g. _javascript_ 0 indexed months.

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.

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.