Re: Timezone conversion woes

Christian,

Did you resolve this ?

Without looking at the code, I am thinking that using an absolute
long for the timestamp might
be the problem. I generally create a calendar and get the date I want
out of it ?

Dave
On 15-Jul-05, at 7:04 PM, Christian Cryder wrote:

> And then there's this. If I change my insert code to use dynamically
> generated SQL via Statement, rather than PreparedStatement, like this:
>
> //insert some sample data
> t = new Timestamp(1112511962000L); //2005-04-03
> 00:06:02
> System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> t = new Timestamp(1112520583000L); //2005-04-03
> 02:29:43
> System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> t = new Timestamp(1112522529000L); //2005-04-03
> 03:02:09
> System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> if (!conn.getAutoCommit()) conn.commit();
>
> the data goes in correctly (no mungin on the last two dates). I get
> the following output after the inserts...
>
> [UID]:58 [TrxTime]:2005-04-03 00:06:02.000 (millis: 1112511962000)
> [UID]:59 [TrxTime]:2005-04-03 02:29:43.000 (millis: 1112520583000)
> [UID]:60 [TrxTime]:2005-04-03 03:02:09.000 (millis: 1112522529000)
>
> So it appears to me there is a bug, either in the PreparedStatement
> code or in the way the DB handles dates set via prepared stmts.
>
> Can anyone verify or comment on this? Any suggestions as to how we
> might fix it, or where I should look? I have no problem trying to
> patch the JDBC code, but I could use a few pointers about where to
> look first...
>
> thanks,
> Christian
>
>
>
> On 7/15/05, Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com> wrote:
>
>> And just a little bit more information. I downloaded the jdbc source,
>> and poked around a little bit to see if I could determine exactly
>> what's going across the wire. I get this...
>>
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 00:06:02.000000-0700>)
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 02:29:43.000000-0700>)
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 03:02:09.000000-0700>)
>>
>> It would appear to me from this that the data is going out of the
>> JDBC
>> drivers correctly, and that if the dates are getting modified (which
>> they are), it's Postgres that's doing it. Can anyone confirm,
>> deny, or
>> correct my thinking here?
>>
>> tia,
>> Christian
>>
>>
>>
>> On 7/15/05, Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com> wrote:
>>
>>> And just in case that example wasn't clear enough, I've tried using
>>> rs.getTimestamp(i) or rs.getTimestamp(i, cal) instead of
>>> rs.getObject(i). Neither of those have any effect.
>>>
>>> The heart of the problem here seems to be that the millis value is
>>> really getting changed on the way to the DB...
>>>
>>> inserting: 2005-04-03 00:06:02.000 (millis: 1112511962000)
>>> inserting: 2005-04-03 02:29:43.000 (millis: 1112520583000)
>>> inserting: 2005-04-03 03:02:09.000 (millis: 1112522529000)
>>> [UID]:16 [TrxTime]:2005-04-03 00:06:02.000 (millis: 1112511962000)
>>> [UID]:17 [TrxTime]:2005-04-03 03:29:43.000 (millis: 1112524183000)
>>> [UID]:18 [TrxTime]:2005-04-03 04:02:09.000 (millis: 1112526129000)
>>>
>>> So I write one thing and get something different back out. That
>>> doesn't seem correct. Surely there is a way to tell Postgres "to
>>> mess
>>> with my data" when you insert it?
>>>
>>> Christian
>>>
>>>
>>>
>>> On 7/15/05, Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com> wrote:
>>>
>>>> Ok, I think I've got a piece of code that dupes my problem:
>>>>
>>>> Here's how I'm creating my table (doesn't seem to matter whether
>>>> I use
>>>> 'with time zone' or not)...
>>>> CREATE TABLE Foo (
>>>> UID SERIAL,
>>>> TrxTime timestamp without time zone NOT NULL
>>>> , PRIMARY KEY (UID)
>>>> );
>>>>
>>>> And here's the code that illustrates the problem...
>>>> //change our timezone so that we are not operating in DST
>>>> (this allows us to
>>>> //get un-munged timestamp values from src db)
>>>> TimeZone curTz = TimeZone.getDefault();
>>>> TimeZone.setDefault(new SimpleTimeZone(curTz.getRawOffset(),
>>>> curTz.getID()));
>>>> System.out.println("current tz:"+TimeZone.getDefault());
>>>>
>>>> //now we're going to write some sample data
>>>> SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd
>>>> HH:mm:ss.SSS");
>>>> ObjectRepository or = ObjectRepository.getGlobalRepository();
>>>> DataSource ds = (DataSource) or.getState
>>>> (AppKeys.DB_SYNC_TARGET);
>>>> Connection conn = null;
>>>> Statement stmt = null;
>>>> PreparedStatement pstmt = null;
>>>> Timestamp t = null;
>>>> try {
>>>> conn = ds.getConnection();
>>>> stmt = conn.createStatement();
>>>>
>>>> //clean up the table
>>>> stmt.execute("DELETE FROM Foo");
>>>>
>>>> //insert some sample data
>>>> pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime)
>>>> VALUES (?)");
>>>> t = new Timestamp(1112511962000L); //2005-04-03
>>>> 00:06:02
>>>> System.out.println("inserting: "+sdf.format(t));
>>>> pstmt.setObject(1, t);
>>>> pstmt.executeUpdate();
>>>> t = new Timestamp(1112520583000L); //2005-04-03
>>>> 02:29:43
>>>> System.out.println("inserting: "+sdf.format(t));
>>>> pstmt.setObject(1, t);
>>>> pstmt.executeUpdate();
>>>> t = new Timestamp(1112522529000L); //2005-04-03
>>>> 03:02:09
>>>> System.out.println("inserting: "+sdf.format(t));
>>>> pstmt.setObject(1, t);
>>>> pstmt.executeUpdate();
>>>> if (!conn.getAutoCommit()) conn.commit();
>>>>
>>>> //now read the values back out
>>>> ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
>>>> while (rs.next()) {
>>>> System.out.println("[UID]:"+rs.getObject(1)+"
>>>> [TrxTime]:"+rs.getObject(2));
>>>> }
>>>> rs.close();
>>>> } catch (SQLException e) {
>>>> System.out.println("Unexpected SQLException: "+e);
>>>> e.printStackTrace();
>>>>
>>>> } finally {
>>>> if (stmt!=null) try {stmt.close();} catch (SQLException
>>>> e) {}
>>>> if (pstmt!=null) try {pstmt.close();} catch
>>>> (SQLException e) {}
>>>> if (conn!=null) try {conn.close();} catch (SQLException
>>>> e) {}
>>>> }
>>>>
>>>> Note that I am running in MST as my default system setting. I
>>>> modify
>>>> this at runtime so that I am NOT using daylight savings. Here's
>>>> what I
>>>> get for output:
>>>>
>>>> inserting: 2005-04-03 00:06:02.000
>>>> inserting: 2005-04-03 02:29:43.000
>>>> inserting: 2005-04-03 03:02:09.000
>>>>
>>>> [UID]:7 [TrxTime]:2005-04-03 00:06:02.0
>>>> [UID]:8 [TrxTime]:2005-04-03 03:29:43.0
>>>> [UID]:9 [TrxTime]:2005-04-03 04:02:09.0
>>>>
>>>> See how the data is getting changed when its written into the DB
>>>> (the
>>>> last 2 timestamps are bumped by an hour). Manually querying the DB
>>>> confirms that it got written in wrong
>>>>
>>>> What appears to be happening is that either the JDBC driver or
>>>> Postgres itself is munging the data on the way in, saying - "since
>>>> Postgres is running in MST w/ DST, I'd better adjust these
>>>> times". And
>>>> that's what I'm trying to avoid - I want it to write exactly what I
>>>> put in, with no adjustments.
>>>>
>>>> Any suggestions?
>>>>
>>>> tia,
>>>> Christian
>>>>
>>>>
>>>>
>>>> On 7/15/05, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>>>
>>>>> Christian,
>>>>>
>>>>> Can you send me a snippet of code that shows me what you are
>>>>> trying
>>>>> to do ?
>>>>>
>>>>> Dave
>>>>> On 14-Jul-05, at 3:25 PM, Christian Cryder wrote:
>>>>>
>>>>>
>>>>>> On 7/14/05, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>>>>>
>>>>>>
>>>>>>> Yeah, create your timestamps without timezones and they will
>>>>>>> not be
>>>>>>> converted.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> Dave, how exactly do you do this? Especially if I am trying to
>>>>>> read a
>>>>>> date out of the db (there is no timezone info there, but by
>>>>>> the time I
>>>>>> access the data via ps.getDate() its already there).
>>>>>>
>>>>>> Any suggestions would be greatly appreciated.
>>>>>>
>>>>>> Thanks,
>>>>>> Christian
>>>>>>
>>>>>> ---------------------------(end of
>>>>>> broadcast)---------------------------
>>>>>> TIP 6: explain analyze is your friend
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>