Re: Yet Another Timestamp Question: Time Defaults

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> If I have learned anything about dealing with dates and times, is that
> it is a set of exceptions bound together by a few rules. Every time you
> think you have the little rascals cornered, one gets away.

Yeah, that's for sure. Anyway, I think we are exceedingly unlikely to
adopt Gavan's suggestion. It would break a huge amount of existing
application code, and I think it is also arguably contrary to the SQL
standard. The standard doesn't specify (at least, not that I've found)
the external representation of datatype values; but it does specify what
they're supposed to look like within literal constants in SQL commands.
At least in SQL92 and SQL99 (too lazy to look at other versions right
now), a timestamp literal that omits the time-of-day part is flat out
illegal:

<unquoted date string> ::= <date value>

<unquoted time string> ::=
<time value> [ <time zone interval> ]

<unquoted timestamp string> ::=
<unquoted date string> <space> <unquoted time string>

Note the lack of square brackets there. The only way that you can
really reconcile the spec with using just a <date value> in timestamp
input is to suppose that the input is meant as a date and then we apply
an implicit cast to timestamp. However, the spec definitely has an
opinion on the meaning of such a cast. In 6.22 <cast specification>,
SD and TD are the source and target datatypes for a cast, SV and TV are
the source and target values:

17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE,
then let TSP be the <timestamp precision> of TD.

b) If SD is a date, then the <primary datetime field>s hour,
minute, and second of TV are set to 0 (zero) and the <primary
datetime field>s year, month, and day of TV are set to their
respective values in SV.

18) If TD is the datetime data type TIMESTAMP WITH TIME ZONE, then
let TSP be the <time precision> of TD.

b) If SD is a date, then TV is:

CAST (CAST (SV AS TIMESTAMP(TSP) WITHOUT TIME ZONE)
AS TIMESTAMP(TSP) WITH TIME ZONE)

(the behavior of that is defined as a timezone rotation)

So it seems to me that the spec is pretty clearly on the side of filling
in zeroes, ie local midnight.

Now, you might say that there's an easy way around both the application
breakage and the spec-compliance objections: let's just define a new GUC
parameter that selects the behavior, with a backwards-compatible default
setting. And ten years ago, I'd have probably said "hey, that's a great
idea". But one of the things I've learned as the project goes along is
that GUCs that affect application-visible semantics are dangerous
things. Robust application code has to be made to cope with any
possible setting of such a GUC, which makes them not nearly such a cheap
fix as they seem initially. Especially not if the behavioral change is
silent, with no possibility of detecting or reporting an error if the
application is not expecting the new behavior.

regards, tom lane