Re: Yet Another Timestamp Question: Time Defaults

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