On 10/21/21 17:42, Bryn Llewellyn wrote:
>> On 21-Oct-2021, at 17:15, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 10/21/21 15:45, Bryn Llewellyn wrote:
>>>> /Adrian Klaver wrote:/
>>>>
>>> *Why is there no "date" overload of "to_char()"?*
>
> 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,
thenusing "to_char()" on a "date" value requires writing an explicit typecast. There are only two possible choices:
castto plain "timestamp" or cast to "timestamptz". And for reasons that I'm sure you'd explain better than I would, the
choicemakes 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
_withoutwriting a typecast_ and then letting the implicit conversion (which we know is to "timestamptz") have its
innocenteffect?
>
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.
--
Adrian Klaver
adrian.klaver@aklaver.com