Ok, let me see if I can explain this simply. Is there any way to
read/write JDBC date/time/timestamp data through the postgres drivers
WITHOUT having any timezone conversion issues?
I've searched high and low for info on this and not found any
conclusive answers.
What is driving this problem is that we are reading data out of one DB
(MS SQL, using Net Direct drivers), and writing it back into Postgres
8.
In most cases, the NetDirect drivers leave timezone info alone, EXCEPT
when the date in question happens to fall into a daylight savings time
issue. In other words, when reading a "zoneless" time out of the db,
NetDirect says "hey, that's not a valid time, because it falls into
daylight savings time "no mans land" (between 2-3 AM, 1st Sunday of
April), and so it rolls it forward to what it considers a valid time.
The only way we have found to offset this is to tell the JVM we are
running in UTC. Then NetDirect doesn't do the conversion.
However, as soon as we do this, we now have a problem on the Postgres
side - Postgres says "oh, you're running in UTC, but the DB is running
as MST, so I better convert that date for you." Argh. No, that is not
what we want. We can compensate by telling Postgres to run in UTC, but
that's not really what we want to do either (ie. because then, any
code that writes data into the DB has to remember to set its jvm
timezone to UTC as well).
What we really want to do here is just tell the drivers - "leave my
dates alone, pal!" Is there any way to do that? I realize that the
NetDirect behavior is kind of at the root of this, but we haven't
found any way to change that. So please don't just say - "your
screwed". Our goal is to sucessfully migrate to Postgres, here, so I'd
really appreciate solutions rather than finger pointing.
Any suggestions?
Thanks,
Christian