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@xxxxxxxxxxx
