If just ran across this today and thought I should mention it just in case it wasn't already known.
ODBC like:
... DEFAULT 'infinity', ...
ODBC doesn't like:
... DEFAULT '-infinity', ...
Regards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--- On Fri, 11/30/07, Greg Cocks
> Using a MS product? Is this *maybe* related to the same
> 'issue' with
> NULLs from PostgreSQL to MS products i.e., in the
>
> file, turning 'transform_null_equals' to
> 'on'?
>
> Likely not, but thought I would put it out there... :-)
> .. DEFAULT '-infinity', ...
I should probably clarify what I am seeing.
I have a table that has a time stamp field. The timestamp fields have two special values in postgres:
'infinity' which implies '+infinity'
and '-infinity'
when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client side timestamp displays '9999-12-31 .... '.
However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message.
Regards,
Richard Broesma Jr.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Richard Broersma Jr wrote:
> --- On Fri, 11/30/07, Greg Cocks
>
>> Using a MS product? Is this *maybe* related to the same
>> 'issue' with
>> NULLs from PostgreSQL to MS products i.e., in the
>>
>> file, turning 'transform_null_equals' to
>> 'on'?
>>
>> Likely not, but thought I would put it out there... :-)
>> .. DEFAULT '-infinity', ...
>
> I should probably clarify what I am seeing.
>
> I have a table that has a time stamp field. The timestamp fields have two special values in postgres:
> 'infinity' which implies '+infinity'
> and '-infinity'
ODBC doesn't have such a concept.
> when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client side timestamp displays '9999-12-31 .... '.
Though I have no confidence, I'm doing so.
Do you think it is an appropriate handling ?
> However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message.
How do you think the driver should treat -infinity ?
regards,
Hiroshi Inoue
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--- On Sun, 12/2/07, Hiroshi Inoue
> Do you think it is an appropriate handling ?
This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resulted in a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I should try a few different client front ends to see how they treated (+/-)infinity:
Client | -infinity | + infinity
ADO |
Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59|
OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59|
ADO = ado recordset created in VB
Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?)
OOB(ODBC) = Open Office.org Base ODBC connection to the Database.
Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making -infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not really care about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an implied NULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity.
> How do you think the driver should treat -infinity ?
It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handled by ODBC. :o)
Regards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Hello.
Richard Broersma Jr wrote:
> --- On Sun, 12/2/07, Hiroshi Inoue
>
>
>> Do you think it is an appropriate handling ?
>>
>
> This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resulted in a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I should try a few different client front ends to see how they treated (+/-)infinity:
>
> Client | -infinity | + infinity
> ADO |
> Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59|
> OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59|
>
> ADO = ado recordset created in VB
> Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?)
> OOB(ODBC) = Open Office.org Base ODBC connection to the Database.
>
> Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making -infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not really care about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an implied NULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity.
>
>
>> How do you think the driver should treat -infinity ?
>>
>
> It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handled by ODBC. :o)
>
It should be handled by ODBC because of possible type conversions on the
ODBC driver layer. Take a look:
http://msdn2.microsoft.com/en-us/library/ms709280aspx. Table shows
required conversions. Also all Microsoft applications notify ODBC driver
by setting additional parameter during initialization. I think ADO
requests 'inifinity' value as SQL_C_CHAR and sure it doesn't expect it
to be 'infinity'.
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--- On Mon, 12/3/07, Andrei Kovalevski
> http://msdn2.microsoft.com/en-us/library/ms709280aspx.
I tried this link, but is says "content not found".
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Hello,
Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski
>
>
>> http://msdn2.microsoft.com/en-us/library/ms709280aspx.
>>
>
> I tried this link, but is says "content not found".
>
Oh... Sorry, missed a dot before 'aspx'. Correct link:
http://msdn2.microsoft.com/en-us/library/ms709280.aspx
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--- On Mon, 12/3/07, Andrei Kovalevski
> Also all Microsoft applications notify ODBC driver
> by setting additional parameter during initialization. I
> think ADO requests 'inifinity' value as SQL_C_CHAR and sure
> it doesn't expect it to be 'infinity'.
Actually, postgresql doesn't actually have a (+/-) infinity value. Notice:
http://www.postgresql.org/docs/8.3/static/datatype-datetime. html#AEN4999
+infinity = 5874897 AD
-infinity = 4713 BC
so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski
>
>
>> Also all Microsoft applications notify ODBC driver
>> by setting additional parameter during initialization. I
>> think ADO requests 'inifinity' value as SQL_C_CHAR and sure
>> it doesn't expect it to be 'infinity'.
>>
>
> Actually, postgresql doesn't actually have a (+/-) infinity value. Notice:
> http://www.postgresql.org/docs/8.3/static/datatype-datetime. html#AEN4999
>
> +infinity = 5874897 AD
> -infinity = 4713 BC
>
Are you sure these values are equal? Try this in PgAdmin:
create table t (t timestamp);
insert into t values ('-infinity');
insert into t values ('infinity');
insert into t values ('9999-12-31 23:59:59');
insert into t values ('5874897-12-31 23:59:59');
insert into t values ('5874897-12-31 23:59:59.999999999');
--insert into t values ('5874898-01-01 00:00:00');
select t::date, t::time, t::timestamp, t::varchar from t;
Cool behaviour: PostgreSQL 8.2.x thinks that 'infinity' = '-infinity' =
null if it was converted from timestamp to date or time....
By the way - you can't 'insert into t values ('5874898-01-01
00:00:00')', but you can 'insert into t values ('5874897-12-31
23:59:59.999999999')' - with interesting result ;)
> so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--- On Mon, 12/3/07, Andrei Kovalevski
> Are you sure these values are equal? Try this in PgAdmin:
>
> create table t (t timestamp);
> insert into t values ('-infinity');
> insert into t values ('infinity');
> insert into t values ('9999-12-31 23:59:59');
> insert into t values ('5874897-12-31 23:59:59');
> insert into t values ('5874897-12-31
> 23:59:59.999999999');
> --insert into t values ('5874898-01-01 00:00:00');
> select t::date, t::time, t::timestamp, t::varchar from t;
Now the real question is, "What does and ODBC client do with these value?".
Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side application can display.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Hello, Happy New Year!
Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski
>
>
>> Are you sure these values are equal? Try this in PgAdmin:
>>
>> create table t (t timestamp);
>> insert into t values ('-infinity');
>> insert into t values ('infinity');
>> insert into t values ('9999-12-31 23:59:59');
>> insert into t values ('5874897-12-31 23:59:59');
>> insert into t values ('5874897-12-31
>> 23:59:59.999999999');
>> --insert into t values ('5874898-01-01 00:00:00');
>> select t::date, t::time, t::timestamp, t::varchar from t;
>>
>
> Now the real question is, "What does and ODBC client do with these value?".
>
> Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side application can display.
>
ODBC supports dates and timestamps as:
typedef struct tagTIMESTAMP_STRUCT
{
SQLSMALLINT year;
..........
} TIMESTAMP_STRUCT;
So, it can't show dates bigger then 32,767 or smaller then -32,768.
I think - correct driver behavour should be:
1) driver returns NULL value for SQL_DATE_STRUCT and SQL_TIMESTAMP_STRUCT
2) function return value should be SQL_SUCCESS_WITH_INFO;
3) driver also sets Informative Description for this error with actual
value - if possible.
--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster