Skip site navigation (1) Skip section navigation (2)

Re: ODBC driver and Dates

From: Hannu Krosing <hannu(at)trust(dot)ee>
To: Byron Nikolaidis <byronn(at)insightdist(dot)com>
Cc: "pgsql-interfaces(at)postgreSQL(dot)org" <pgsql-interfaces(at)postgreSQL(dot)org>
Subject: Re: ODBC driver and Dates
Date: 1998-04-17 08:16:58
Message-ID: 35370FFA.24E582EF@sid.trust.ee (view raw or flat)
Thread:
Lists: pgsql-interfaces
Byron Nikolaidis wrote:
> 
> Hello,
> 
> Yes it is true that there should be something done about supporting other date
> styles.  In making the driver be able to handle all the odbc conversions of
> date, time, and timestamp, assumptions were made about what format they were in.
> 
> It would be relatively easy to modify this to allow for other styles.

I don't think that modifying the driver is the right way to go - there 
is extemsive date-fiddling functionality already present in Windows, 
and duplicating it at wire-protocol level would just confuse things.

I think that the right way would be to force the dates to be in a singe 
format on wire.I assume that you use the ASCII protocol, in binary they 
probably are, but in a platform-dependent way

Therefore my suggestion to set the date format to 'ISO' - i think it 
is easiest to parse being nicely ordered YYYY-MM-DD hh:mm:ss.s+ts

If you have already working US then of course there is no need to change 
it, just have a startup command make the backend aware that the dates 
should be sent in 'US' format.

There is _no_ need to start parsing the dates in different formats, 
it would probably just introduce some subtle bugs.
 
> Should the driver query the database (i.e., "show datestyle") to see what format
> it should use, -OR-, should it be an option for the datasource, where you select
> what style to use, and the driver sets the style when it makes a connection
> ("set datestyle")?

As it will be invisible to the user anyway, I recomment that you just do
a
"set datestyle to 'US'" (assuming you have debugged it for US dates :)
 
> HERE IS A POSSIBLY USEFUL BUT UNDOCUMENTED FEATURE OF THE NEW ODBC DRIVER:
> 
> By the way, there is a feature in this driver that allows you to send simple
> commands to the driver at connection time.  Its registry key is 'ConnSettings'
> and it can be on a driver level or datasource level, or both.
> 
> Here is an example, if you had a datasource called 'co1', then in the registry
> under the HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\co1 you would create a String
> Value called 'ConnSettings' which can contain a list of commands, separated by
> semicolans, to send to Postgres on a successful connection.  Example, "set geqo
> to 'off'; set datestyle to 'ISO' "
> 
> You can also set this on a driver level so that for any datasource these
> commands would be set.  That key is
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL
> and it is also called 'ConnSettings'.
> 
> It will first send the driver 'ConnSettings' (if any) followed by the datasource
> 'ConnSettings' (if any).
> 
> This feature has its limitations.   For example, it can only handle commands;
> not queries that would generate tuples.

Well, that is a useful feature indeed, and I think that this will solve 
the whole problem without changing the driver code. But the install
script 
should set this to "set Datestyle to 'US';" for all of us poor
non-americans 
(and it would'nt hurt those who use the US dates neither).

> P.S., Please send these notes to the pgsql-interfaces(at)postgreSQL(dot)org list so
> that everyone can read them, including me.

Sorry, I answered to a message in the 'old' postodbc list and forgot to
cc.

Hannu

In response to

pgsql-interfaces by date

Next:From: Aleksey DemakovDate: 1998-04-17 08:18:28
Subject: Re: ODBC driver and Dates
Previous:From: Olaf MittelstaedtDate: 1998-04-17 07:14:44
Subject: Re: [INTERFACES] Re: Insight ODBC driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group