Thread: Obtaining hundredths of a second data from time data types
I am using PostgreSQL 9.5 and the 32bit PostgreSQL ODBC Driver(ANSI) version 9.05.01.00 on Windows. Given a table with the following create script: create table TestTable( id integer, ts timestamp,ts0 timestamp(0),ts1 timestamp(1),ts2 timestamp(2),ts3 timestamp(3),ts4 timestamp(4),ts5 timestamp(5),ts6 timestamp(6),t time,t0 time(0),t1 time(1),t2 time(2),t3 time(3),t4 time(4),t5 time(5),t6 time(6),ExtraInfo char(10),constraint TestTable_PrimaryKey primary key (id)) I issue the following INSERT statement: INSERT INTO TestTable (id, ts, ts0, ts1, ts2, ts3, ts4, ts5, ts6, t, t0, t1, t2, t3, t4, t5, t6) VALUES (1, '2001-12-24 01:11:11.111111', '2001-12-24 01:11:11', '2001-12-24 01:11:11.1', '2001-12-24 01:11:11.11', '2001-12-24 01:11:11.111', '2001-12-24 01:11:11.1111', '2001-12-24 01:11:11.11111', '2001-12-24 01:11:11.111111', '01:11:11.111111', '01:11:11', '01:11:11.1', '01:11:11.11', '01:11:11.111', '01:11:11.1111', '01:11:11.11111', '01:11:11.111111') I issue a "SELECT * from TestTable" using SQLExecDirect. I use SQLBindCol to bind the timestamp and time fields to SQL_C_TIMESTAMP data structures. I then use SQLFetch to retrieve the data. The timestamp fields return the hundredths of a second information correctly, but the time fields all return 0 for the hundredths of a second. How can I get the hundredths of a second values for the time fields using the PostgreSQL ODBC Driver? Cheers, Scott Ferrett
On 03/03/2016 10:39 PM, Scott Ferrett wrote: > I am using PostgreSQL 9.5 and the 32bit PostgreSQL ODBC Driver(ANSI) > version 9.05.01.00 on Windows. > > Given a table with the following create script: > create table TestTable( id integer, ts timestamp,ts0 > timestamp(0),ts1 timestamp(1),ts2 timestamp(2),ts3 > timestamp(3),ts4 timestamp(4),ts5 timestamp(5),ts6 timestamp(6),t > time,t0 time(0),t1 time(1),t2 time(2),t3 time(3),t4 > time(4),t5 time(5),t6 time(6),ExtraInfo char(10),constraint > TestTable_PrimaryKey primary key (id)) > > I issue the following INSERT statement: > > INSERT INTO TestTable (id, ts, ts0, ts1, ts2, ts3, ts4, ts5, ts6, t, t0, > t1, t2, t3, t4, t5, t6) VALUES (1, '2001-12-24 01:11:11.111111', > '2001-12-24 01:11:11', '2001-12-24 01:11:11.1', '2001-12-24 > 01:11:11.11', '2001-12-24 01:11:11.111', '2001-12-24 01:11:11.1111', > '2001-12-24 01:11:11.11111', '2001-12-24 01:11:11.111111', > '01:11:11.111111', '01:11:11', '01:11:11.1', '01:11:11.11', > '01:11:11.111', '01:11:11.1111', '01:11:11.11111', '01:11:11.111111') > > I issue a "SELECT * from TestTable" using SQLExecDirect. I use > SQLBindCol to bind the timestamp and time fields to SQL_C_TIMESTAMP data > structures. I then use SQLFetch to retrieve the data. The timestamp > fields return the hundredths of a second information correctly, but the > time fields all return 0 for the hundredths of a second. How can I get > the hundredths of a second values for the time fields using the > PostgreSQL ODBC Driver? So does is show the tenths and/or the fields after the hundredths? Can you show the results of the SELECT query? > > Cheers, > Scott Ferrett > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/04/2016 01:20 PM, Scott Ferrett wrote: > Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM > TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two columns: > > > > As you can see, the timestamp has the hundredths part filled in where as > the time part does not. > > I also tried binding to SQL_C_CHAR as a work-around. But this made no > difference, producing: > > > > This is the result of the same select statement run from pgAdmin III > showing that there the hundredths part is returned for both fields: > > Looks like this is the reason: https://msdn.microsoft.com/en-us/library/ms712436%28v=vs.85%29.aspx The identifier for the time ODBC SQL data type is: SQL_TYPE_TIME to SQL_C_TYPE_TIMESTAMP None[b] Data[c] 16[d] [c] The date fields of the timestamp structure are set to the current date, and the fractional seconds field of the timestamp structure is set to zero. > > > Cheers, > Scott > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/05/2016 12:45 PM, Scott Ferrett wrote: > Interesting. It looks like PostgreSQL is conforming to the > specification (unlike Microsoft SQL or SQL Anywhere). However, it does > not help me with the underlying problem and that is "How do I get the > fractional part of a TIME field via ODBC?". > > I have tried SQL_C_TYPE_TIMSTAMP, SQL_C_TYPE_CHAR and > SQL_C_TYPE_DEFAULT. None of these return the fractional part of the > time field. I've also tries SQL_C_TYPE_BINARY and get an error > szSqlState = "07006", *pfNativeError = 14, *pcbErrorMsg = 43, > MessageText = "Received an unsupported type from Postgres." > > So how can I get the fractional information? Use?: SQL_C_TYPE_TIME > > Note that I do not have any control of the SQL statement. My code needs > to handle any statement and return all information about the query. > > Cheers, > Scott > > On 05/03/2016 09:03, Adrian Klaver wrote: >> On 03/04/2016 01:20 PM, Scott Ferrett wrote: >>> Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM >>> TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two >>> columns: >>> >>> >>> >>> As you can see, the timestamp has the hundredths part filled in where as >>> the time part does not. >>> >>> I also tried binding to SQL_C_CHAR as a work-around. But this made no >>> difference, producing: >>> >>> >>> >>> This is the result of the same select statement run from pgAdmin III >>> showing that there the hundredths part is returned for both fields: >>> >>> >> >> Looks like this is the reason: >> >> https://msdn.microsoft.com/en-us/library/ms712436%28v=vs.85%29.aspx >> >> The identifier for the time ODBC SQL data type is: >> >> SQL_TYPE_TIME >> >> to >> >> SQL_C_TYPE_TIMESTAMP None[b] Data[c] 16[d] >> >> [c] The date fields of the timestamp structure are set to the >> current date, and the fractional seconds field of the timestamp >> structure is set to zero. >> >> >>> >>> >>> Cheers, >>> Scott >>> >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
On 03/06/2016 05:49 PM, Scott Ferrett wrote:
> SQL_C_TYPE_TIME is defined as
>
> struct tagTIME_STRUCT {
> SQLUSMALLINT hour;
> SQLUSMALLINT minute;
> SQLUSMALLINT second;
> } TIME_STRUCT;
>
>
> There is no fraction of a second component.
>
Hmm, then I am at a loss for answer.
--
Adrian Klaver
adrian.klaver@aklaver.com
Interesting. It looks like PostgreSQL is conforming to the specification (unlike Microsoft SQL or SQL Anywhere). However, it does not help me with the underlying problem and that is "How do I get the fractional part of a TIME field via ODBC?". I have tried SQL_C_TYPE_TIMSTAMP, SQL_C_TYPE_CHAR and SQL_C_TYPE_DEFAULT. None of these return the fractional part of the time field. I've also tries SQL_C_TYPE_BINARY and get an error szSqlState = "07006", *pfNativeError = 14, *pcbErrorMsg = 43, MessageText = "Received an unsupported type from Postgres." So how can I get the fractional information? Note that I do not have any control of the SQL statement. My code needs to handle any statement and return all information about the query. Cheers, Scott On 05/03/2016 09:03, Adrian Klaver wrote: > On 03/04/2016 01:20 PM, Scott Ferrett wrote: >> Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM >> TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two >> columns: >> >> >> >> As you can see, the timestamp has the hundredths part filled in where as >> the time part does not. >> >> I also tried binding to SQL_C_CHAR as a work-around. But this made no >> difference, producing: >> >> >> >> This is the result of the same select statement run from pgAdmin III >> showing that there the hundredths part is returned for both fields: >> >> > > Looks like this is the reason: > > https://msdn.microsoft.com/en-us/library/ms712436%28v=vs.85%29.aspx > > The identifier for the time ODBC SQL data type is: > > SQL_TYPE_TIME > > to > > SQL_C_TYPE_TIMESTAMP None[b] Data[c] 16[d] > > [c] The date fields of the timestamp structure are set to the > current date, and the fractional seconds field of the timestamp > structure is set to zero. > > >> >> >> Cheers, >> Scott >> > >
Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two columns:
As you can see, the timestamp has the hundredths part filled in where as the time part does not.
I also tried binding to SQL_C_CHAR as a work-around. But this made no difference, producing:
This is the result of the same select statement run from pgAdmin III showing that there the hundredths part is returned for both fields:
Cheers,
Scott
On 05/03/2016 05:19, Adrian Klaver wrote:
As you can see, the timestamp has the hundredths part filled in where as the time part does not.
I also tried binding to SQL_C_CHAR as a work-around. But this made no difference, producing:
This is the result of the same select statement run from pgAdmin III showing that there the hundredths part is returned for both fields:
Cheers,
Scott
On 05/03/2016 05:19, Adrian Klaver wrote:
On 03/03/2016 10:39 PM, Scott Ferrett wrote:I am using PostgreSQL 9.5 and the 32bit PostgreSQL ODBC Driver(ANSI)
version 9.05.01.00 on Windows.
Given a table with the following create script:
create table TestTable( id integer, ts timestamp,ts0
timestamp(0),ts1 timestamp(1),ts2 timestamp(2),ts3
timestamp(3),ts4 timestamp(4),ts5 timestamp(5),ts6 timestamp(6),t
time,t0 time(0),t1 time(1),t2 time(2),t3 time(3),t4
time(4),t5 time(5),t6 time(6),ExtraInfo char(10),constraint
TestTable_PrimaryKey primary key (id))
I issue the following INSERT statement:
INSERT INTO TestTable (id, ts, ts0, ts1, ts2, ts3, ts4, ts5, ts6, t, t0,
t1, t2, t3, t4, t5, t6) VALUES (1, '2001-12-24 01:11:11.111111',
'2001-12-24 01:11:11', '2001-12-24 01:11:11.1', '2001-12-24
01:11:11.11', '2001-12-24 01:11:11.111', '2001-12-24 01:11:11.1111',
'2001-12-24 01:11:11.11111', '2001-12-24 01:11:11.111111',
'01:11:11.111111', '01:11:11', '01:11:11.1', '01:11:11.11',
'01:11:11.111', '01:11:11.1111', '01:11:11.11111', '01:11:11.111111')
I issue a "SELECT * from TestTable" using SQLExecDirect. I use
SQLBindCol to bind the timestamp and time fields to SQL_C_TIMESTAMP data
structures. I then use SQLFetch to retrieve the data. The timestamp
fields return the hundredths of a second information correctly, but the
time fields all return 0 for the hundredths of a second. How can I get
the hundredths of a second values for the time fields using the
PostgreSQL ODBC Driver?
So does is show the tenths and/or the fields after the hundredths?
Can you show the results of the SELECT query?
Cheers,
Scott Ferrett
Attachment
SQL_C_TYPE_TIME is defined as
struct tagTIME_STRUCT {
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT;
There is no fraction of a second component.
On 06/03/2016 10:07, Adrian Klaver wrote:
> On 03/05/2016 12:45 PM, Scott Ferrett wrote:
>> Interesting. It looks like PostgreSQL is conforming to the
>> specification (unlike Microsoft SQL or SQL Anywhere). However, it does
>> not help me with the underlying problem and that is "How do I get the
>> fractional part of a TIME field via ODBC?".
>>
>> I have tried SQL_C_TYPE_TIMSTAMP, SQL_C_TYPE_CHAR and
>> SQL_C_TYPE_DEFAULT. None of these return the fractional part of the
>> time field. I've also tries SQL_C_TYPE_BINARY and get an error
>> szSqlState = "07006", *pfNativeError = 14, *pcbErrorMsg = 43,
>> MessageText = "Received an unsupported type from Postgres."
>>
>> So how can I get the fractional information?
>
> Use?:
>
> SQL_C_TYPE_TIME
>
>>
>> Note that I do not have any control of the SQL statement. My code needs
>> to handle any statement and return all information about the query.
>>
>> Cheers,
>> Scott
>>
>> On 05/03/2016 09:03, Adrian Klaver wrote:
>>> On 03/04/2016 01:20 PM, Scott Ferrett wrote:
>>>> Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM
>>>> TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two
>>>> columns:
>>>>
>>>>
>>>>
>>>> As you can see, the timestamp has the hundredths part filled in
>>>> where as
>>>> the time part does not.
>>>>
>>>> I also tried binding to SQL_C_CHAR as a work-around. But this made no
>>>> difference, producing:
>>>>
>>>>
>>>>
>>>> This is the result of the same select statement run from pgAdmin III
>>>> showing that there the hundredths part is returned for both fields:
>>>>
>>>>
>>>
>>> Looks like this is the reason:
>>>
>>> https://msdn.microsoft.com/en-us/library/ms712436%28v=vs.85%29.aspx
>>>
>>> The identifier for the time ODBC SQL data type is:
>>>
>>> SQL_TYPE_TIME
>>>
>>> to
>>>
>>> SQL_C_TYPE_TIMESTAMP None[b] Data[c] 16[d]
>>>
>>> [c] The date fields of the timestamp structure are set to the
>>> current date, and the fractional seconds field of the timestamp
>>> structure is set to zero.
>>>
>>>
>>>>
>>>>
>>>> Cheers,
>>>> Scott
>>>>
>>>
>>>
>>
>
>
Hi Scott, Could you try the test drivers 9.5.0104 at http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ ? regards, Hiroshi Inoue On 2016/03/04 15:39, Scott Ferrett wrote: > I am using PostgreSQL 9.5 and the 32bit PostgreSQL ODBC Driver(ANSI) > version 9.05.01.00 on Windows. > > Given a table with the following create script: > create table TestTable( id integer, ts timestamp,ts0 > timestamp(0),ts1 timestamp(1),ts2 timestamp(2),ts3 > timestamp(3),ts4 timestamp(4),ts5 timestamp(5),ts6 timestamp(6),t > time,t0 time(0),t1 time(1),t2 time(2),t3 time(3),t4 > time(4),t5 time(5),t6 time(6),ExtraInfo char(10),constraint > TestTable_PrimaryKey primary key (id)) > > I issue the following INSERT statement: > > INSERT INTO TestTable (id, ts, ts0, ts1, ts2, ts3, ts4, ts5, ts6, t, > t0, t1, t2, t3, t4, t5, t6) VALUES (1, '2001-12-24 01:11:11.111111', > '2001-12-24 01:11:11', '2001-12-24 01:11:11.1', '2001-12-24 > 01:11:11.11', '2001-12-24 01:11:11.111', '2001-12-24 01:11:11.1111', > '2001-12-24 01:11:11.11111', '2001-12-24 01:11:11.111111', > '01:11:11.111111', '01:11:11', '01:11:11.1', '01:11:11.11', > '01:11:11.111', '01:11:11.1111', '01:11:11.11111', '01:11:11.111111') > > I issue a "SELECT * from TestTable" using SQLExecDirect. I use > SQLBindCol to bind the timestamp and time fields to SQL_C_TIMESTAMP > data structures. I then use SQLFetch to retrieve the data. The > timestamp fields return the hundredths of a second information > correctly, but the time fields all return 0 for the hundredths of a > second. How can I get the hundredths of a second values for the time > fields using the PostgreSQL ODBC Driver? > > Cheers, > Scott Ferrett
Hi Hiroshi, This driver fixes all the problems with retrieving data. Thank you. This allowed me to get further with my testing and found the next problem. Although you can now retrieve data using SQLBindCol, you cannot insert of update data using SQLBindParameter. If you issue an INSERT INTO TestTable (ID,T) VALUES (?,?) and use SQLBindParameter to bind an SQL_C_TIMESTAMP structure to the second parameter, then the new row is inserted with the fractional part truncated. Cheers, Scott On 08/03/2016 23:37, Inoue, Hiroshi wrote: > Hi Scott, > > Could you try the test drivers 9.5.0104 at > http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ > ? > > regards, > Hiroshi Inoue > > On 2016/03/04 15:39, Scott Ferrett wrote: >> I am using PostgreSQL 9.5 and the 32bit PostgreSQL ODBC Driver(ANSI) >> version 9.05.01.00 on Windows. >> >> Given a table with the following create script: >> create table TestTable( id integer, ts timestamp,ts0 >> timestamp(0),ts1 timestamp(1),ts2 timestamp(2),ts3 >> timestamp(3),ts4 timestamp(4),ts5 timestamp(5),ts6 timestamp(6),t >> time,t0 time(0),t1 time(1),t2 time(2),t3 time(3),t4 >> time(4),t5 time(5),t6 time(6),ExtraInfo char(10),constraint >> TestTable_PrimaryKey primary key (id)) >> >> I issue the following INSERT statement: >> >> INSERT INTO TestTable (id, ts, ts0, ts1, ts2, ts3, ts4, ts5, ts6, t, >> t0, t1, t2, t3, t4, t5, t6) VALUES (1, '2001-12-24 01:11:11.111111', >> '2001-12-24 01:11:11', '2001-12-24 01:11:11.1', '2001-12-24 >> 01:11:11.11', '2001-12-24 01:11:11.111', '2001-12-24 01:11:11.1111', >> '2001-12-24 01:11:11.11111', '2001-12-24 01:11:11.111111', >> '01:11:11.111111', '01:11:11', '01:11:11.1', '01:11:11.11', >> '01:11:11.111', '01:11:11.1111', '01:11:11.11111', '01:11:11.111111') >> >> I issue a "SELECT * from TestTable" using SQLExecDirect. I use >> SQLBindCol to bind the timestamp and time fields to SQL_C_TIMESTAMP >> data structures. I then use SQLFetch to retrieve the data. The >> timestamp fields return the hundredths of a second information >> correctly, but the time fields all return 0 for the hundredths of a >> second. How can I get the hundredths of a second values for the time >> fields using the PostgreSQL ODBC Driver? >> >> Cheers, >> Scott Ferrett > >
Hi Scott, On 2016/03/09 9:34, Scott Ferrett wrote: > Hi Hiroshi, > > This driver fixes all the problems with retrieving data. Thank you. > > This allowed me to get further with my testing and found the next > problem. > > Although you can now retrieve data using SQLBindCol, you cannot insert > of update data using SQLBindParameter. > > If you issue an INSERT INTO TestTable (ID,T) VALUES (?,?) and use > SQLBindParameter to bind an SQL_C_TIMESTAMP structure to the second > parameter, then the new row is inserted with the fractional part > truncated. Please try the test drivers 9.5.0105 at http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ . regards, Hiroshi Inoue
Hi Hiroshi, That driver passed all my tests. Thanks. Cheers, Scott On 09/03/2016 21:24, Inoue, Hiroshi wrote: > Hi Scott, > > On 2016/03/09 9:34, Scott Ferrett wrote: >> Hi Hiroshi, >> >> This driver fixes all the problems with retrieving data. Thank you. >> >> This allowed me to get further with my testing and found the next >> problem. >> >> Although you can now retrieve data using SQLBindCol, you cannot >> insert of update data using SQLBindParameter. >> >> If you issue an INSERT INTO TestTable (ID,T) VALUES (?,?) and use >> SQLBindParameter to bind an SQL_C_TIMESTAMP structure to the second >> parameter, then the new row is inserted with the fractional part >> truncated. > > Please try the test drivers 9.5.0105 at > http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ > . > > regards, > Hiroshi Inoue >