PostgreSQL JDBC - Timestamp Conversion Woes Redux

Author

Timestamp Conversion Woes Redux

Christian Cryder

2005-07-18, 1:23 pm

Hi Dave (& others),

You said,

> The problem isn't with PreparedStatement, rather with Timestamp itself.
> I ran the following
> Timestamp ts = new Timestamp(1112511962
000L);
> System.out.println(ts);
> it prints out
> 2005-04-03 03:06:02.0

Actually, I still think it is a problem w/ PreparedStatement, and I'll
see if I can explain why, as well as provide a better test case to
illustrate.

First, the reason you got what you did up above is because you are
running in a different timezone than I am. If you were to run your
example where I live (MST), you'd get this:

2005-04-03 00:06:02.0 (MST)
2005-04-03 00:06:02.0 (MST - w/ daylight savings turned off)

See how those values are both the same? Now watch what happens if we
were to run the exact same piece of code in EST:

2005-04-03 03:06:02.0 (EST)
2005-04-03 02:06:02.0 (EST - w/ daylight savings turned off)

See how they are different? What is happening here is that 2:06 is the
actual time (EST is 2 hrs before MST, right?). But on April 3, 2005,
2:06 is not a valid time - because that is right in between when the
time is supposed to be springing forward for daylight savings.

With me so far? My point here is just that the single millisecond
value we are looking at (1112511962000L) can actually be represented
as two different things, depending on your relation to the timezone.

So let's say I read 2005-04-03 02:06:02.0 in from a db - that
timestamp will convert to the millisecond value given above, but then
when we write it back out its going to get rolled forward an hour
because of daylight savings. See the problem? The write just applied
timezone rules to my data and modified it whether I wanted it to or
not.

Now, this is only happening with prepared statements, not w/ regular
statements, and the attached example (below) illustrates that clearly.

Let's switch back to my original example, in MST. There were 3
timestamp strings:
t1: 2005-04-03 00:06:02.000 - before the DST cutoff
t2: 2005-04-03 02:29:43.000 - during the DST rollover
t3: 2005-04-03 03:02:09.000 - after the DST rollover

The point here is that if we are applying timezone rules and DST is
turned on, that second value t2 is actually not valid - it's right in
the middle of the switch. So a timestamp would format it as 03:29:43.
Which is fine - except for the fact that I am reading the date from a
"timestamp without timezone" column.

Now, if I run my example code (see below) with usepstmt = false, the
code uses regular statements and I get the following results:

(usepstmt = false)
------------------
current tz: java.util.SimpleTimeZone[id=MST,offset=- 25200000,dstSavings=
3600000,
useDaylight=false,...<snipped>]

starting t1: 2005-04-03 00:06:02.000
starting t2: 2005-04-03 02:29:43.000
starting t3: 2005-04-03 03:02:09.000

inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)

resulting t1: [UID]:112 [TrxTime]:2005-04-03 00:06:02.0 (millis: 1112511962000)
resulting t2: [UID]:113 [TrxTime]:2005-04-03 02:29:43.0 (millis: 1112520583000)
resulting t3: [UID]:114 [TrxTime]:2005-04-03 03:02:09.0 (millis: 1112522529000)

See how all 3 of the values stay the same? JDBC inserted all 3 dates
exactly as we requested. Now, watch what happens when I use prepared
statements instead:

(usepstmt = true)
------------------
current tz: java.util.SimpleTimeZone[id=MST,offset=- 25200000,dstSavings=
3600000,
useDaylight=false,...<snipped>]

starting t1: 2005-04-03 00:06:02.000
starting t2: 2005-04-03 02:29:43.000
starting t3: 2005-04-03 03:02:09.000

inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)

resulting t1: [UID]:115 [TrxTime]:2005-04-03 00:06:02.0 (millis: 1112511962000)
resulting t2: [UID]:116 [TrxTime]:2005-04-03 03:29:43.0 (millis: 1112524183000)
resulting t3: [UID]:117 [TrxTime]:2005-04-03 04:02:09.0 (millis: 1112526129000)

See what happened? t2 and t3 got bumped forward an hour in the db. So
even though we said "insert 02:29:43" something in the prepared
statement applied some kind of daylight savings logic and rolled those
last two dates forward.

And THAT is where the problem lies. PreparedStatement needs to handle
these dates the same way that regular Statement does.

Does that help explain the issue any better? Please holler if you need
any further information. I am proceeding to try and poke around in the
jdbc source, but I'm not really sure where I should be looking.

This is a HUGE issue for us as we are trying to switch from MS SQL to
Postgres - we have to find a way to fix this issue asap. So if anyone
has any ideas, I am all ears.

Thanks!
Christian
-------------------------------------------
Sample Table...
-------------------------------------------

CREATE TABLE Foo (
UID SERIAL,
TrxTime timestamp without time zone NOT NULL
, PRIMARY KEY (UID)
);

-------------------------------------------
Sample Code...
-------------------------------------------
//set the timezone to MST so that others can easily replicate, then
//install a variant where daylight savings is turned off (this
will allow us to
//see the source dates un-munged, which is important here)
TimeZone.setDefault(TimeZone.getTimeZone("MST"));
TimeZone curTz = TimeZone.getDefault();
TimeZone.setDefault(new SimpleTimeZone(curTz
.getRawOffset(),
curTz.getID())); //if you don't do this, t1 and t2 will get rolled
forward because of DST...
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");
System.out.println("starting t1: "+sdf.format(new
Timestamp(1112511962
000L))); //2005-04-03 00:06:02
System.out.println("starting t2: "+sdf.format(new
Timestamp(1112520583
000L))); //2005-04-03 02:29:43
System.out.println("starting t3: "+sdf.format(new
Timestamp(1112522529
000L))); //2005-04-03 03:02:09

//here we go...
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
Timestamp t = null;
Calendar cal = Calendar.getInstance();
boolean usepstmt = true;
try {
conn = ds.getConnection();
stmt = conn.createStatement();
pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime) VALUES (?)");

//clean up the table
stmt.execute("DELETE FROM Foo");

//insert some sample data
//...2005-04-03 00:06:02 (before the DST cutover)
t = new Timestamp(1112511962
000L);
System.out.println("inserting t1: "+t+" (millis: "+t.getTime()+")");
if (usepstmt) {
pstmt.setTimestamp(1, t);
pstmt.executeUpdate();
} else {
stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
}

//...2005-04-03 02:29:43 (during the DST cutover)
t = new Timestamp(1112520583
000L);
System.out.println("inserting t2: "+t+" (millis: "+t.getTime()+")");
if (usepstmt) {
pstmt.setTimestamp(1, t);
pstmt.executeUpdate();
} else {
stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
}

//...2005-04-03 03:02:09 (after the DST cutover)
t = new Timestamp(1112522529
000L);
System.out.println("inserting t3: "+t+" (millis: "+t.getTime()+")");
if (usepstmt) {
pstmt.setTimestamp(1, t);
pstmt.executeUpdate();
} else {
stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
}

if (!conn.getAutoCommit()) conn.commit();

//now read the values back out
ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
int cntr = 0;
while (rs.next()) {
t = rs.getTimestamp(2);
System.out.println("resulting t"+(++cntr)+":
[UID]:"+rs.getObject(1)+" [TrxTime]:"+t+" (millis: "+t.getTime()+")");
}
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) {}
}

:

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Cramer

2005-07-18, 8:24 pm

Christian,

Try adding protocolVersion=2 to your url and then it will not use
bind, and you will
get logging messages in postgresql logs.

Also what is the timezone setting for your server ?

Dave
On 18-Jul-05, at 1:50 PM, Christian Cryder wrote:

> Hi Dave (& others),
>
> You said,
>
>
> Actually, I still think it is a problem w/ PreparedStatement, and I'll
> see if I can explain why, as well as provide a better test case to
> illustrate.
>
> First, the reason you got what you did up above is because you are
> running in a different timezone than I am. If you were to run your
> example where I live (MST), you'd get this:
>
> 2005-04-03 00:06:02.0 (MST)
> 2005-04-03 00:06:02.0 (MST - w/ daylight savings turned off)
>
> See how those values are both the same? Now watch what happens if we
> were to run the exact same piece of code in EST:
>
> 2005-04-03 03:06:02.0 (EST)
> 2005-04-03 02:06:02.0 (EST - w/ daylight savings turned off)
>
> See how they are different? What is happening here is that 2:06 is the
> actual time (EST is 2 hrs before MST, right?). But on April 3, 2005,
> 2:06 is not a valid time - because that is right in between when the
> time is supposed to be springing forward for daylight savings.
>
> With me so far? My point here is just that the single millisecond
> value we are looking at (1112511962000L) can actually be represented
> as two different things, depending on your relation to the timezone.
>
> So let's say I read 2005-04-03 02:06:02.0 in from a db - that
> timestamp will convert to the millisecond value given above, but then
> when we write it back out its going to get rolled forward an hour
> because of daylight savings. See the problem? The write just applied
> timezone rules to my data and modified it whether I wanted it to or
> not.
>
> Now, this is only happening with prepared statements, not w/ regular
> statements, and the attached example (below) illustrates that clearly.
>
> Let's switch back to my original example, in MST. There were 3
> timestamp strings:
> t1: 2005-04-03 00:06:02.000 - before the DST cutoff
> t2: 2005-04-03 02:29:43.000 - during the DST rollover
> t3: 2005-04-03 03:02:09.000 - after the DST rollover
>
> The point here is that if we are applying timezone rules and DST is
> turned on, that second value t2 is actually not valid - it's right in
> the middle of the switch. So a timestamp would format it as 03:29:43.
> Which is fine - except for the fact that I am reading the date from a
> "timestamp without timezone" column.
>
> Now, if I run my example code (see below) with usepstmt = false, the
> code uses regular statements and I get the following results:
>
> (usepstmt = false)
> ------------------
> current tz: java.util.SimpleTimeZone
> [id=MST,offset=- 25200000,dstSavings=
3600000,
> useDaylight=false,...<snipped>]
>
> starting t1: 2005-04-03 00:06:02.000
> starting t2: 2005-04-03 02:29:43.000
> starting t3: 2005-04-03 03:02:09.000
>
> inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
> inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
> inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)
>
> resulting t1: [UID]:112 [TrxTime]:2005-04-03 00:06:02.0 (millis:
> 1112511962000)
> resulting t2: [UID]:113 [TrxTime]:2005-04-03 02:29:43.0 (millis:
> 1112520583000)
> resulting t3: [UID]:114 [TrxTime]:2005-04-03 03:02:09.0 (millis:
> 1112522529000)
>
> See how all 3 of the values stay the same? JDBC inserted all 3 dates
> exactly as we requested. Now, watch what happens when I use prepared
> statements instead:
>
> (usepstmt = true)
> ------------------
> current tz: java.util.SimpleTimeZone
> [id=MST,offset=- 25200000,dstSavings=
3600000,
> useDaylight=false,...<snipped>]
>
> starting t1: 2005-04-03 00:06:02.000
> starting t2: 2005-04-03 02:29:43.000
> starting t3: 2005-04-03 03:02:09.000
>
> inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
> inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
> inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)
>
> resulting t1: [UID]:115 [TrxTime]:2005-04-03 00:06:02.0 (millis:
> 1112511962000)
> resulting t2: [UID]:116 [TrxTime]:2005-04-03 03:29:43.0 (millis:
> 1112524183000)
> resulting t3: [UID]:117 [TrxTime]:2005-04-03 04:02:09.0 (millis:
> 1112526129000)
>
> See what happened? t2 and t3 got bumped forward an hour in the db. So
> even though we said "insert 02:29:43" something in the prepared
> statement applied some kind of daylight savings logic and rolled those
> last two dates forward.
>
> And THAT is where the problem lies. PreparedStatement needs to handle
> these dates the same way that regular Statement does.
>
> Does that help explain the issue any better? Please holler if you need
> any further information. I am proceeding to try and poke around in the
> jdbc source, but I'm not really sure where I should be looking.
>
> This is a HUGE issue for us as we are trying to switch from MS SQL to
> Postgres - we have to find a way to fix this issue asap. So if anyone
> has any ideas, I am all ears.
>
> Thanks!
> Christian
> -------------------------------------------
> Sample Table...
> -------------------------------------------
>
> CREATE TABLE Foo (
> UID SERIAL,
> TrxTime timestamp without time zone NOT NULL
> , PRIMARY KEY (UID)
> );
>
> -------------------------------------------
> Sample Code...
> -------------------------------------------
> //set the timezone to MST so that others can easily replicate,
> then
> //install a variant where daylight savings is turned off (this
> will allow us to
> //see the source dates un-munged, which is important here)
> TimeZone.setDefault(TimeZone.getTimeZone("MST"));
> TimeZone curTz = TimeZone.getDefault();
> TimeZone.setDefault(new SimpleTimeZone(curTz
.getRawOffset(),
> curTz.getID())); //if you don't do this, t1 and t2 will get rolled
> forward because of DST...
> 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");
> System.out.println("starting t1: "+sdf.format(new
> Timestamp(1112511962
000L))); //2005-04-03 00:06:02
> System.out.println("starting t2: "+sdf.format(new
> Timestamp(1112520583
000L))); //2005-04-03 02:29:43
> System.out.println("starting t3: "+sdf.format(new
> Timestamp(1112522529
000L))); //2005-04-03 03:02:09
>
> //here we go...
> Connection conn = null;
> Statement stmt = null;
> PreparedStatement pstmt = null;
> Timestamp t = null;
> Calendar cal = Calendar.getInstance();
> boolean usepstmt = true;
> try {
> conn = ds.getConnection();
> stmt = conn.createStatement();
> pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime)
> VALUES (?)");
>
> //clean up the table
> stmt.execute("DELETE FROM Foo");
>
> //insert some sample data
> //...2005-04-03 00:06:02 (before the DST cutover)
> t = new Timestamp(1112511962
000L);
> System.out.println("inserting t1: "+t+" (millis: "+t.getTime
> ()+")");
> if (usepstmt) {
> pstmt.setTimestamp(1, t);
> pstmt.executeUpdate();
> } else {
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> }
>
> //...2005-04-03 02:29:43 (during the DST cutover)
> t = new Timestamp(1112520583
000L);
> System.out.println("inserting t2: "+t+" (millis: "+t.getTime
> ()+")");
> if (usepstmt) {
> pstmt.setTimestamp(1, t);
> pstmt.executeUpdate();
> } else {
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> }
>
> //...2005-04-03 03:02:09 (after the DST cutover)
> t = new Timestamp(1112522529
000L);
> System.out.println("inserting t3: "+t+" (millis: "+t.getTime
> ()+")");
> if (usepstmt) {
> pstmt.setTimestamp(1, t);
> pstmt.executeUpdate();
> } else {
> stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
> }
>
> if (!conn.getAutoCommit()) conn.commit();
>
> //now read the values back out
> ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
> int cntr = 0;
> while (rs.next()) {
> t = rs.getTimestamp(2);
> System.out.println("resulting t"+(++cntr)+":
> [UID]:"+rs.getObject(1)+" [TrxTime]:"+t+" (millis: "+t.getTime()+")");
> }
> 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) {}
> }
>
>
> :
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-07-18, 8:24 pm

Christian Cryder <c.s.cryder@gmail.com> writes:
[color=darkred]
> Actually, I still think it is a problem w/ PreparedStatement, and I'll
> see if I can explain why, as well as provide a better test case to
> illustrate.

I'm hardly a JDBC expert, but I recall some considerable discussion
awhile back about how JDBC ought to map Java's (one) Timestamp type
into Postgres' TIMESTAMP WITH/WITHOUT TIME ZONE types, neither of
which apparently match the semantics of Timestamp very well. You
should go digging in the pgsql-jdbc archives for background.

As for the problem at hand, I suspect that the driver is prespecifying
the parameter data type as either TIMESTAMP WITH or TIMESTAMP WITHOUT
TIME ZONE, and that whichever choice is used is different from what
the server would infer without the prespecification. This would in
turn affect what the server assumes about the timezone spec (or lack
of one) in the supplied input string. Worse, there could be an
ensuing run-time conversion between the two data types, leading to
adding or subtracting your local GMT offset.

(For that matter, is the parameter being sent in text or binary?
If it's binary then most of these theories fall to the ground...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer

2005-07-18, 8:24 pm

Tom,

You are correct, the driver is binding it to a timestamptz. The
underlying data is without
timezone so the server probably attempts to remove it

I'm starting to think that binding the type to "unknown" might be
better as you suggested earlier.

Dave
On 18-Jul-05, at 3:26 PM, Tom Lane wrote:

> Christian Cryder <c.s.cryder@gmail.com> writes:
>
>
>
>
> I'm hardly a JDBC expert, but I recall some considerable discussion
> awhile back about how JDBC ought to map Java's (one) Timestamp type
> into Postgres' TIMESTAMP WITH/WITHOUT TIME ZONE types, neither of
> which apparently match the semantics of Timestamp very well. You
> should go digging in the pgsql-jdbc archives for background.
>
> As for the problem at hand, I suspect that the driver is prespecifying
> the parameter data type as either TIMESTAMP WITH or TIMESTAMP WITHOUT
> TIME ZONE, and that whichever choice is used is different from what
> the server would infer without the prespecification. This would in
> turn affect what the server assumes about the timezone spec (or lack
> of one) in the supplied input string. Worse, there could be an
> ensuing run-time conversion between the two data types, leading to
> adding or subtracting your local GMT offset.
>
> (For that matter, is the parameter being sent in text or binary?
> If it's binary then most of these theories fall to the ground...)
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

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

Oliver Jowett

2005-07-18, 8:24 pm

Tom Lane wrote:

> (For that matter, is the parameter being sent in text or binary?
> If it's binary then most of these theories fall to the ground...)

Text -- the only type the driver currently uses binary parameters for is
bytea, from memory.

-O

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

Oliver Jowett

2005-07-18, 8:24 pm

Dave Cramer wrote:

> You are correct, the driver is binding it to a timestamptz. The
> underlying data is without
> timezone so the server probably attempts to remove it
>
> I'm starting to think that binding the type to "unknown" might be
> better as you suggested earlier.

Yeah, given that we can't work out which of the two types is intended
ahead of time, this might be the best idea..

Christian, you should be able to test this easily enough by modifying
setTimestamp() to pass Oid.UNKNOWN instead of Oid.TIMESTAMPTZ to
bindString().

-O

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

Dave Cramer

2005-07-19, 7:24 am

I'm also thinking we should use UNKOWN for setString as well,
hopefully this would reduce the number of upgrade problems people are
having when they upgrade from 7.x to 8.x

Dave
On 18-Jul-05, at 7:13 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
>
> Yeah, given that we can't work out which of the two types is intended
> ahead of time, this might be the best idea..
>
> Christian, you should be able to test this easily enough by modifying
> setTimestamp() to pass Oid.UNKNOWN instead of Oid.TIMESTAMPTZ to
> bindString().
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-07-19, 7:24 am

Dave Cramer wrote:
> I'm also thinking we should use UNKOWN for setString as well, hopefully
> this would reduce the number of upgrade problems people are having when
> they upgrade from 7.x to 8.x

I still think this is a bad idea.

-O

---------------------------(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

Dave Cramer

2005-07-19, 7:24 am

Oliver,

Can you explain your rationale ?

Mine is:

The overhead isn't that bad.
Anyone using the API correctly will not be affected ( assuming they
use setInt() etal correctly )

Philosophically speaking, making the driver more strict does not make
it easier for people to convert to postgresql. AFAICT, most drivers
tend to be more lenient.

Dave

On 19-Jul-05, at 8:23 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
> I still think this is a bad idea.
>
> -O
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett

2005-07-19, 7:24 am

Dave Cramer wrote:
> Can you explain your rationale ?

I don't really want to rehash this again and again, but it boils down
to: if setString() does not type the parameter as a string, how *do* I
type a parameter as a string?

I do not find "there are lots of broken applications out there" a
particularly convincing argument for changing this.

-O

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

Dave Cramer

2005-07-19, 9:23 am

Oliver,

Sorry for bringing this up again. I realize we have discussed this
before.

by typing it as UNKNOWN we are letting the backend decide what to do
with it. There is considerable code in there to do the "right" thing.

Dave
On 19-Jul-05, at 8:45 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
> I don't really want to rehash this again and again, but it boils
> down to: if setString() does not type the parameter as a string,
> how *do* I type a parameter as a string?
>
> I do not find "there are lots of broken applications out there" a
> particularly convincing argument for changing this.
>
> -O
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-07-19, 9:23 am

Dave Cramer wrote:

> by typing it as UNKNOWN we are letting the backend decide what to do
> with it. There is considerable code in there to do the "right" thing.

So you are suggesting we type all parameters as UNKNOWN regardless of
which setXXX() accessor was used to set them?

If not, why is setString() special?

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer

2005-07-19, 9:23 am

Oliver,

setString is "special" because historically this is what we see in
the upgrade problems. People try to insert a date using setString
('dateval') etc.

Also we have historically told people to use setString to insert pg
specific types, when there was no corresponding java type.

I am not suggesting that we type all parameters as UNKNOWN only
String parameters.

Dave
On 19-Jul-05, at 9:07 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
>
> So you are suggesting we type all parameters as UNKNOWN regardless
> of which setXXX() accessor was used to set them?
>
> If not, why is setString() special?
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett

2005-07-19, 9:23 am

Dave Cramer wrote:

> setString is "special" because historically this is what we see in the
> upgrade problems. People try to insert a date using setString
> ('dateval') etc.

And we're back to: I think the right solution here is for people to fix
their broken apps, not to introduce unpredictable behaviour in the driver.

> Also we have historically told people to use setString to insert pg specific types, when there was no corresponding java type.

That's a fair point.

We really need a proper way for specifying this anyway; I had a trivial
PGobject implementation at one point that did the trick, but Kris didn't
like the other bits I did in that patch and I never got around to
implementing it separately.

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-07-19, 9:23 am

Oliver Jowett <oliver@opencloud.com> writes:
> Dave Cramer wrote:
[color=darkred]
> I still think this is a bad idea.

I think one main point against using UNKNOWN is that it creates a risk
of "could not resolve parameter type" query failures. That's OK for
generic setString() cases, since the user can always escape the failure
by changing his code to specify the parameter type more clearly.

The other argument against UNKNOWN is that the backend might choose an
unexpected data type. Again, that doesn't scare me a lot for setString,
because the backend's rules for dealing with UNKNOWN are biased in favor
of resolving the parameter type as TEXT, which seems perfectly
reasonable for setString cases.

Unfortunately, both of these considerations speak *against* using
UNKNOWN for Timestamp. If the backend rejects the query --- or more
likely, makes the wrong datatype choice --- there will be no way for
the user to fix it.

So I'm in favor of using UNKNOWN for setString, but I think we gotta
find another answer for Christian's problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-07-19, 9:23 am

Tom Lane wrote:

> I think one main point against using UNKNOWN is that it creates a risk
> of "could not resolve parameter type" query failures. That's OK for
> generic setString() cases, since the user can always escape the failure
> by changing his code to specify the parameter type more clearly.

> The other argument against UNKNOWN is that the backend might choose an
> unexpected data type. Again, that doesn't scare me a lot for setString,
> because the backend's rules for dealing with UNKNOWN are biased in favor
> of resolving the parameter type as TEXT, which seems perfectly
> reasonable for setString cases.

The main thing I'm worried about there is that if there are cases where
an UNKNOWN parameter will generate an error rather than resolve to TEXT,
then the driver has just backed the user into a corner they can't escape
from. Are there any cases where this can happen? (I'm thinking of some
of the ambiguous-type problems we ran into when sending nulls as UNKNOWN..)

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2005-07-19, 9:23 am

Oliver Jowett <oliver@opencloud.com> writes:
> The main thing I'm worried about there is that if there are cases where
> an UNKNOWN parameter will generate an error rather than resolve to TEXT,
> then the driver has just backed the user into a corner they can't escape
> from. Are there any cases where this can happen?

There are some, for instance someone was just complaining about this:

template1=# select 1 where 5 in (select '');
ERROR: failed to find conversion function from "unknown" to integer

My previous response was based on what was in my inbox, which I now see
wasn't the whole thread. I agree with you that if we make setString
default to UNKNOWN, there had better be a way to say "by golly this
really is TEXT" for the corner cases. It'd be a good idea if it wasn't
limited to TEXT, either, but could allow specification of any random
datatype.

I believe that UNKNOWN will work fine for 99% of cases out there,
because the backend's algorithms have been tuned for years to generally
do the right thing when presented with unadorned literal strings ---
but there has to be an escape hatch for the other 1%.

Of course, there's always the escape hatch of changing the query text,
that is write
select ... cast(? as text)
or
select ... ?::text
but this seems pretty crude.

regards, tom lane

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

Csaba Nagy

2005-07-19, 9:23 am

Hi all,

My opinion as a user who just "fixed" his application to properly set
the data base types: fixing the application is a bigger PITA than fixing
weird cases of strange type choices of the server. The first one
involves changing lots of exiting code, the second involves newly
written code, as I think the old code relying on setting string and
letting the server decide what is it should work fine.
In my case I've had to make extra meta data look-ups to be able to
properly set the data type in some of our more generic code, which is
adding some complexity overhead to my code (I presume the performance
difference is negligible). Given the fact that the server is very likely
to decide correctly the type, and a lot of code can be simpler if no
exact knowledge of the data types is needed, I think setting the type to
unknown should be acceptable.
Actually, will this allow to use setString on a boolean field using
preaparedStatements ? 'cause that's the one which gave me the headaches.

Just my 2c.

Cheers,
Csaba.

On Tue, 2005-07-19 at 16:03, Oliver Jowett wrote:
> Tom Lane wrote:
>
>
>
> The main thing I'm worried about there is that if there are cases where
> an UNKNOWN parameter will generate an error rather than resolve to TEXT,
> then the driver has just backed the user into a corner they can't escape
> from. Are there any cases where this can happen? (I'm thinking of some
> of the ambiguous-type problems we ran into when sending nulls as UNKNOWN..)
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

---------------------------(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

Csaba Nagy

2005-07-19, 9:23 am

& #91;snip]
> wasn't the whole thread. I agree with you that if we make setString
> default to UNKNOWN, there had better be a way to say "by golly this
> really is TEXT" for the corner cases. It'd be a good idea if it wasn't
> limited to TEXT, either, but could allow specification of any random
> datatype.

This makes me think, isn't it possible to introduce a special type to
say something like: setObject(..., Types.UNKNOWN), and map that to
setting a string with type unknown ? In that case people could still use
prepared statements with parameters of unknown type, it just have to be
explicit. For me that would have been a much simpler "fixing the app".

Cheers,
Csaba.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dave Cramer

2005-07-19, 11:24 am

What about creating two extension classes

PGTimestamp, and PGTimestamptz

then allowing setObject to recognize these internally and bind to
Oid.Timestamp, and Oid.Timestamptz repectively

for setString I am in favour of using UNKNOWN as this is no worse
than what 7.4 drivers do now

Dave
On 19-Jul-05, at 9:54 AM, Tom Lane wrote:

> Oliver Jowett <oliver@opencloud.com> writes:
>
>
>
>
> I think one main point against using UNKNOWN is that it creates a risk
> of "could not resolve parameter type" query failures. That's OK for
> generic setString() cases, since the user can always escape the
> failure
> by changing his code to specify the parameter type more clearly.
>
> The other argument against UNKNOWN is that the backend might choose an
> unexpected data type. Again, that doesn't scare me a lot for
> setString,
> because the backend's rules for dealing with UNKNOWN are biased in
> favor
> of resolving the parameter type as TEXT, which seems perfectly
> reasonable for setString cases.
>
> Unfortunately, both of these considerations speak *against* using
> UNKNOWN for Timestamp. If the backend rejects the query --- or more
> likely, makes the wrong datatype choice --- there will be no way for
> the user to fix it.
>
> So I'm in favor of using UNKNOWN for setString, but I think we gotta
> find another answer for Christian's problem.
>
> regards, tom lane
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dave Cramer

2005-07-19, 11:24 am

For that matter we could use a PGUnknown type as well.

Dave.
On 19-Jul-05, at 10:52 AM, Csaba Nagy wrote:

> [snip]
>
>
> This makes me think, isn't it possible to introduce a special type to
> say something like: setObject(..., Types.UNKNOWN), and map that to
> setting a string with type unknown ? In that case people could
> still use
> prepared statements with parameters of unknown type, it just have
> to be
> explicit. For me that would have been a much simpler "fixing the app".
>
> Cheers,
> Csaba.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Csaba Nagy

2005-07-19, 11:24 am

Ok, that would be more type safe too. For my code it doesn't matter
anymore though, I've already fixed it to set the right type... but it
would have saved me about 2 days of work.

Cheers,
Csaba.

On Tue, 2005-07-19 at 17:31, Dave Cramer wrote:
> For that matter we could use a PGUnknown type as well.
>
> Dave.
> On 19-Jul-05, at 10:52 AM, Csaba Nagy wrote:
>
>

---------------------------(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

Kris Jurka

2005-07-19, 11:24 am

On Tue, 19 Jul 2005, Dave Cramer wrote:

> What about creating two extension classes
>
> PGTimestamp, and PGTimestamptz
>
> then allowing setObject to recognize these internally and bind to
> Oid.Timestamp, and Oid.Timestamptz repectively
>

Who is going to really use these unportable things instead of standard
Timestamps? People who are desperate to get their application to work
around driver bugs? I don't believe timestamp/timestamptz is an
intractable problem and in the previous discussion I recall suggesting
setting the server time zone to match the client's would fix this problem.
I also seem to recall a number of other timezone related issues. Let's
focus on trying to fix what we've got before we go down this unportable
route. I'll put together at test cases for the bugs I'm aware of tonight
so we can focus this discussion on the real problems.

Kris Jurka

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

Christian Cryder

2005-07-19, 11:24 am

Hi Kris,

Dave and I have been talking about this a lot over the past couple of
days, and I've provided some sample code that will clearly illustrate
the problem. At the heart of the issue is the fact that there needs to
be a way to insert Timestamps into the DB using PreparedStatements,
where you can indicate to the DB "do not munge this date, thank you
very much". Right now, if you are using Timestamps, that will happen
regardless of whether you are declaring the timestamp with or without
timezone.

So to summarize: the sql standard has the notion of 2 kinds of
timestamp (w/, w/out timezone). The JDBC API on the other hand doesn't
- it's just "timestamp". And so the drivers have to make assumptions
about how to send the data across. The current implementation (which
uses TIMESTAMPTZ) _guarantees_ the server will remap the time. And
that is very much a problem (as my sample code illustrates, especially
when you are trying to store zoneless times).

As an example: consider what happens when you try and insert a time
like 2005-04-03 2:39:00. If daylight savings is turned on, this is not
a valid time, because it falls between 2 and 3 AM on the first Sunday
in April. So when you try and insert/update it into the db, it will
"adjust" it for you. But if this is zoneless data, then it's perfectly
legit. Unfortunately, there's no way via the JDBC PreparedStatement
(as currently implemented) to keep that munging from happening. And
that is very much a problem.

I should not have to configure my server to turn daylight savings off
in order to get stuff to insert into the DB correctly (after all,
other things run on the same server).

From my perspective, there needs to be a way for the JDBC driver to
know what type of timestamp you are working with. That information
resides in the DB, so you _could_ query the metadata info to get that
info up front. The problem w/ that of course is that it introduces a
performance penalty (unless you can come up w/ some kind of caching
strategy). So the only other option is to pass in some kind of hint to
the JDBC driver on the client side.

I'm not wild about referencing Postgres specific types in our code,
but if that's the only alternative, I can definitely live with it. I
DON'T like the idea of having to shove timestamps in via setString(),
because it seems hacky and counterintuitive (after all, they aren't
Strings!). It still seems to me that the easiest solution would be to
have setTimestamp() map with type UNKNOWN - the decision would get
delegated to the server, based on the column type definition, and the
only downside is that if someone tried to insert a timestamp into a
String column, it would work without erring (which it would do anyway
if we tweaked setString to use type UNKNOWN).

So those are my thoughts. Please feel free to holler if anything isn't clear...

>
> Kris Jurka
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Oliver Jowett

2005-07-19, 8:24 pm

Csaba Nagy wrote:
> [snip]
>
>
>
> This makes me think, isn't it possible to introduce a special type to
> say something like: setObject(..., Types.UNKNOWN),

I expect you can do this already with a PGobject implementantion. It
might be nice to make it directly support String too.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2005-07-19, 8:24 pm

Tom Lane wrote:

> My previous response was based on what was in my inbox, which I now see
> wasn't the whole thread. I agree with you that if we make setString
> default to UNKNOWN, there had better be a way to say "by golly this
> really is TEXT" for the corner cases. It'd be a good idea if it wasn't
> limited to TEXT, either, but could allow specification of any random
> datatype.

We already have a mechanism for setting values of arbitary type: pass a
PGobject implementation to setObject(). We could extend this easily so
that passing a String with Types.UNKNOWN is a shorthand for passing a
PGobject with a type of 'unknown'.

It seems very odd to have a situation where the standard JDBC way of
setting a VARCHAR parameter -- setString -- sometimes doesn't work and
you have to go and use a non-standardi postgresql-specific method to say
"no, really, this *is* VARCHAR".

If we change setString() then we've gone from the existing case where
correct applications work and broken applications break, to a situation
where some correct applications do *not* work and must add this
nonstandard code to continue to work, and broken applications have the
breakage hidden.

I can understand why people want this from the perspective of supporting
existing apps that expect implicit casting of string parameters, but
from a driver-correctness and new-development perspective it seems all
wrong.

Maybe a driver URL parameter that changes the behaviour of setString()
is a reasonable compromise between the two?

-O

---------------------------(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

Oliver Jowett

2005-07-19, 8:24 pm

Dave Cramer wrote:
> For that matter we could use a PGUnknown type as well.

This is exactly what the patch I mentioned earlier did, IIRC.

-O

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

Dave Cramer

2005-07-19, 8:24 pm

Christian is perfectly happy with

a PGTimestamp, and a PGTimestamptz type.

I did some research and Oracle has an extended PreparedStatement, which
implements

setTimestamp, and setTimestamptz, so I imagine they have run into the
same issues

Dave
On 19-Jul-05, at 6:59 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
> This is exactly what the patch I mentioned earlier did, IIRC.
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jeffrey Tenny

2005-07-19, 8:24 pm

Well, Oliver and Tom are very deep into things from their perspective,
and I just wanted to offer an opinion from the legions of "app"
developers. My opinion might not be what you expect, since I care less
about compatability than some other things.

What I care about most is that:

1) The conventions for standards-based constructs follow
standards-based rules of engagement, so that if I ever
get a clue about what the standard is, my code will be portable.

2) The code perform like screamin' demons. If setting it to
UNKNOWN slows down all my parameterized query invocations,
I'd rather not see that.

3) If setString() used to work for timestamps and doesn't work
now because of standards compliance changes,
please just tutor me on the correct standards based call to make.

4) If the combined JDBC/backend stuff, when used correctly, is
screwing up timezones, that's mission critical and needs to be fixed.

As far as application portability, I suspect most purveyors of
heavy weight database apps view most any software upgrade, much less a
complete vendor change, to be a "platform port" and so can fix these
little incompatibilities as they debug the rest of their
incompatibilities. (Whether it's adding "WITHOUT OIDS" to my pgsql
CREATE TABLE statements for or "TYPE=INNODB" in futile attempts to get
MySQL to give me transactionally safe tables).

Heck, I'm still using 7.3. I skipped 7.4 because of transactions being
stale on pooled connections, and I've been axiously looking forward to
8.0, but not if my timezones are shot to hell. In all this discussion I
seem to have missed whether they're actually broken or whether I just
need to make a "correct" call.

Thanks for listening, and any further clarifications about my confusions
are welcome.

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

Dave Cramer

2005-07-19, 8:24 pm

On 19-Jul-05, at 7:11 PM, Jeffrey Tenny wrote:

> Well, Oliver and Tom are very deep into things from their perspective,
> and I just wanted to offer an opinion from the legions of "app"
> developers. My opinion might not be what you expect, since I care
> less about compatability than some other things.
>
> What I care about most is that:
>
> 1) The conventions for standards-based constructs follow
> standards-based rules of engagement, so that if I ever
> get a clue about what the standard is, my code will be portable.

Well .... it will be based on the standard, but portable maybe, maybe
not.
There is considerable ambiguity in the standard, and other drivers
may implement
differently.
>
> 2) The code perform like screamin' demons. If setting it to
> UNKNOWN slows down all my parameterized query invocations,
> I'd rather not see that.
You wouldn't notice the affect of this.
>
> 3) If setString() used to work for timestamps and doesn't work
> now because of standards compliance changes,
> please just tutor me on the correct standards based call to make.
setTimestamp is the correct call to make
>
> 4) If the combined JDBC/backend stuff, when used correctly, is
> screwing up timezones, that's mission critical and needs to be
> fixed.

The challenge is this:

There is only one setTimestamp and there are two types of timestamps

1) timestamp with time zone
2) timestamp without time zone

Currently setTimestamp binds the parameter to timestamp with time
zone (timestamptz), the
problem arises when the underlying data type is a timestamp without
time zone (timestamp)
The backend automatically casts the timestamptz to a timestamp as per
the comments in the
backend code

/* timestamptz_timestam
p()
* Convert timestamp at GMT to local timestamp
*/

Dave

>
>
> As far as application portability, I suspect most purveyors of
> heavy weight database apps view most any software upgrade, much
> less a complete vendor change, to be a "platform port" and so can
> fix these little incompatibilities as they debug the rest of their
> incompatibilities. (Whether it's adding "WITHOUT OIDS" to my pgsql
> CREATE TABLE statements for or "TYPE=INNODB" in futile attempts to
> get MySQL to give me transactionally safe tables).
>
> Heck, I'm still using 7.3. I skipped 7.4 because of transactions
> being stale on pooled connections, and I've been axiously looking
> forward to 8.0, but not if my timezones are shot to hell. In all
> this discussion I
> seem to have missed whether they're actually broken or whether I
> just need to make a "correct" call.
>
> Thanks for listening, and any further clarifications about my
> confusions are welcome.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

Oliver Jowett

2005-07-19, 8:24 pm

Christian Cryder wrote:

> From my perspective, there needs to be a way for the JDBC driver to
> know what type of timestamp you are working with. [...]

How about this mapping?

setTimestamp(int,Tim
estamp) -> timestamp
setTimestamp(int,Tim
estamp,Calendar) -> timestamptz

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dave Cramer

2005-07-20, 3:24 am

Christian suggested this:

However I think this too opaque.

Not to mention the fact that it changes the
current behaviour.

Dave
On 19-Jul-05, at 9:41 PM, Oliver Jowett wrote:

> Christian Cryder wrote:
>
>
>
> How about this mapping?
>
> setTimestamp(int,Tim
estamp) -> timestamp
> setTimestamp(int,Tim
estamp,Calendar) -> timestamptz
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

---------------------------(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

Oliver Jowett

2005-07-20, 3:24 am

Dave Cramer wrote:
> Christian suggested this:
>
> However I think this too opaque.

Why do you think this? There's no timezone information associated with a
Timestamp, so it seems like the logical mapping: if you provide a
timezone via Calendar, it's a timestamp-with-timezone; otherwise, it's a
timestamp-without-timezone.

> Not to mention the fact that it changes the
> current behaviour.

Err, given that the current behaviour is broken, is this a problem?

Every time I've looked at the timestamp code I've gone "ow, that has to
be broken" but never got around to investigating further .. IMO, this is
an area that the driver just gets *wrong* and we should be fixing it so
it works, not trying to support applications that expect the wrong
behaviour!

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Dave Cramer

2005-07-20, 3:24 am

On 19-Jul-05, at 10:02 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
> Why do you think this? There's no timezone information associated
> with a
> Timestamp, so it seems like the logical mapping: if you provide a
> timezone via Calendar, it's a timestamp-with-timezone; otherwise,
> it's a
> timestamp-without-timezone.

Well, we're in a vague area of the spec here. There are two TIMESTAMP
types defined by the sql
spec, and only one setTimestamp. There is no indication by the spec
that this behaviour is the "right" behaviour.

>
>
>
> Err, given that the current behaviour is broken, is this a problem?

Well, depends on what we break by "fixing" it.
I still have access to the box and can re-run the cts to make sure it
still passes.

>
> Every time I've looked at the timestamp code I've gone "ow, that
> has to
> be broken" but never got around to investigating further .. IMO,
> this is
> an area that the driver just gets *wrong* and we should be fixing
> it so
> it works, not trying to support applications that expect the wrong
> behaviour!
>
Interestingly enough I implemented PGTimestamp, and PGTimestamptz and
ran his test case.
It passed in both cases and did the right thing. I'm still
investigating why.

Dave
> -O
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Cramer

2005-07-20, 3:24 am

It appears all we have to do is remove the addition of the timezone
information in setTimestamp(n, ts)

and leave it for setTimestamp(n, ts, cal)

This only fails our internal tests at +- infinity which is tractable.

Dave
On 19-Jul-05, at 10:27 PM, Dave Cramer wrote:

>
> On 19-Jul-05, at 10:02 PM, Oliver Jowett wrote:
>
>
>
> Well, we're in a vague area of the spec here. There are two
> TIMESTAMP types defined by the sql
> spec, and only one setTimestamp. There is no indication by the spec
> that this behaviour is the "right" behaviour.
>
>
>
>
> Well, depends on what we break by "fixing" it.
> I still have access to the box and can re-run the cts to make sure
> it still passes.
>
>
>
> Interestingly enough I implemented PGTimestamp, and PGTimestamptz
> and ran his test case.
> It passed in both cases and did the right thing. I'm still
> investigating why.
>
> Dave
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-07-20, 3:24 am

Dave Cramer wrote:

> Well, we're in a vague area of the spec here. There are two TIMESTAMP
> types defined by the sql spec,

right.

> and only one setTimestamp.

The SQL spec doesn't define setTimestamp at all. The JDBC spec defines
*two* setTimestamp methods.

> There is no indication by the spec
> that this behaviour is the "right" behaviour.

The javadoc for the with-Calendar variant is quite clear:
[color=darkred]

I think it's pretty clear that this variant must map to timestamptz.

The javadoc for the no-Calendar variant is more vague:
[color=darkred]

I'm willing to take that as license to map it to timestamp as there's no
mention of timezone at all, no way to supply one, and "SQL TIMESTAMP"
defaults to WITHOUT TIME ZONE I believe.

If you want to set a TIMESTAMP WITH TIMEZONE using the default timezone,
the JDBC spec seems to want you to call setTimestamp(i, timestamp, null)
(see the first javadoc excerpt above -- "if no calendar object is
specified"). The current driver also does this for
setTimestamp(i,times
tamp), which is what I'd like to change.

(and also fix setTimestamp(i,times
tamp,calendar) to actually preserve
the timezone info you give it rather than mashing it into the default
timezone, but that's a separate issue..)

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Oliver Jowett

2005-07-20, 3:24 am

Kris Jurka wrote:

> ! { "TimeZone", java.util.TimeZone.getDefault().getID()}

Ew! How much existing code is that going to break?

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dave Cramer

2005-07-20, 7:24 am

On 19-Jul-05, at 11:00 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
>
> right.
>
>
>
> The SQL spec doesn't define setTimestamp at all. The JDBC spec defines
> *two* setTimestamp methods.
This was implied...
>
>
>
> The javadoc for the with-Calendar variant is quite clear:
>
>
>
> I think it's pretty clear that this variant must map to timestamptz.

Well to add more confusion here is the excerpt from Sun's JDBC API
Tutorial

pg 1024 ( slightly condensed )

The number of milliseconds in a Timestamp object always takes into
account a time zone ...

In order to calculate the milliseconds the driver takes into account
the time zone, information the DBMS may or may not store. If no
Calendar object is supplied the driver will use the default Calendar
whose time zone is that of the JVM that is
running the application. If the DBMS does provide timezone
information the driver will simply use that and IGNORE (my caps) a
Calendar object that may have been passed to it.

>
> The javadoc for the no-Calendar variant is more vague:
>
>
>
> I'm willing to take that as license to map it to timestamp as
> there's no
> mention of timezone at all, no way to supply one, and "SQL TIMESTAMP"
> defaults to WITHOUT TIME ZONE I believe.
>
> If you want to set a TIMESTAMP WITH TIMEZONE using the default
> timezone,
> the JDBC spec seems to want you to call setTimestamp(i, timestamp,
> null)
> (see the first javadoc excerpt above -- "if no calendar object is
> specified"). The current driver also does this for
> setTimestamp(i,times
tamp), which is what I'd like to change.
>
> (and also fix setTimestamp(i,times
tamp,calendar) to actually preserve
> the timezone info you give it rather than mashing it into the default
> timezone, but that's a separate issue..)
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dave Cramer

2005-07-20, 7:24 am

Thinking some more....

It would appear that the setTimestamp is intended to do one or the
other, but not both.

either you have timezone information or you don't. I think this
effectively revokes your license to
interpret the spec as you suggested below.

Further the JDBCCTS doesn't take have any facility for both
scenarios, you can choose the timestamp
type that you want it to pass with (assuming you have a choice).

Dave
On 20-Jul-05, at 5:55 AM, Dave Cramer wrote:

>
> On 19-Jul-05, at 11:00 PM, Oliver Jowett wrote:
>
>
> This was implied...
>
>
> Well to add more confusion here is the excerpt from Sun's JDBC API
> Tutorial
>
> pg 1024 ( slightly condensed )
>
>
> The number of milliseconds in a Timestamp object always takes into
> account a time zone ...
>
> In order to calculate the milliseconds the driver takes into
> account the time zone, information the DBMS may or may not store.
> If no Calendar object is supplied the driver will use the default
> Calendar whose time zone is that of the JVM that is
> running the application. If the DBMS does provide timezone
> information the driver will simply use that and IGNORE (my caps) a
> Calendar object that may have been passed to it.
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )

---------------------------(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

Oliver Jowett

2005-07-20, 7:24 am

Dave Cramer wrote:
> Thinking some more....
>
> It would appear that the setTimestamp is intended to do one or the
> other, but not both.
>
> either you have timezone information or you don't. I think this
> effectively revokes your license to
> interpret the spec as you suggested below.
>
> Further the JDBCCTS doesn't take have any facility for both scenarios,
> you can choose the timestamp
> type that you want it to pass with (assuming you have a choice).

Ok, so this sounds like JDBC doesn't distinguish with-timezone and
without-timezone at all so trying to base the semantics on the spec is
hopeless; we should just pick something that's sensible and do that.

I still think my suggestion of using the two setTimestamp methods to
support the two types makes sense -- but I'm out of time to deal with
this, sorry.

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-07-20, 7:24 am

Dave Cramer wrote:

> In order to calculate the milliseconds the driver takes into account
> the time zone, information the DBMS may or may not store. If no
> Calendar object is supplied the driver will use the default Calendar
> whose time zone is that of the JVM that is
> running the application. If the DBMS does provide timezone information
> the driver will simply use that and IGNORE (my caps) a Calendar object
> that may have been passed to it.

Not having read the source material, but isn't this talking about the
getTimestamp() path not the setTimestamp() path?

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dave Cramer

2005-07-20, 7:24 am

Yes, it does, my mistake ( It was early here )

However reading the notes about setTimestamp:

When the DBMS does not store timezone information the driver will use
cal to construct a JDBC Timestamp value.
If no Calendar object is specified the driver uses the timezone of
the JVM.

Frustratingly it does not say what to do when the DBMS does not store
timezone information. I guess one could infer that
if it doesn't store timezone information, then timezone information
is ignored ?

Which puts us right back to the original problem... Two SQL types,
and only one setTimestamp.

Onel way to deal with this is to find out what the underlying type
is, or to define a different server type, and cast to deal with this
appropriately. IE Oid.javatimestamp and the cast would be able to do
the right thing based on the underlying type.

Dave
On 20-Jul-05, at 8:30 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
>
> Not having read the source material, but isn't this talking about
> the getTimestamp() path not the setTimestamp() path?
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2005-07-20, 7:24 am

Dave Cramer wrote:
> Yes, it does, my mistake ( It was early here )
>
> However reading the notes about setTimestamp:
>
> When the DBMS does not store timezone information the driver will use
> cal to construct a JDBC Timestamp value.
> If no Calendar object is specified the driver uses the timezone of the
> JVM.

Where's this from exactly? It doesn't seem to make sense if it's talking
about setTimestamp -- the driver does not construct a JDBC timestamp in
setTimestamp at all, it's given one by the application.

-O

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

Dave Cramer

2005-07-20, 9:24 am

Same book

JDBC API Tutorial 3rd Edition
pg 676

In fact we do this now..

On 20-Jul-05, at 8:53 AM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
> Where's this from exactly? It doesn't seem to make sense if it's
> talking about setTimestamp -- the driver does not construct a JDBC
> timestamp in setTimestamp at all, it's given one by the application.
>
> -O
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Christian Cryder

2005-07-20, 11:23 am

Holy schmoly, there's a lot of reading to catch up on here. Couple of
comments as I try to take it all in...

On 7/20/05, Oliver Jowett <oliver@opencloud.com> wrote:
> Ok, so this sounds like JDBC doesn't distinguish with-timezone and
> without-timezone at all so trying to base the semantics on the spec is
> hopeless; we should just pick something that's sensible and do that.
>
> I still think my suggestion of using the two setTimestamp methods to
> support the two types makes sense -- but I'm out of time to deal with
> this, sorry.

I like Oliver's suggestion (because it would work for me) even though
I think Dave is probably right when he says
[color=darkred]

One issue which no one has really mentioned is that when we flatten a
timestamp to a string via toString() (eg. I ran into this even when
creating a custom PGTimestamp object yesterday) - when you flatten to
a String, you ARE using a calendar whether you realize it or not (eg.
the Default). Which means that you are performing a DST shift on the
client whether you like it or not. So if the date falls into the right
segment, it may still get munged.

The point here is that not only do we need to consider how to pass the
value to the server w/ TIMESTAMP rather than TIMESTAMPTZ, we also need
to consider how to render it to a string in PGobject.value w/out
munging the date. And the problem here (I think) is that you really
need to know the column info (w/ tz or w/out) in order to know whether
DST applies.

So that's something that needs to be considered. Right now, I can work
around the existing problems using PGTimestamp (per Dave's
suggestion), but I still have to tweak the timezone I am running in to
turn DST off in order to keep the dates from getting munged when
rendered to strings.

One other thing - could the problem be alleviated simply by

a) sending the data across the wire, always using UNKNOWN (thereby
deferring the decision to the DB)

b) sending the data across as millisecs value, rather than flattening
to a Timestamp string? That way to could avoid the toString() issues
mentioned above, plus it'd probably be faster to reconsitute from
millis on the server than by parsing a timestamp string anyways.

Those are just some thoughts to consider...
Christian

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Christian Cryder

2005-07-20, 11:23 am

One other comment - if it was me implementing this thing from scratch,
here's how I'd be inclined to tackle it...

a) I'd send the timestamp value across the wire as millis (the millis
value won't change based on whether DST is turned on/off w/in the
default zone on the client) - so reading/sending as millis would mean
that the client wouldn't end up munging any dates unintentionally
simply on the basis of DST (which is a problem no)

b) I would interpret setTimestamp(i, ts, cal) as meaning "convert from
current millis to millis in cal's timezone", and then send that new
millis value across the wire

c) I'd send the timestamp across the wire as UNKNOWN, and then allow
the server to make the decision based on the column type - since the
server knows what the column is defined as, it can easily decide how
to interpret the millis value when figuring out how to actually
persist it

It seems to me this would handle all the problems we are having, while
working within the API limitations. It doesn't seem like it would
break any existing code (at least not anything that's not already
broken).

Of course, I have no idea whether any of this is feasible or not. But
I think the root of our problems is tied to flattening timestamp
objects via toString(), which then applies the calendar rendering,
which then applies DST rules of the default timezone (again, whether
you want them to or not).

One final consideration - has anyone evaluated the performance
implications of rendering/reconstituting timestamps to Strings vs.
millis? It seems to me like there must be a lot more overhead
associated with the String versions (looking up calendars, applying
dst logic, etc) than with millis.

Christian

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris Jurka

2005-07-20, 11:23 am

On Wed, 20 Jul 2005, Oliver Jowett wrote:

> Kris Jurka wrote:
>
>
> Ew! How much existing code is that going to break?
>

Very little. Currently everything works if the client and server are in
the same timezone which is 99% of the time. This just ensures that the
client and server agree on what timezone to use for the remaining 1% which
are already broken. Problems arise when the server and JDK don't have the
same set of timezones, which will happen, but not often. We could easily
add a URL parameter to bail people out of here though.

http://archives.postgresql.org/pgsq...12/msg00139.php

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Christian Cryder

2005-07-20, 1:23 pm

On 7/20/05, Kris Jurka <books@ejurka.com> wrote:
> Currently everything works if the client and server are in
> the same timezone which is 99% of the time.

Just to clarify - this is not technically correct. If you have a
zoneless timestamp in the db, and your client and server are running
in daylight savings time, and that date happens to fall in DST "no
man's land" (eg. between 1 and 2 AM on fist Sunday in April), that
value WILL get munged when you read it, and it stays munged when you
write it back - regardless of how the column is declared in the db.

This is a function of the timestamp getting flattened to a String
(which in turn uses a Calendar, which in turn applies DST to display a
"valid" time, which in turn munges the data).

So there is a scenario where the dates will still get munged even
though client and server are in the same timezone. And that's a
problem.

Christian

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2005-07-20, 1:23 pm

Kris Jurka <books@ejurka.com> writes:
> ... Problems arise when the server and JDK don't have the
> same set of timezones, which will happen, but not often. We could easily
> add a URL parameter to bail people out of here though.

Hmm ... does Java have a standard set of timezone names? If so, does it
bear any resemblance to the zic database names?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Christian Cryder

2005-07-20, 1:23 pm

On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hmm ... does Java have a standard set of timezone names? If so, does it
> bear any resemblance to the zic database names?

Yes, you can see them by running this snippet...

String zones[] = TimeZone.getAvailableIDs();
for (int i=0; i<zones.length; i++) {
System.out.println(" "+i+": "+TimeZone.getTimeZone(zones[i]));
}

No idea how that compares to zic...

Christian

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer

2005-07-20, 1:23 pm

Yes, it does, and yes they should be.

The real solution appears to be to use the current server timezone,
not the
other way around. See my other posts.

Dave
On 20-Jul-05, at 1:41 PM, Tom Lane wrote:

> Kris Jurka <books@ejurka.com> writes:
>
>
> Hmm ... does Java have a standard set of timezone names? If so,
> does it
> bear any resemblance to the zic database names?
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Cramer

2005-07-20, 1:23 pm

Actually,

Java has timezones that postgres doesn't understand, which is
why using the server timezone makes more sense.

Hopefully java understands all the timezones postgres understands.

Dave
On 20-Jul-05, at 1:47 PM, Christian Cryder wrote:

> On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
> Yes, you can see them by running this snippet...
>
> String zones[] = TimeZone.getAvailableIDs();
> for (int i=0; i<zones.length; i++) {
> System.out.println(" "+i+": "+TimeZone.getTimeZone(zones[i]));
> }
>
> No idea how that compares to zic...
>
> Christian
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Vadim Nasardinov

2005-07-20, 1:23 pm

On Wednesday 20 July 2005 11:40, Christian Cryder wrote:
> b) sending the data across as millisecs value, rather than
> flattening to a Timestamp string? That way to could avoid the
> toString() issues mentioned above, plus it'd probably be faster to
> reconsitute from millis on the server than by parsing a timestamp
> string anyways.

I learned all sorts of fascinating things by reading this thread:
http://forum.java.sun.com/thread.jspa?messageID=2813701

For example:

| $ export TZ=Europe/London
| $ date +%s -d '19981231 23:59:00'
| 915148740
| $ date +%s -d '19990101 00:00:00'
| 915148800
|
| We can see here that the duration of the last minute in 1998 was 60
| seconds.
|
| The more or less experimental time zones prefixed by "right/"
| accounts for leap seconds:
|
| $ export TZ=right/Europe/London
| $ date +%s -d '19981231 23:59:00'
| 915148761
| $ date +%s -d '19990101 00:00:00'
| 915148822
|
| We see here that the same last minute was 61 seconds long.
|
| The implementation of Java by Sun does not retrieve the leap
| seconds info from the C library.
|
| As leap second support seems not to have been implemented yet,
| every minute in Sun's Java seems to be 60 seconds long.
|
| But there is no guarantee it won't change in the future, as
| permitted in the documentation, and other implementations of Java
| can be different, too.

Based on this, I get the vague impression that Java's interpretation
of milliseconds since epoch may differ from PostgreSQL's interpretation
of the same.

---------------------------(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

John R Pierce

2005-07-20, 8:24 pm

Dave Cramer wrote:
> Actually,
>
> Java has timezones that postgres doesn't understand, which is
> why using the server timezone makes more sense.
>
> Hopefully java understands all the timezones postgres understands.

as we discovered the hard way, named timezones are a BAD IDEA. We had some
stuff in java + jdbc + postgres that used a timezone, when it was brought up in
Singapore, their local timezone memnonic wasn't recognized by postgres, and
when it was brought up in China, CST was misinterpretted as Central Standard
Time rather than China Standard Time (about 12 hours off)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris Jurka

2005-07-20, 8:24 pm

On Wed, 20 Jul 2005, John R Pierce wrote:

> as we discovered the hard way, named timezones are a BAD IDEA. We had some
> stuff in java + jdbc + postgres that used a timezone, when it was brought up in
> Singapore, their local timezone memnonic wasn't recognized by postgres, and
> when it was brought up in China, CST was misinterpretted as Central Standard
> Time rather than China Standard Time (about 12 hours off)
>

No, this indicates that *abbreviated* timezones are a bad idea, not named
timezones.

Kris Jurka

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

Dave Cramer

2005-07-20, 8:24 pm

I think using the same time zone as the server is the only way to go:

Kris has proposed a patch which would set the servers time zone to
the JVM when the connection is started

This can still be broken if someone were to change the default
timezone after the connection was initiated.

If we do the reverse and save the servers timezone for the purposes
of creating the timestamp object we don't run into this problem, even
if someone were to issue a set timezone='newtimezon
e' we will still
get the notice and can update the
stored server timezone for the connection.

Dave
On 20-Jul-05, at 3:50 PM, Kris Jurka wrote:

>
>
> On Wed, 20 Jul 2005, John R Pierce wrote:
>
>
>
> No, this indicates that *abbreviated* timezones are a bad idea, not
> named
> timezones.
>
> Kris Jurka
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Christian Cryder

2005-07-20, 8:24 pm

On 7/20/05, Dave Cramer <pg@fastcrypt.com> wrote:
> I think using the same time zone as the server is the only way to go:

How does this prevent the DST munging issue, where a zoneless time
(eg. 04-03-2005 1:22 AM) is read from the DB and then re-written? What
is to keep it being from being rewritten as 2:22 AM?

> Kris has proposed a patch which would set the servers time zone to
> the JVM when the connection is started

What happens when someone writes a client server app, and one client
connects from timezone A, and another client connects from timezone B,
and the server itself is running in timezone C?

Christian

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

Dave Cramer

2005-07-20, 8:24 pm

On 20-Jul-05, at 4:44 PM, Christian Cryder wrote:

> On 7/20/05, Dave Cramer <pg@fastcrypt.com> wrote:
>
>
> How does this prevent the DST munging issue, where a zoneless time
> (eg. 04-03-2005 1:22 AM) is read from the DB and then re-written? What
> is to keep it being from being rewritten as 2:22 AM?
We still have the crossover to deal with
>
>
>
> What happens when someone writes a client server app, and one client
> connects from timezone A, and another client connects from timezone B,
> and the server itself is running in timezone C?
>
Setting the servers time zone only sets the time zone for that client.
> Christian
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>

---------------------------(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

Oliver Jowett

2005-07-20, 8:24 pm

Kris Jurka wrote:
>
> On Wed, 20 Jul 2005, Oliver Jowett wrote:
>
>
>
> Very little.

Aside from the issues that others have raised, I was worried about
existing code that does *not* use PreparedStatement. (probably with good
reason given this discussion!)

I really don't think the driver should be touching TimeZone :/

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-07-20, 8:24 pm

Oliver Jowett <oliver@opencloud.com> writes:
> I really don't think the driver should be touching TimeZone :/

The idea of tracking the server timezone and using it locally for
conversions of incoming data would avoid that gripe.

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.

I think any solution that tries to work 100% for both flavors of SQL
timestamp is simply doomed to failure --- unless there are more
semantics to Java's Timestamp type than I've gathered from this
discussion. The impression I have is that Timestamp is supposed to
represent absolute time instants (ie, there's no additional "what time
zone is this in" info needed to determine the exact equivalent GMT
time), which would make it equivalent to timestamptz.
timestamp-without-zone is a fundamentally different critter, because it
does not assume that there is any such thing as absolute GMT-equivalent
time.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kris Jurka

2005-07-20, 8:24 pm

On Thu, 21 Jul 2005, Oliver Jowett wrote:

> Aside from the issues that others have raised, I was worried about
> existing code that does *not* use PreparedStatement. (probably with good
> reason given this discussion!)
>
> I really don't think the driver should be touching TimeZone :/
>

You might appreciate this archived message...

http://archives.postgresql.org/pgsq...12/msg00169.php

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Oliver Jowett

2005-07-20, 8:24 pm

Kris Jurka wrote:
>
> On Thu, 21 Jul 2005, Oliver Jowett wrote:
>
>
> You might appreciate this archived message...
>
> http://archives.postgresql.org/pgsq...12/msg00169.php

Heh, good point :-)

Of course there are no broken applications out there! ;-)

I still think touching TimeZone is asking for trouble -- we've got no
guarantees that the Java and server timezone databases are 100% in sync
(even for those timezones in common)

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-07-20, 8:24 pm

Tom Lane wrote:

> I think any solution that tries to work 100% for both flavors of SQL
> timestamp is simply doomed to failure --- unless there are more
> semantics to Java's Timestamp type than I've gathered from this
> discussion. The impression I have is that Timestamp is supposed to
> represent absolute time instants (ie, there's no additional "what time
> zone is this in" info needed to determine the exact equivalent GMT
> time), which would make it equivalent to timestamptz.

That's correct. A Timestamp is essentially a wrapper around
milliseconds-since-epoch. It has no timezone information beyond some
badly thought out, not settable, "timezone offset" that always reflects
the default timezone, and some getHour/getMinute/etc accessors (again
using the JVM's default timezone only); these have been deprecated since
JDK 1.1 in favor of using Calendar objects.

Timestamps are then interpreted by feeding them through a Calendar for
locale- and timezone-specific formatting -- e.g. you can ask a Calendar
for the hour of a particular Timestamp, and it'll give you the
appropriate hour based on the timezone the Calendar is for.

> timestamp-without-zone is a fundamentally different critter, because it
> does not assume that there is any such thing as absolute GMT-equivalent
> time.

In other words, a timestamp-without-zone is just a
year-month-day-hour-minute-second tuple, which could correspond to many
instants in time depending on timezone and daylight savings changes?

The thing is that there are two distinct variants of setTimestamp():

(1) setTimestamp(index, timestamp)
(2) setTimestamp(index, timestamp, calendar)

(2) obviously maps to timestamp-with-zone. The question is what do we do
with (1) -- is the intention to set:

(a) a particular instant in time identified by 'timestamp', or
(b) the particular year-month-day-hour-minute-second described by
('timestamp' interpreted in the default timezone of the JVM)

1(a) is timestamp-with-zone again
1(b) is timestamp-without-zone

The JDBC spec doesn't help us here.

If we go with 1(a) then we have problems when casting to a
timestamp-without-zone value when the JVM and server timezones do not match.

If we go with 1(b) then we have problems when casting to a
timestamp-with-zone value when the JVM and server timezones do not
match, or when daylight savings means there are two possible instants in
a particular timezone identified by the timestamp-without-zone.

....

I'd like to go with 1(b):

I don't like 1(a) because it gives you no way