Thread: Daylight saving time rules being applied to DateTimes that don't have a timezone
Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Nathan Kendall
Date:
We are having a problem with psqlODBC versions 09.05.0100 through
09.05.0400. With the database table schema listed below, the sample
C# test app below that fails with a violation of the primary key.
However, the same C# code works without issue when using psqlODBC
version 09.03.0400 or older. It would seem as though psqlODBC
versions from 09.05.0100 onward are applying daylight saving time
rules to DateTimes that are specified as not being associated with a
timezone.
Nathan
CREATE TABLE time_test
(
stationid character varying(8) NOT NULL,
date_time timestamp without time zone NOT NULL,
temperature double precision,
CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
);
C# Test App Source Code:
using System;
using System.Data.Odbc;
namespace TimeTestApp1
{
class Program
{
static void Main(string[] args)
{
using (var conn = new OdbcConnection("Driver={PostgreSQL
ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
{
conn.Open();
using (var command = new OdbcCommand("", conn))
{
command.Parameters.Add(new OdbcParameter("stationid",
OdbcType.VarChar));
command.Parameters.Add(new OdbcParameter("date_time",
OdbcType.DateTime));
command.Parameters.Add(new OdbcParameter("temperature",
OdbcType.Double));
command.CommandText = "INSERT INTO time_test (stationid,
date_time, temperature) VALUES (?, ?, ?)";
command.CommandTimeout = 60;
command.Prepare();
DateTime[] testTimes = new DateTime[] {
new DateTime(2016, 3, 13, 1, 0, 0, DateTimeKind.Unspecified),
new DateTime(2016, 3, 13, 2, 0, 0, DateTimeKind.Unspecified),
new DateTime(2016, 3, 13, 3, 0, 0, DateTimeKind.Unspecified)
};
int i = 0;
foreach (var dt in testTimes)
{
i++;
command.Parameters[0].Value = "Place";
command.Parameters[1].Value = dt;
command.Parameters[2].Value = 60 - i;
command.ExecuteNonQuery();
}
}
}
}
}
}
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Adrian Klaver
Date:
On 11/10/2016 06:45 AM, Nathan Kendall wrote:
> We are having a problem with psqlODBC versions 09.05.0100 through
> 09.05.0400. With the database table schema listed below, the sample
> C# test app below that fails with a violation of the primary key.
> However, the same C# code works without issue when using psqlODBC
> version 09.03.0400 or older. It would seem as though psqlODBC
> versions from 09.05.0100 onward are applying daylight saving time
> rules to DateTimes that are specified as not being associated with a
> timezone.
What is the error you are getting back or the value you are seeing that
is different?
>
> Nathan
>
>
> CREATE TABLE time_test
> (
> stationid character varying(8) NOT NULL,
> date_time timestamp without time zone NOT NULL,
> temperature double precision,
> CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
> );
>
>
> C# Test App Source Code:
>
> using System;
> using System.Data.Odbc;
>
> namespace TimeTestApp1
> {
> class Program
> {
> static void Main(string[] args)
> {
> using (var conn = new OdbcConnection("Driver={PostgreSQL
> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
> {
> conn.Open();
> using (var command = new OdbcCommand("", conn))
> {
> command.Parameters.Add(new OdbcParameter("stationid",
> OdbcType.VarChar));
> command.Parameters.Add(new OdbcParameter("date_time",
> OdbcType.DateTime));
> command.Parameters.Add(new OdbcParameter("temperature",
> OdbcType.Double));
> command.CommandText = "INSERT INTO time_test (stationid,
> date_time, temperature) VALUES (?, ?, ?)";
> command.CommandTimeout = 60;
> command.Prepare();
>
> DateTime[] testTimes = new DateTime[] {
> new DateTime(2016, 3, 13, 1, 0, 0, DateTimeKind.Unspecified),
> new DateTime(2016, 3, 13, 2, 0, 0, DateTimeKind.Unspecified),
> new DateTime(2016, 3, 13, 3, 0, 0, DateTimeKind.Unspecified)
> };
>
> int i = 0;
> foreach (var dt in testTimes)
> {
> i++;
> command.Parameters[0].Value = "Place";
> command.Parameters[1].Value = dt;
> command.Parameters[2].Value = 60 - i;
> command.ExecuteNonQuery();
> }
> }
> }
> }
> }
> }
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Nathan Kendall
Date:
On 15/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 11/10/2016 06:45 AM, Nathan Kendall wrote:
>> We are having a problem with psqlODBC versions 09.05.0100 through
>> 09.05.0400. With the database table schema listed below, the sample
>> C# test app below that fails with a violation of the primary key.
>> However, the same C# code works without issue when using psqlODBC
>> version 09.03.0400 or older. It would seem as though psqlODBC
>> versions from 09.05.0100 onward are applying daylight saving time
>> rules to DateTimes that are specified as not being associated with a
>> timezone.
>
> What is the error you are getting back or the value you are seeing that
> is different?
With psqlODBC version 09.05.x, the DateTime value of '2016-03-13
02:00:00' is getting pushed ahead by an hour and is appearing in the
database as '2016-03-13 03:00:00'. This is causing the following
iteration of the loop ('2016-03-13 03:00:00') to fail with an
exception of type 'System.Data.Odbc.OdbcException': ERROR [23505]
ERROR: duplicate key value violates unique constraint "time_test_pk";
Error while executing the query
Perhaps I should also mention that I am working on computers whose
operating system level timezone is set to Atlantic Standard Time,
which is one hour ahead of the time in New York. However, the
timezone should not be getting applied to a column of type "timestamp
without time zone". As mentioned previously, there was no problem
with psqlODBC 09.03.0400.
>
>>
>> Nathan
>>
>>
>> CREATE TABLE time_test
>> (
>> stationid character varying(8) NOT NULL,
>> date_time timestamp without time zone NOT NULL,
>> temperature double precision,
>> CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
>> );
>>
>>
>> C# Test App Source Code:
>>
>> using System;
>> using System.Data.Odbc;
>>
>> namespace TimeTestApp1
>> {
>> class Program
>> {
>> static void Main(string[] args)
>> {
>> using (var conn = new OdbcConnection("Driver={PostgreSQL
>> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
>> {
>> conn.Open();
>> using (var command = new OdbcCommand("", conn))
>> {
>> command.Parameters.Add(new OdbcParameter("stationid",
>> OdbcType.VarChar));
>> command.Parameters.Add(new OdbcParameter("date_time",
>> OdbcType.DateTime));
>> command.Parameters.Add(new OdbcParameter("temperature",
>> OdbcType.Double));
>> command.CommandText = "INSERT INTO time_test (stationid,
>> date_time, temperature) VALUES (?, ?, ?)";
>> command.CommandTimeout = 60;
>> command.Prepare();
>>
>> DateTime[] testTimes = new DateTime[] {
>> new DateTime(2016, 3, 13, 1, 0, 0, DateTimeKind.Unspecified),
>> new DateTime(2016, 3, 13, 2, 0, 0, DateTimeKind.Unspecified),
>> new DateTime(2016, 3, 13, 3, 0, 0, DateTimeKind.Unspecified)
>> };
>>
>> int i = 0;
>> foreach (var dt in testTimes)
>> {
>> i++;
>> command.Parameters[0].Value = "Place";
>> command.Parameters[1].Value = dt;
>> command.Parameters[2].Value = 60 - i;
>> command.ExecuteNonQuery();
>> }
>> }
>> }
>> }
>> }
>> }
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Adrian Klaver
Date:
On 11/15/2016 01:51 PM, Nathan Kendall wrote:
> On 15/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 11/10/2016 06:45 AM, Nathan Kendall wrote:
>>> We are having a problem with psqlODBC versions 09.05.0100 through
>>> 09.05.0400. With the database table schema listed below, the sample
>>> C# test app below that fails with a violation of the primary key.
>>> However, the same C# code works without issue when using psqlODBC
>>> version 09.03.0400 or older. It would seem as though psqlODBC
>>> versions from 09.05.0100 onward are applying daylight saving time
>>> rules to DateTimes that are specified as not being associated with a
>>> timezone.
>>
>> What is the error you are getting back or the value you are seeing that
>> is different?
>
> With psqlODBC version 09.05.x, the DateTime value of '2016-03-13
> 02:00:00' is getting pushed ahead by an hour and is appearing in the
> database as '2016-03-13 03:00:00'. This is causing the following
> iteration of the loop ('2016-03-13 03:00:00') to fail with an
> exception of type 'System.Data.Odbc.OdbcException': ERROR [23505]
> ERROR: duplicate key value violates unique constraint "time_test_pk";
>
> Error while executing the query
>
> Perhaps I should also mention that I am working on computers whose
> operating system level timezone is set to Atlantic Standard Time,
> which is one hour ahead of the time in New York. However, the
> timezone should not be getting applied to a column of type "timestamp
> without time zone". As mentioned previously, there was no problem
> with psqlODBC 09.03.0400.
Does the above mean the client you are entering the data on is in New York?
>
>
>>
>>>
>>> Nathan
>>>
>>>
>>> CREATE TABLE time_test
>>> (
>>> stationid character varying(8) NOT NULL,
>>> date_time timestamp without time zone NOT NULL,
>>> temperature double precision,
>>> CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
>>> );
>>>
>>>
>>> C# Test App Source Code:
>>>
>>> using System;
>>> using System.Data.Odbc;
>>>
>>> namespace TimeTestApp1
>>> {
>>> class Program
>>> {
>>> static void Main(string[] args)
>>> {
>>> using (var conn = new OdbcConnection("Driver={PostgreSQL
>>> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
>>> {
>>> conn.Open();
>>> using (var command = new OdbcCommand("", conn))
>>> {
>>> command.Parameters.Add(new OdbcParameter("stationid",
>>> OdbcType.VarChar));
>>> command.Parameters.Add(new OdbcParameter("date_time",
>>> OdbcType.DateTime));
>>> command.Parameters.Add(new OdbcParameter("temperature",
>>> OdbcType.Double));
>>> command.CommandText = "INSERT INTO time_test (stationid,
>>> date_time, temperature) VALUES (?, ?, ?)";
>>> command.CommandTimeout = 60;
>>> command.Prepare();
>>>
>>> DateTime[] testTimes = new DateTime[] {
>>> new DateTime(2016, 3, 13, 1, 0, 0, DateTimeKind.Unspecified),
>>> new DateTime(2016, 3, 13, 2, 0, 0, DateTimeKind.Unspecified),
>>> new DateTime(2016, 3, 13, 3, 0, 0, DateTimeKind.Unspecified)
>>> };
>>>
>>> int i = 0;
>>> foreach (var dt in testTimes)
>>> {
>>> i++;
>>> command.Parameters[0].Value = "Place";
>>> command.Parameters[1].Value = dt;
>>> command.Parameters[2].Value = 60 - i;
>>> command.ExecuteNonQuery();
>>> }
>>> }
>>> }
>>> }
>>> }
>>> }
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Adrian Klaver
Date:
On 11/16/2016 01:09 PM, Nathan Kendall wrote:
Ccing list
> On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 11/15/2016 01:51 PM, Nathan Kendall wrote:
>>> On 15/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>> On 11/10/2016 06:45 AM, Nathan Kendall wrote:
>>>>> We are having a problem with psqlODBC versions 09.05.0100 through
>>>>> 09.05.0400. With the database table schema listed below, the sample
>>>>> C# test app below that fails with a violation of the primary key.
>>>>> However, the same C# code works without issue when using psqlODBC
>>>>> version 09.03.0400 or older. It would seem as though psqlODBC
>>>>> versions from 09.05.0100 onward are applying daylight saving time
>>>>> rules to DateTimes that are specified as not being associated with a
>>>>> timezone.
>>>>
>>>> What is the error you are getting back or the value you are seeing that
>>>> is different?
>>>
>>> With psqlODBC version 09.05.x, the DateTime value of '2016-03-13
>>> 02:00:00' is getting pushed ahead by an hour and is appearing in the
>>> database as '2016-03-13 03:00:00'. This is causing the following
>>> iteration of the loop ('2016-03-13 03:00:00') to fail with an
>>> exception of type 'System.Data.Odbc.OdbcException': ERROR [23505]
>>> ERROR: duplicate key value violates unique constraint "time_test_pk";
>>>
>>> Error while executing the query
>>>
>>> Perhaps I should also mention that I am working on computers whose
>>> operating system level timezone is set to Atlantic Standard Time,
>>> which is one hour ahead of the time in New York. However, the
>>> timezone should not be getting applied to a column of type "timestamp
>>> without time zone". As mentioned previously, there was no problem
>>> with psqlODBC 09.03.0400.
>>
>> Does the above mean the client you are entering the data on is in New York?
>
> No, I only mentioned New York as a reference point. The client and
> server operating systems are both running on the Atlantic Time. The
> significance of 2016-03-13 is that daylight saving time begins at 2am
> on the second Sunday of March in most of North America.
Yeah, 03/13/2016 2:00:00 and 03/13/2016 3:00:00 are the same time:
test[5432]=# set timezone = 'America/Halifax';
SET
test[5432]=# select '03/13/2016 2:00:00'::timestamptz at time zone 'utc';
timezone
---------------------
2016-03-13 06:00:00
(1 row)
test[5432]=# select '03/13/2016 3:00:00'::timestamptz at time zone 'utc';
timezone
---------------------
2016-03-13 06:00:00
(1 row)
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?
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.
>
>>
>>>
>>>
>>>>
>>>>>
>>>>> Nathan
>>>>>
>>>>>
>>>>> CREATE TABLE time_test
>>>>> (
>>>>> stationid character varying(8) NOT NULL,
>>>>> date_time timestamp without time zone NOT NULL,
>>>>> temperature double precision,
>>>>> CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
>>>>> );
>>>>>
>>>>>
>>>>> C# Test App Source Code:
>>>>>
>>>>> using System;
>>>>> using System.Data.Odbc;
>>>>>
>>>>> namespace TimeTestApp1
>>>>> {
>>>>> class Program
>>>>> {
>>>>> static void Main(string[] args)
>>>>> {
>>>>> using (var conn = new OdbcConnection("Driver={PostgreSQL
>>>>> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
>>>>> {
>>>>> conn.Open();
>>>>> using (var command = new OdbcCommand("", conn))
>>>>> {
>>>>> command.Parameters.Add(new OdbcParameter("stationid",
>>>>> OdbcType.VarChar));
>>>>> command.Parameters.Add(new OdbcParameter("date_time",
>>>>> OdbcType.DateTime));
>>>>> command.Parameters.Add(new OdbcParameter("temperature",
>>>>> OdbcType.Double));
>>>>> command.CommandText = "INSERT INTO time_test (stationid,
>>>>> date_time, temperature) VALUES (?, ?, ?)";
>>>>> command.CommandTimeout = 60;
>>>>> command.Prepare();
>>>>>
>>>>> DateTime[] testTimes = new DateTime[] {
>>>>> new DateTime(2016, 3, 13, 1, 0, 0,
>>>>> DateTimeKind.Unspecified),
>>>>> new DateTime(2016, 3, 13, 2, 0, 0,
>>>>> DateTimeKind.Unspecified),
>>>>> new DateTime(2016, 3, 13, 3, 0, 0,
>>>>> DateTimeKind.Unspecified)
>>>>> };
>>>>>
>>>>> int i = 0;
>>>>> foreach (var dt in testTimes)
>>>>> {
>>>>> i++;
>>>>> command.Parameters[0].Value = "Place";
>>>>> command.Parameters[1].Value = dt;
>>>>> command.Parameters[2].Value = 60 - i;
>>>>> command.ExecuteNonQuery();
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>> }
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.klaver@aklaver.com
>>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Adrian Klaver
Date:
On 11/16/2016 03:44 PM, Nathan Kendall wrote:
> On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
Re: Daylight saving time rules being applied to DateTimes that don't have a timezone
From
Nathan Kendall
Date:
On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 11/16/2016 01:09 PM, Nathan Kendall wrote:
>
> Ccing list
>> On 16/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>> On 11/15/2016 01:51 PM, Nathan Kendall wrote:
>>>> On 15/11/2016, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>>> On 11/10/2016 06:45 AM, Nathan Kendall wrote:
>>>>>> We are having a problem with psqlODBC versions 09.05.0100 through
>>>>>> 09.05.0400. With the database table schema listed below, the sample
>>>>>> C# test app below that fails with a violation of the primary key.
>>>>>> However, the same C# code works without issue when using psqlODBC
>>>>>> version 09.03.0400 or older. It would seem as though psqlODBC
>>>>>> versions from 09.05.0100 onward are applying daylight saving time
>>>>>> rules to DateTimes that are specified as not being associated with a
>>>>>> timezone.
>>>>>
>>>>> What is the error you are getting back or the value you are seeing
>>>>> that
>>>>> is different?
>>>>
>>>> With psqlODBC version 09.05.x, the DateTime value of '2016-03-13
>>>> 02:00:00' is getting pushed ahead by an hour and is appearing in the
>>>> database as '2016-03-13 03:00:00'. This is causing the following
>>>> iteration of the loop ('2016-03-13 03:00:00') to fail with an
>>>> exception of type 'System.Data.Odbc.OdbcException': ERROR [23505]
>>>> ERROR: duplicate key value violates unique constraint "time_test_pk";
>>>>
>>>> Error while executing the query
>>>>
>>>> Perhaps I should also mention that I am working on computers whose
>>>> operating system level timezone is set to Atlantic Standard Time,
>>>> which is one hour ahead of the time in New York. However, the
>>>> timezone should not be getting applied to a column of type "timestamp
>>>> without time zone". As mentioned previously, there was no problem
>>>> with psqlODBC 09.03.0400.
>>>
>>> Does the above mean the client you are entering the data on is in New
>>> York?
>>
>> No, I only mentioned New York as a reference point. The client and
>> server operating systems are both running on the Atlantic Time. The
>> significance of 2016-03-13 is that daylight saving time begins at 2am
>> on the second Sunday of March in most of North America.
>
> Yeah, 03/13/2016 2:00:00 and 03/13/2016 3:00:00 are the same time:
>
> test[5432]=# set timezone = 'America/Halifax';
> SET
> test[5432]=# select '03/13/2016 2:00:00'::timestamptz at time zone 'utc';
> timezone
> ---------------------
> 2016-03-13 06:00:00
> (1 row)
>
> test[5432]=# select '03/13/2016 3:00:00'::timestamptz at time zone 'utc';
> timezone
> ---------------------
> 2016-03-13 06:00:00
> (1 row)
>
> 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.
>
> 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.
>
>
>>
>>>
>>>>
>>>>
>>>>>
>>>>>>
>>>>>> Nathan
>>>>>>
>>>>>>
>>>>>> CREATE TABLE time_test
>>>>>> (
>>>>>> stationid character varying(8) NOT NULL,
>>>>>> date_time timestamp without time zone NOT NULL,
>>>>>> temperature double precision,
>>>>>> CONSTRAINT time_test_pk PRIMARY KEY (stationid, date_time)
>>>>>> );
>>>>>>
>>>>>>
>>>>>> C# Test App Source Code:
>>>>>>
>>>>>> using System;
>>>>>> using System.Data.Odbc;
>>>>>>
>>>>>> namespace TimeTestApp1
>>>>>> {
>>>>>> class Program
>>>>>> {
>>>>>> static void Main(string[] args)
>>>>>> {
>>>>>> using (var conn = new OdbcConnection("Driver={PostgreSQL
>>>>>> ANSI(x64)};Server=localhost;Database=weather;Uid=userGoesHere;Pwd=passGoesHere;"))
>>>>>> {
>>>>>> conn.Open();
>>>>>> using (var command = new OdbcCommand("", conn))
>>>>>> {
>>>>>> command.Parameters.Add(new OdbcParameter("stationid",
>>>>>> OdbcType.VarChar));
>>>>>> command.Parameters.Add(new OdbcParameter("date_time",
>>>>>> OdbcType.DateTime));
>>>>>> command.Parameters.Add(new OdbcParameter("temperature",
>>>>>> OdbcType.Double));
>>>>>> command.CommandText = "INSERT INTO time_test (stationid,
>>>>>> date_time, temperature) VALUES (?, ?, ?)";
>>>>>> command.CommandTimeout = 60;
>>>>>> command.Prepare();
>>>>>>
>>>>>> DateTime[] testTimes = new DateTime[] {
>>>>>> new DateTime(2016, 3, 13, 1, 0, 0,
>>>>>> DateTimeKind.Unspecified),
>>>>>> new DateTime(2016, 3, 13, 2, 0, 0,
>>>>>> DateTimeKind.Unspecified),
>>>>>> new DateTime(2016, 3, 13, 3, 0, 0,
>>>>>> DateTimeKind.Unspecified)
>>>>>> };
>>>>>>
>>>>>> int i = 0;
>>>>>> foreach (var dt in testTimes)
>>>>>> {
>>>>>> i++;
>>>>>> command.Parameters[0].Value = "Place";
>>>>>> command.Parameters[1].Value = dt;
>>>>>> command.Parameters[2].Value = 60 - i;
>>>>>> command.ExecuteNonQuery();
>>>>>> }
>>>>>> }
>>>>>> }
>>>>>> }
>>>>>> }
>>>>>> }
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian.klaver@aklaver.com
>>>>>
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>