Re: Daylight saving time rules being applied to DateTimes that don't have a timezone

On 11/16/2016 03:44 PM, Nathan Kendall wrote:
> On 16/11/2016, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>> On 11/16/2016 01:09 PM, Nathan Kendall wrote:
>>
>> Ccing list

>>>

>> So to be strictly accurate the PK constraint error is actually valid.
>> The question remains though, what caused the change in behavior. Before
>> I forget:
>>
>> What is the TimeZone set to in postgresql.conf?
>>
>
> postgresql.conf has timezone = 'Canada/Atlantic'
>
> You conducted your test with data type timestamptz. I am working with
> columns of type timestamp *without* time zone because I have to
> support data sources which do not obey daylight saving time rules.
> When I key in INSERT statements into pgAdmin, it has no trouble with
> accepting both '2016-03-13 02:00' and '2016-03-13 03:00', without
> shifting the time by an hour or producing a PK violation, because the
> column in the database table is defined as timestamp without time
> zone.

I understand. I was not clear enough. Just thinking out loud that at the
totally accurate level the 'correct' thing was being done. Still having
timestamp versus timestamptz is to allow for the situation you describe
and it should work and it does like you say in pgAdmin and:

test[5432]=# set timezone = 'Canada/Atlantic';
SET
test[5432]=# select '03/13/2016 2:00:00'::timestamp ;
timestamp
---------------------
2016-03-13 02:00:00
(1 row)

test[5432]=# select '03/13/2016 3:00:00'::timestamp ;
timestamp
---------------------
2016-03-13 03:00:00
(1 row)

I took a stab at the source code to see what is going on, but it is
gibberish to me. Hoping that someone else picks up this thread and can
use the information to figure out why the ODBC driver is picking up the
timezone and rotating the timestamp before sending it to the database?

>
>>
>> The answer to why the behavior changed I suspect lies in:
>>
>> https://odbc.postgresql.org/docs/release.html
>>
>> psqlODBC 09.05.0100 Release
>>
>> Use libpq for all communication with the server
>> Previously, libpq was only used for authentication. Using it for all
>> communication lets us remove a lot of duplicated code. libpq is now
>> required for building or using libpq.
>>
>>
>> Send datatype information for query parameters, when known
>> If a query parameter is bound with a specific SQL type, pass on that
>> information to the server. This makes the behaviour of queries like
>> "SELECT '555' > ?" more sensible, where the result depends on whether
>> the query parameter is interpreted as an integer or a string.
>>
>>
>> I'm afraid it is going to take someone more knowledgeable of the
>> internals to fully answer this though.
>>

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