Re: Yet Another Timestamp Question: Time Defaults

Adrian Klaver wrote:

> I see where my confusion lies. There are two proposals at work in the above:
>
> "Taking another tangent I would much prefer the default time to be
> 12:00:00 for the conversion of a date to timestamp(+/-timezone)"
>
> "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00 "
>
> For the timestamp(alias for timestamp without time zone) case the date
> does not change. For timestamp with time zone it might.

Well, the big problem here is in trying to use either version of
timestamp when what you really want is a date. It will be much
easier to get the right semantics if you use the date type for a
date.

-Kevin

On 01/21/2013 05:06 PM, Kevin Grittner wrote:
> Adrian Klaver wrote:
>
>> I see where my confusion lies. There are two proposals at work in the above:
>>
>> "Taking another tangent I would much prefer the default time to be
>> 12:00:00 for the conversion of a date to timestamp(+/-timezone)"
>>
>> "Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00"
>>
>> For the timestamp(alias for timestamp without time zone) case the date
>> does not change. For timestamp with time zone it might.
>
> Well, the big problem here is in trying to use either version of
> timestamp when what you really want is a date. It will be much
> easier to get the right semantics if you use the date type for a
> date.

Agreed. If I was following Gavan correctly, he wanted to have a single
timestamp field to store calender dates and datetimes. In other words to
cover both date only situations like birthdays and datetime situations
like an appointment.

>
> -Kevin
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:

>Adrian Klaver wrote: [Actually Gavan Schneider wrote this, don't blame Adrian :]
>
>>I see where my confusion lies. There are two proposals at work in the above:
>>
>>"Taking another tangent I would much prefer the default time
>>to be 12:00:00 for the conversion of a date to timestamp(+/-timezone)"
>>
>>"Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00 "
>>
>>For the timestamp(alias for timestamp without time zone) case
>>the date does not change. For timestamp with time zone it might.
>
>Well, the big problem here is in trying to use either version of
>timestamp when what you really want is a date. It will be much
>easier to get the right semantics if you use the date type for a
>date.
>
This is the cleanest solution.

And I did not want to imply the following...

Adrian Klaver wrote:
>
>If I was following Gavan correctly, he wanted to have a single
>timestamp field to store calender dates and datetimes. In other
>words to cover both date only situations like birthdays and
>datetime situations like an appointment.

My discussion really only applies to some notion of the best
(or, more exactly, the least wrong) time to attribute to a date
when conversion to timestamp happens for whatever reason. And,
as indicated in my original post, I have been stung when dates
got (badly) mixed into a datetime timezone aware context.

The points raised by Adrain have prompted some more research on
my part and I am intrigued to learn that on one day of the year
in many countries (e.g., Brazil) where daylight conversion
happens over midnight the local-time version of midnight as
start of day does not exist. Basically the last day of
unadjusted time ends at midnight and rolls directly into
01:00:00 the next day (i.e., time 00:00:00 never happens on this
one day). So the current date-> date+time system must already
have some added complexity/overhead to check for this rare
special case. (If not, there's a bug needs fixing!)

Basically midnight is not safe as a target entity once timezones
and daylight saving get involved. Midday, on the other hand, is
a very solid proposition, no checks required, 12:00:00 will
happen in all time zones on every day of the year! Basically
nobody messes with their clocks in the middle of the day.

So restating:
'2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never
be wrong; but,
'2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in
some places.

Regards
Gavan Schneider

On 01/21/2013 07:40 PM, Gavan Schneider wrote:
> On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:

>>
>> Well, the big problem here is in trying to use either version of
>> timestamp when what you really want is a date. It will be much
>> easier to get the right semantics if you use the date type for a
>> date.
>>
> This is the cleanest solution.
>
> And I did not want to imply the following...

Well, another fine assumption shot down:)

>
> Adrian Klaver wrote:
>>
>> If I was following Gavan correctly, he wanted to have a single
>> timestamp field to store calender dates and datetimes. In other
>> words to cover both date only situations like birthdays and
>> datetime situations like an appointment.

>
> The points raised by Adrain have prompted some more research on my part
> and I am intrigued to learn that on one day of the year in many
> countries (e.g., Brazil) where daylight conversion happens over midnight
> the local-time version of midnight as start of day does not exist.
> Basically the last day of unadjusted time ends at midnight and rolls
> directly into 01:00:00 the next day (i.e., time 00:00:00 never happens
> on this one day). So the current date-> date+time system must already
> have some added complexity/overhead to check for this rare special case.
> (If not, there's a bug needs fixing!)

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.

>
> Regards
> Gavan Schneider
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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

On Monday, January 21, 2013 at 15:33, Tom Lane wrote:

>I think it is also arguably contrary to the SQL standard...
>
>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.
>
That has to be the trump card.

>... let's just define a new GUC parameter that selects the behavior,
>with a backwards-compatible default setting. ... 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. ...
>
and, why go to significant trouble to implement standards
non-compliance when there is no legacy code to support?

I could always wish the SQL committee had thought along my lines
all those years ago, and then again, I could just do something
useful. :)

On Monday, January 21, 2013 at 11:38, Adrian Klaver wrote:

>I must be missing something. I to am in PST:
>
>test=# \d ts_test
>Table "utility.ts_test"
>Column | Type | Modifiers
>--------+--------------------------+-----------
>ts_fld | timestamp with time zone |
>
>
>test=# INSERT INTO ts_test VALUES('2012-01-21');
>
>test=# SELECT * from ts_test ;
>ts_fld
>------------------------
>2012-01-21 00:00:00-08
>
>test=# set timezone ='AKST9AKDT';
>
>test=# SELECT ts_fld from ts_test;
>ts_fld
>------------------------
>2012-01-20 23:00:00-09
>
The only thing missed is we are saying much same thing. There is
no problem with the conversion. It is, as we see from Tom, fully
SQL compliant. The only "problem" is when you are more
interested in the date itself and not the point in time. This is
just one of several scenarios where the date might get changed
in ways that could be difficult to trace... caveat coder.

Thanks again everyone for a lot more clarity in my thinking
about dates times and timezones.

Regards
Gavan Schneider