Re: Timestamp Summary

On 25-Jul-05, at 2:17 PM, Kevin Grittner wrote:


> Hi Christian,
>
> I don't know what this value is meant to convey, semantically.  In
> your
> time zone, there was no such moment.  If you were converting from a
> database which allowed October 35th in a timestamp column, would you
> feel compelled to preserve the value in the new database, or fix
> it?  If
> it's from a different timezone, it doesn't tell you what moment it
> represents without knowing which timezone.  It seems like you've
> worked
> around this by munging your runtime environment to something other
> than
> the actual timezone it would normally have.  As long as this value
> sits
> in your database, every client which might want to read it (or similar
> values) must munge the runtime environment.
>
> What I'm proposing is that we need a fix so that when mapping a
> Timestamp object, which always represents an unambiguous point in
> time,
> to a "timestamp with time zone" value on the server (which also
> represents an unambiguous point in time), that they match, and when
> mapping a Timestamp object to a "timestamp without time zone" value on
> the server, that the client specify which time zone's
> representation of
> the moment to use.
>

The challenge with this, is that we don't know ahead of time what
type the
underlying data is. If we did this is a trivial problem. Right now we
bind the
parameter in the statement to a timestamptz type. If we knew ahead of
time, we
could easily bind it to a timestamp.

The simplest solution that Christian has is to create two types that
extend PGobject and do exactly as above.




>
> This would give you what you want by simply setting the time zone for
> your client JVM to a non-DST value -- the server setting wouldn't
> matter.  I think it would also solve the problems reported by others.
>
> -Kevin
>
>
>
>
>>>> Christian Cryder <c.s.cryder@gmail.com> 07/25/05 12:47 PM >>>
>>>>
>>>>
> Hi Kevin,
>
> On 7/25/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>
>
>> As someone who is interested in timestamp columns only to hold actual
>> moments in time, I'm very uncomfortable with Christian's proposed
>>
>>
> "fix".
>
> But this isn't how the DB works...from the command line sql interface,
> or via the Statement implementation, you can easily insert "invalid"
> (eg. not-valid-DST) timestamps. So how does this mesh with my data
> integrity concerns - if I read a timestamp from jdbc, and then turn
> around and write that same timestamp, it seems to me the object
> shouldn't get munged. And right now, it does.
>
>
>
>> since you can't actually create a Timestamp object within
>> a JVM set to the correct time zone to represent what he wants
>>
>>
>
> Just to be clear - you CAN create a Timestamp for these objects (it
> just requires having DST turned off in order to do it). And that's
> really the rub - the DB contains data that Timestamp thinks is invalid
> (unless DST is turned off).
>
> We need something more than a "configure both your client and server
> to use the same non-DST timezone", which is currently the only option
> (although my suggestion still requires us to set the client into
> non-DST programatically).
>
> All that said, I am still basically sympathetic with your concern. It
> seems a bit hacky to me too, to be forcing the timezone on the server,
> just so date munging doesn't happen. I'd still like a solution where I
> can re-insert the date without munging, even if the server and the
> client are both running w/ DST turned on. So if someone can think of a
> way to do that, that would be even better...
>
> Christian
>
> ---------------------------(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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>