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

Re: Timestamp Summary

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: List <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Timestamp Summary
Date: 2005-07-25 18:58:09
Message-ID: 2F5A3254-76D0-41B5-BE9E-FE798232BB99@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(dot)s(dot)cryder(at)gmail(dot)com> 07/25/05 12:47 PM >>>
>>>>
>>>>
> Hi Kevin,
>
> On 7/25/05, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)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(at)postgresql(dot)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
>
>
>



In response to

pgsql-jdbc by date

Next:From: Dennis GeskerDate: 2005-07-25 19:40:22
Subject: JdbcRowSet Problem
Previous:From: Kevin GrittnerDate: 2005-07-25 18:52:16
Subject: Re: Timestamp Summary

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