Re: Timestamp Conversion Woes Redux

Hi Christian,

I believe that the root of your difficulty is that you are asking the
Timestamp object, which represents a moment in time, to represent
something which, in your timezone, is not a valid moment in time. The
alternative to the technique you describe is to use String objects
within Java, which don't much care what they hold, rather than Timestamp
objects with specific semantics which conflict with your values.

I hope you have something that's working for you -- I know how painful
conversions can be.

-Kevin


>>> Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com> 07/21/05 10:13 AM >>>
On 7/20/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I am not sure however that Christian's basic complaint is solvable.
> The problem here is very simple: the semantics of SQL TIMESTAMP
WITHOUT
> TIME ZONE do not match Java's Timestamp, no how, no way. For
instance,
> '2004-04-04 02:30' is an unconditionally valid timestamp-without-zone,
> no matter what anybody's timezone setting is. However, if you try
> to interpret it as local time in a US DST-observing zone, you have a
> problem. You can never convert this value to timestamptz and back in
> a DST-observing zone and not have it change ... which is basically
> what Christian is hoping for. But there is *no* timestamptz value
> that will decode as 02:30, because that's not a valid value for
> timestamptz.

Hi guys,

Here's what we're looking for in a nutshell. The DB allows a date of
'2004-04-04 02:30' in a timestamp column (eg. it may have gotten there
via a manual insert from the sql query tool, etc. - However it got
there, it's there; that's our starting point).

Now, all we want is to be able to read that value via JDBC and then
write it back via JDBC, and have the DB end up with same date that we
started w/ at the beginning. And right now, it's _extremely_ difficult
to accomplish that via the current Postgres driver implementation (we
had to jump through hoops and create a custom PGTimestamp class, then
wrap the postgres prepared statement, and even then, we still have to
programatically turn off DST on the client...it works, but BLECH!)

In my mind, that fact that it is so difficult to do such a simple
thing should give us pause; it's a fundamental data integrity issue at
the root. It's certainly causing us a lot of grief as we try to think
about migrating to Postgres; and I'll bet we're not the only ones who
see this as an issue.

Please don't misunderstand - I realize the JDBC spec is vague, I
understand that Sun's Timestamp implementation may have been poorly
thought out. But at the end of the day, we have a timestamp value in
Postgres - we need to be able to read/write that value without having
it changed on us. We are currently using another DB who's JDBC
implementation doesn't have this problem. We currently have lots and
lots of code using PreparedStatements (so switching to Statements to
work around this is not very realistic). The idea of having to turn
off DST on our severs in order to make this work is also not a viable
option (there are other things running on these boxes, where the time
does matter). My point in all this is that regardless of who's to
blame here, what we really need is a workable solution.

What I was trying to say yesterday is that I think a big part of the
problem hinges on the decision to convert the underlying timestamp
value to a _string_ representation when passing it across the wire;
DST doesn't affect millis, but it does affect the toString()
representation. So if we keep the value in millis when flattening and
then reconstituting, it seems like we could work around the DST issue
(and that right there would be a huge step in the right direction).

Again, I'm not familiar enough w/ the internals of Postgres or even
the JDBC implementation to tell you "this is how you should do it".
I'm just saying that the way the current implementation flattens the
timestamp via toString() seems fundamentally problematic to me. At the
end of the day, I don't really care how we get there - we just need to
be able to read/write timestamp data without having it munged...
hopefully that's not too much to ask.

Hope that helps,
Christian

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster