Обсуждение: Timestamp Conversion Woes Redux

Hi Dave (& others),

You said,
> The problem isn't with PreparedStatement, rather with Timestamp itself.
> I ran the following
>     Timestamp ts = new Timestamp(1112511962000L);
>     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(1112511962000L)));     //2005-04-03 00:06:02
    System.out.println("starting t2: "+sdf.format(new
Timestamp(1112520583000L)));     //2005-04-03 02:29:43
    System.out.println("starting t3: "+sdf.format(new
Timestamp(1112522529000L)));     //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(1112511962000L);
        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(1112520583000L);
        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(1112522529000L);
        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) {}
    }


:

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,
>
>> The problem isn't with PreparedStatement, rather with Timestamp
>> itself.
>> I ran the following
>>     Timestamp ts = new Timestamp(1112511962000L);
>>     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(1112511962000L)));     //2005-04-03 00:06:02
>     System.out.println("starting t2: "+sdf.format(new
> Timestamp(1112520583000L)));     //2005-04-03 02:29:43
>     System.out.println("starting t3: "+sdf.format(new
> Timestamp(1112522529000L)));     //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(1112511962000L);
>         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(1112520583000L);
>         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(1112522529000L);
>         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
>
>

Christian Cryder <c.s.cryder@gmail.com> writes:
>> The problem isn't with PreparedStatement, rather with Timestamp itself.

> 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

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:
>
>>> The problem isn't with PreparedStatement, rather with Timestamp
>>> itself.
>>>
>
>
>> 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
>
>

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

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

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

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

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

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

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

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:
>
>
>> 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 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
javatype. 

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

Oliver Jowett <oliver@opencloud.com> writes:
> 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.

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

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

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

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

[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.

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:
>
>> 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.
>>
>
> 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
>
>

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

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

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

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:
>
> > [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
> >
> >
>

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

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
>

Csaba Nagy wrote:
> [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),

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

-O

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

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

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

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.

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,Timestamp) -> timestamp
  setTimestamp(int,Timestamp,Calendar) -> timestamptz

-O

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:
>
>
>> 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,Timestamp) -> timestamp
>   setTimestamp(int,Timestamp,Calendar) -> timestamptz
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

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

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

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

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.


>
>
>> Not to mention the fact that it changes the
>> current 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
>
>

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:
>
>
>> 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.
>>
>
> 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.
>
>
>
>>
>>
>>
>>> Not to mention the fact that it changes the
>>> current 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 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:

>> Sets the designated parameter to the given java.sql.Timestamp value,
>> using the given Calendar object. The driver uses the Calendar object to
>> construct an SQL TIMESTAMP value, which the driver then sends to the
>> database. With a Calendar object, the driver can calculate the timestamp
>> taking into account a custom timezone. If no Calendar object is
>> specified, the driver uses the default timezone, which is that of the
>> virtual machine running the application.

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

The javadoc for the no-Calendar variant is more vague:

>> Sets the designated parameter to the given java.sql.Timestamp value. The
>> driver converts this to an SQL TIMESTAMP value when it sends it to the
>> database.

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,timestamp), which is what I'd like to change.

(and also fix setTimestamp(i,timestamp,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

On Tue, 19 Jul 2005, Christian Cryder wrote:

> 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).

No, you just need to turn it off on that particular connection.  This was
my original suggestion on how to handle this issue.  The attached patch
and built jar files, pass your example test just fine.  I've also put up
your test as a standalone compilable file here.

http://www.ejurka.com/pgsql/jars/cc/

I think this approach is better than trying to infer something from the
particular arguments to setTimestamp because it should just work unless
you are trying to do something tricky.  Unless you are working with a
whole bunch of different timezones on the same connection there is no
need to do anything else.  I mean, if the driver developers can't
figure out the semantics of setTimestamp how can we expect end users to?

Kris Jurka

Kris Jurka wrote:

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

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

-O

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

> 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.
This was implied...
>
>
>> There is no indication by the spec
>> that this behaviour is the "right" behaviour.
>>
>
> The javadoc for the with-Calendar variant is quite clear:
>
>
>>> Sets the designated parameter to the given java.sql.Timestamp value,
>>> using the given Calendar object. The driver uses the Calendar
>>> object to
>>> construct an SQL TIMESTAMP value, which the driver then sends to the
>>> database. With a Calendar object, the driver can calculate the
>>> timestamp
>>> taking into account a custom timezone. If no Calendar object is
>>> specified, the driver uses the default timezone, which is that of
>>> the
>>> virtual machine running the application.
>>>
>
> 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:
>
>
>>> Sets the designated parameter to the given java.sql.Timestamp
>>> value. The
>>> driver converts this to an SQL TIMESTAMP value when it sends it
>>> to the
>>> database.
>>>
>
> 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,timestamp), which is what I'd like to change.
>
> (and also fix setTimestamp(i,timestamp,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
>
>

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:
>
>
>> 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.
>>
> This was implied...
>
>>
>>
>>
>>> There is no indication by the spec
>>> that this behaviour is the "right" behaviour.
>>>
>>>
>>
>> The javadoc for the with-Calendar variant is quite clear:
>>
>>
>>
>>>> Sets the designated parameter to the given java.sql.Timestamp
>>>> value,
>>>> using the given Calendar object. The driver uses the Calendar
>>>> object to
>>>> construct an SQL TIMESTAMP value, which the driver then sends to
>>>> the
>>>> database. With a Calendar object, the driver can calculate the
>>>> timestamp
>>>> taking into account a custom timezone. If no Calendar object is
>>>> specified, the driver uses the default timezone, which is that
>>>> of the
>>>> virtual machine running the application.
>>>>
>>>>
>>
>> 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:
>>
>>
>>
>>>> Sets the designated parameter to the given java.sql.Timestamp
>>>> value. The
>>>> driver converts this to an SQL TIMESTAMP value when it sends it
>>>> to the
>>>> database.
>>>>
>>>>
>>
>> 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,timestamp), which is what I'd like to change.
>>
>> (and also fix setTimestamp(i,timestamp,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
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )

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

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

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

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

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

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

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

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

On Wed, 20 Jul 2005, Oliver Jowett wrote:

> Kris Jurka wrote:
>
> > !                                     { "TimeZone", java.util.TimeZone.getDefault().getID()}
>
> 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/pgsql-jdbc/2004-12/msg00139.php

Kris Jurka

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

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

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

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:
>
>> ... 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
>
>

On Wednesday 20 July 2005 13:41, Tom Lane wrote:
> Hmm ... does Java have a standard set of timezone names?  If so,
> does it bear any resemblance to the zic database names?

I'm not sure how Roedy Green knows this, but this is what he has to
say on the subject in his Java Glossary:

   http://www.mindprod.com/jgloss/timezone.html

   The names for timezones used in Java comes from a list maintained
   at NIH by Arthur David Olson. For reasons only he understands,
   Pacific Standard Time is called America/Los_Angeles.

As far as I can tell, Olson's timezone data can be found here:
  ftp://elsie.nci.nih.gov/pub/

Sun's JDK puts timezone data in $JAVA_HOME/jre/lib/zi/

Sun's JDK's timezone info seems fairly different from what, say,
Fedora Core distributes in its tzdata RPM:

 | $ find /usr/share/zoneinfo/ -type f -printf '%P\n' | sort > /tmp/linux-tzdata.txt
 | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi -type f -printf '%P\n' | sort > /tmp/java-tzdata.txt
 | $ diff /tmp/linux-tzdata.txt /tmp/java-tzdata.txt  | wc -l
 | 1309

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

Vadim Nasardinov <vadimn@redhat.com> writes:
> I'm not sure how Roedy Green knows this, but this is what he has to
> say on the subject in his Java Glossary:

>    http://www.mindprod.com/jgloss/timezone.html

>    The names for timezones used in Java comes from a list maintained
>    at NIH by Arthur David Olson. For reasons only he understands,
>    Pacific Standard Time is called America/Los_Angeles.

> As far as I can tell, Olson's timezone data can be found here:
>   ftp://elsie.nci.nih.gov/pub/

Hmm ... that is the zic distribution, so if this information is accurate
there should be a pretty exact match in the sets of names ... which it
sounds like there is not.

> Sun's JDK's timezone info seems fairly different from what, say,
> Fedora Core distributes in its tzdata RPM:

Fedora's info also comes from zic.

            regards, tom lane

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.

On 20-Jul-05, at 2:29 PM, Tom Lane wrote:

> Vadim Nasardinov <vadimn@redhat.com> writes:
>
>> I'm not sure how Roedy Green knows this, but this is what he has to
>> say on the subject in his Java Glossary:
>>
>
>
>>    http://www.mindprod.com/jgloss/timezone.html
>>
>
>
>>    The names for timezones used in Java comes from a list maintained
>>    at NIH by Arthur David Olson. For reasons only he understands,
>>    Pacific Standard Time is called America/Los_Angeles.
>>
>
>
>> As far as I can tell, Olson's timezone data can be found here:
>>   ftp://elsie.nci.nih.gov/pub/
>>
>
> Hmm ... that is the zic distribution, so if this information is
> accurate
> there should be a pretty exact match in the sets of names ... which it
> sounds like there is not.

On my Mac my java timezone was set to Canada/Montreal which was
broken (did not use DST ) and the server certainly
doesn't understand it.

The server on the same machine had the timezone set to Canada/Eastern
which java understands and I hacked the driver code to use the server
timezone. This seems to work. There's still a fair bit of ambiguity
as to whether java will do the right thing with the server supplied
time zones though?

It appears that timestamp, date, calendar in java are all terribly
implemented. I'm starting to see more and more why other DBMS
implemented their own setTimestamptz.

>
>
>> Sun's JDK's timezone info seems fairly different from what, say,
>> Fedora Core distributes in its tzdata RPM:
>>
>
> Fedora's info also comes from zic.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

On Wednesday 20 July 2005 14:57, Dave Cramer wrote:
> On my Mac my java timezone was set to Canada/Montreal which was
> broken (did not use DST ) and the server certainly doesn't
> understand it.

Out of curiosity, do you remember which JDK had this timezone?
Sun's JDK on Linux doesn't have it:

 | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi/ -name Montreal
 | /usr/local/j2sdk1.4.2_08/jre/lib/zi/America/Montreal

It only has America/Montreal, which PostgreSQL should grok just fine,
AFAICT:

 | $ find /usr/share/zoneinfo/ -name Montreal
 | /usr/share/zoneinfo/right/America/Montreal
 | /usr/share/zoneinfo/posix/America/Montreal
 | /usr/share/zoneinfo/America/Montreal

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)

On Wednesday 20 July 2005 14:16, Vadim Nasardinov wrote:
> Sun's JDK's timezone info seems fairly different from what, say,
> Fedora Core distributes in its tzdata RPM:

More precisely, Sun's timezone info seems to be a strict subset of
Fedora's timezone info.  (This is for Sun's JDK 1.4.2_08 and Fedora
Core 3.  I didn't bother to check JDK 1.5.0 -- it does add three
additional timezones over what was present in 1.4.2: America/Bahia,
America/Campo_Grande, and America/Toronto.)

I added a couple of "grep -v" pipes to filter out irrelevant
differences:

 | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi -type f -printf '%P\n' | \
 |   grep -v ZoneInfoMappings | sort > /tmp/java-tzdata.txt
 | $ find /usr/share/zoneinfo/ -type f -printf '%P\n' | \
 |   grep -v right/ | grep -vE '(right/|posix/|zone\.tab)' | \
 |   sort > /tmp/linux-tzdata.txt

This shows that the FC3 tzdata is a strict superset of the JDK 1.4.2
timezone info:

 | $ diff /tmp/java-tzdata.txt /tmp/linux-tzdata.txt  | grep -c '>'
 | 152
 | $ diff /tmp/java-tzdata.txt /tmp/linux-tzdata.txt  | grep -c '<'
 | 0

Whether or not this means that PostgreSQL on FC should be able to
handle any time zone that Java throws at it, I am not sure.

Vadim,

Actually you are correct, it is America/Montreal and now that I
switched it back it actually has useDaylight=true... very strange.

I think as John pointed out though there are some issues with using
named TimeZones.

Is there a way to get the servers timezone info from the server ?

Dave
On 20-Jul-05, at 3:10 PM, Vadim Nasardinov wrote:

> On Wednesday 20 July 2005 14:57, Dave Cramer wrote:
>
>> On my Mac my java timezone was set to Canada/Montreal which was
>> broken (did not use DST ) and the server certainly doesn't
>> understand it.
>>
>
> Out of curiosity, do you remember which JDK had this timezone?
> Sun's JDK on Linux doesn't have it:
>
>  | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi/ -name Montreal
>  | /usr/local/j2sdk1.4.2_08/jre/lib/zi/America/Montreal
>
> It only has America/Montreal, which PostgreSQL should grok just fine,
> AFAICT:
>
>  | $ find /usr/share/zoneinfo/ -name Montreal
>  | /usr/share/zoneinfo/right/America/Montreal
>  | /usr/share/zoneinfo/posix/America/Montreal
>  | /usr/share/zoneinfo/America/Montreal
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

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

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='newtimezone' 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:
>
>
>> 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
>
>

On Wednesday 20 July 2005 15:42, Dave Cramer wrote:
> I think as John pointed out though there are some issues with using
> named TimeZones.

Kris rebutted that comment.

> Is there a way to get the servers timezone info from the server ?

Good question.

Based upon a very cursory reading of the documentation, it seems to be
that PostgreSQL uses a hardcoded internal list of time zones for
dealing with timezone *input*, but it uses the underlying OS's
facilities for dealing with timezone *output*.  But then again, the
docs seems to have changed between 7.4 and 8.0.  For example, in 7.4:

  http://www.postgresql.org/docs/7.4/static/datatype-datetime.html

  PostgreSQL uses your operating system's underlying features to
  provide output time-zone support,

However, this sentence seems missing from 8.0:

  http://www.postgresql.org/docs/8.0/static/datatype-datetime.html

The docs for 7.4 provide a list of timezone abbreviations that
PostgreSQL recognizes:

  http://www.postgresql.org/docs/7.4/static/datetime-keywords.html

The 8.0 docs, too, provide the list of abbreviations but also a list
of unabbreviated timezone names:

  http://www.postgresql.org/docs/8.0/static/datetime-keywords.html

These seem to be hardcoded.

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

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

> 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?
We still have the crossover to deal with
>
>
>> 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?
>
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
>
>

Vadim Nasardinov <vadimn@redhat.com> writes:
> Based upon a very cursory reading of the documentation, it seems to be
> that PostgreSQL uses a hardcoded internal list of time zones for
> dealing with timezone *input*, but it uses the underlying OS's
> facilities for dealing with timezone *output*.  But then again, the
> docs seems to have changed between 7.4 and 8.0.

That's because the underlying implementation was completely revamped.
We now have our own timezone code and timezone database (the latter
being the zic database) to avoid depending on the OS facilities.

            regards, tom lane

On Wednesday 20 July 2005 18:33, Tom Lane wrote:
> That's because the underlying implementation was completely
> revamped.  We now have our own timezone code and timezone database
> (the latter being the zic database) to avoid depending on the OS
> facilities.

If 8.0 comes with its own copy of a recent zic database, then, in
theory, it should be able to handle any timezone known to Java.  (The
opposite is not necessarily true, as Java's timezone info seems to be
missing a lot of recent changes.)  Does that sound like a correct
statement to you or did I forget some important qualifiers?

Vadim Nasardinov <vadimn@redhat.com> writes:
> If 8.0 comes with its own copy of a recent zic database, then, in
> theory, it should be able to handle any timezone known to Java.  (The
> opposite is not necessarily true, as Java's timezone info seems to be
> missing a lot of recent changes.)  Does that sound like a correct
> statement to you or did I forget some important qualifiers?

Yeah, it seems that we are just open to version skew issues between
different copies of the zic database ... which changes enough that
that's a nontrivial risk, but I fear we are unlikely to do better.

In any case, it seems that the arguments about which side is likely
to know more timezone names than the other side are moot; we should
not let that consideration drive the design choice.

            regards, tom lane

Kris Jurka wrote:
>
> On Wed, 20 Jul 2005, Oliver Jowett wrote:
>
>
>>Kris Jurka wrote:
>>
>>
>>>!                                     { "TimeZone", java.util.TimeZone.getDefault().getID()}
>>
>>Ew! How much existing code is that going to break?
>
> 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

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

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/pgsql-jdbc/2004-12/msg00169.php

Kris Jurka

Kris Jurka wrote:
>
> 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/pgsql-jdbc/2004-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

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 of getting a
timestamp-without-zone into the database without having a timezone
available on the Java side that exactly matches the server timezone. As
found elsewhere in the thread, the two zic databases are already out of
sync, so I don't like the chances of that..

With 1(b), if you hit the casting problem, you have a workaround: use
the setTimestamp() method that takes a Calendar, and explicitly pass the
default JVM calendar. That completely avoids the need for the
problematic cast.

-O

On 20-Jul-05, at 8:16 PM, Oliver Jowett wrote:

> Kris Jurka wrote:
>
>>
>> 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/pgsql-jdbc/2004-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)

If we track the server's timezone in the driver, then if we get a
timezone that we don't understand
we can just use the JVM's default time zone.
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

Oliver Jowett <oliver@opencloud.com> writes:
> That's correct. A Timestamp is essentially a wrapper around
> milliseconds-since-epoch.

OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
(at the moment anyway --- there's some agitation for putting an
explicit zone identifier in too, because it looks like the SQL spec
requires that).

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

Right, this corresponds to the server's idea of rotating the timestamptz
value to the client's TimeZone for display.

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

Exactly.  This I believe is per SQL spec.

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

Hm, that's not obvious to me at all.  If the timestamp is supposed to be
unconditional absolute time, then the only sensible interpretation of (1)
is that you're setting a timestamptz, and (2) would presumably produce
a timestamp-without-tz value corresponding to the local time readout of
the Calendar.

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

No matter what we do, we will have issues if the user uses the wrong
variant of setTimestamp for a particular parameter --- the server will
add its own rotation by the TimeZone offset while converting timestamp
to timestamptz or vice versa.

You could possibly avoid that gotcha by setting the server's TimeZone
to UTC, but I got the impression you wanted to avoid doing that.

            regards, tom lane

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>That's correct. A Timestamp is essentially a wrapper around
>>milliseconds-since-epoch.
>
> OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
> (at the moment anyway --- there's some agitation for putting an
> explicit zone identifier in too, because it looks like the SQL spec
> requires that).

Ah ok, this is where some of my confusion is coming from -- I assumed
that WITH TIME ZONE actually stored the timezone you provided and would
preserve it on output. Guess I should test these things before wading in!

>>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.
>
> Hm, that's not obvious to me at all.  If the timestamp is supposed to be
> unconditional absolute time, then the only sensible interpretation of (1)
> is that you're setting a timestamptz, and (2) would presumably produce
> a timestamp-without-tz value corresponding to the local time readout of
> the Calendar.

I was coming at it from the representations of the values:

  '2005-01-01 15:00:00' is a timestamp representation
  '2005-01-01 15:00:00+1300' is a timestamptz representation

We don't have a timezone offset to send in case (1) so it "must" be a
timestamp. But I can see where your interpretation comes from though --
if the Timestamp is defined to be milliseconds-since-epoch we can
identify the instant without needing a separate timezone offset.

Reviewing it all I'm leaning towards JDBC's idea of TIMESTAMP being
timestamp-without-timezone; the driver is meant to convert the
absolute-instant of the Timestamp to a particular date/time using a
specified (or default) timezone. That interpretation would make some of
the setTimestamp() javadoc more comprehensible.

This would also explain why Oracle apparently has a separate extension
setTimestamptz() method.

Think I'm going to give up on this now though..

-O

Dave Cramer wrote:

> If we track the server's timezone in the driver, then if we get a
> timezone that we don't understand
> we can just use the JVM's default time zone.

Uh, how does this do anything but mangle your dates?

-O

This is more or less the same as Kris's suggestion of setting the
server's time zone to the JVM's time zone, except
we can track time zone changes using the NOTICE messages.

So if the user were to create a connection and then subsequently
change the JVM's default time zone. Which isn't that
far fetched if the user is using a connection pool. The driver would
remain in sync with the server's time zone.

On 20-Jul-05, at 9:09 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>
>> If we track the server's timezone in the driver, then if we get a
>> timezone that we don't understand
>> we can just use the JVM's default time zone.
>>
This is just a safe fallback position that's no worse than what we
have now

--dc--
>
> Uh, how does this do anything but mangle your dates?
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

On 20-Jul-05, at 9:09 PM, Oliver Jowett wrote:

> Tom Lane wrote:
>
>> Oliver Jowett <oliver@opencloud.com> writes:
>>
>>
>>> That's correct. A Timestamp is essentially a wrapper around
>>> milliseconds-since-epoch.
>>>
>>
>> OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
>> (at the moment anyway --- there's some agitation for putting an
>> explicit zone identifier in too, because it looks like the SQL spec
>> requires that).
Reading the JDBC Tutorial suggests that this information would be
used when
retrieving the timestamp.
>>
>
> Ah ok, this is where some of my confusion is coming from -- I assumed
> that WITH TIME ZONE actually stored the timezone you provided and
> would
> preserve it on output. Guess I should test these things before
> wading in!
>
>
>>> 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.
>>>
>>
>> Hm, that's not obvious to me at all.  If the timestamp is supposed
>> to be
>> unconditional absolute time, then the only sensible interpretation
>> of (1)
>> is that you're setting a timestamptz, and (2) would presumably
>> produce
>> a timestamp-without-tz value corresponding to the local time
>> readout of
>> the Calendar.
>>
>
> I was coming at it from the representations of the values:
>
>   '2005-01-01 15:00:00' is a timestamp representation
>   '2005-01-01 15:00:00+1300' is a timestamptz representation
>
> We don't have a timezone offset to send in case (1) so it "must" be a
> timestamp. But I can see where your interpretation comes from
> though --
> if the Timestamp is defined to be milliseconds-since-epoch we can
> identify the instant without needing a separate timezone offset.
>
> Reviewing it all I'm leaning towards JDBC's idea of TIMESTAMP being
> timestamp-without-timezone; the driver is meant to convert the
> absolute-instant of the Timestamp to a particular date/time using a
> specified (or default) timezone. That interpretation would make
> some of
> the setTimestamp() javadoc more comprehensible.
Well, there is documentation that suggests both are supported;
however apparently
not simultaneously.
>
> This would also explain why Oracle apparently has a separate extension
> setTimestamptz() method.
>
> Think I'm going to give up on this now though..
>
> -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
>
>

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

Hi guys,

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

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

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

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

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

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

Hope that helps,
Christian

Hi Christian,

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

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

-Kevin


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

Hi guys,

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

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

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

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

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

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

Hope that helps,
Christian

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

The only instance variable in the java.sql.Timestamp class is a Java
long primitive, which is the offset of the moment from 1970-01-01
00:00:00.0 GMT.  There is absolutely no other information carried in an
instance of this class.  The meaining of a Timestamp object is clear and
unambiguous.  By default, rendering a Timestamp to a String is done
using the default time zone for the JVM.  This code snippet:

    Timestamp ts = new Timestamp(0L);
    System.out.println(ts);

renders this on my console:

1969-12-31 18:00:00.0

because I'm in the U.S. Central time zone.  I could use a specific
Timezone object to render this as a string appropriate to any time zone
of my choice.  The questions would seem to be:

(1)  When storing a Timestamp object to a database column which is
defined as a timestamp with a time zone, what time zone should be used?
Regardless of the choice, the value in the column must represent the
same moment as the original Timestamp object.  It seems clear that some
methods allow you to specify a Calendar object for the sole purpose of
specifying the time zone, and that in the absence of that, the default
time zone of the JVM should be used.

(2)  When storing a Timestamp object to a database column which is
defined as a timestamp without a time zone, which time zone's local
representation of the Timestamp object should be used to convert the
Timestamp object from a moment to whatever the heck a timestamp without
a time zone is?  The rub is that the semantics of such a database column
are not obvious.  Does it represent the moment corresponding to the
given year, month, day, hour, minute, second, etc. in the server's time
zone, the client's time zone, GMT, or something else?  There are use
cases where each of these make sense, although the primary reason for
HAVING a timestamp without a timezone, would seem to be so that the
actual moment would be different for different clients.  (For example, a
database used in many time zones, which contained columns to configure
when late-night maintenance activities on client machines should occur.)
 It seems to me that specifying a time zone on database writes in this
case should cause the database representation of the moment represented
by the Timestamp object to be the date and time of that moment in the
specified time zone.

I can't think of anything in the ANSI SQL or JDBC specifications or
javadocs which provides any direction on what a timestamp without a time
zone represents; however, there are two compelling reasons to use the
time zone of the client:

(a)  The only reasonable use cases I can see for storing a timestamp
which does NOT represent a moment in time involve interpreting it as
different moments based on the client time zone.  This means that
software the is right and proper would want to use the local timezone,
at least for retrieval, and we all like symmetry, don't we?

(b)  The most common reason for using a timestamp without a timezone is
certainly that people are assuming that the whole system is based around
a single timezone which they never explicitly specify, and they don't
want to bother with it.  In this case, the client and server time zones
should match (or it was a very bad choice for data type).  If the time
zones DON'T match, we're dealing with a fundamentally screwed up
situation at that point -- the schema is fundamentally inappropriate for
the runtime environment.  It seems to me that allowing the client side
to specify a time zone when presenting the Timestamp to the database is
the reasonable way to go, with the default being the default time zone
for the JVM.

The solution to Christian's problem seems to me to be that he should
either get the timestamps from his source as String objects (using a
database server side function to convert them to the desired
character-based representation), and generate the values as literals in
loading the new database,

OR

he should ensure that his client and sever use the same time zone and
that the Timestamp objects actually represent the moments he cares
about.

Either should work.

-Kevin

On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:

> The only instance variable in the java.sql.Timestamp class is a Java
> long primitive, which is the offset of the moment from 1970-01-01
> 00:00:00.0 GMT.  There is absolutely no other information carried
> in an
> instance of this class.  The meaining of a Timestamp object is
> clear and
> unambiguous.  By default, rendering a Timestamp to a String is done
> using the default time zone for the JVM.  This code snippet:
>
>     Timestamp ts = new Timestamp(0L);
>     System.out.println(ts);
>
> renders this on my console:
>
> 1969-12-31 18:00:00.0
>
> because I'm in the U.S. Central time zone.  I could use a specific
> Timezone object to render this as a string appropriate to any time
> zone
> of my choice.  The questions would seem to be:
>
> (1)  When storing a Timestamp object to a database column which is
> defined as a timestamp with a time zone, what time zone should be
> used?
> Regardless of the choice, the value in the column must represent the
> same moment as the original Timestamp object.  It seems clear that
> some
> methods allow you to specify a Calendar object for the sole purpose of
> specifying the time zone, and that in the absence of that, the default
> time zone of the JVM should be used.
>
According to the JDBC API tutorial the time zone of the server
> (2)  When storing a Timestamp object to a database column which is
> defined as a timestamp without a time zone, which time zone's local
> representation of the Timestamp object should be used to convert the
> Timestamp object from a moment to whatever the heck a timestamp
> without
> a time zone is?  The rub is that the semantics of such a database
> column
> are not obvious.  Does it represent the moment corresponding to the
> given year, month, day, hour, minute, second, etc. in the server's
> time
> zone, the client's time zone, GMT, or something else?  There are use
> cases where each of these make sense, although the primary reason for
> HAVING a timestamp without a timezone, would seem to be so that the
> actual moment would be different for different clients.  (For
> example, a
> database used in many time zones, which contained columns to configure
> when late-night maintenance activities on client machines should
> occur.)
>  It seems to me that specifying a time zone on database writes in this
> case should cause the database representation of the moment
> represented
> by the Timestamp object to be the date and time of that moment in the
> specified time zone.
>
> I can't think of anything in the ANSI SQL or JDBC specifications or
> javadocs which provides any direction on what a timestamp without a
> time
> zone represents; however, there are two compelling reasons to use the
> time zone of the client:
>
> (a)  The only reasonable use cases I can see for storing a timestamp
> which does NOT represent a moment in time involve interpreting it as
> different moments based on the client time zone.  This means that
> software the is right and proper would want to use the local timezone,
> at least for retrieval, and we all like symmetry, don't we?
>
> (b)  The most common reason for using a timestamp without a
> timezone is
> certainly that people are assuming that the whole system is based
> around
> a single timezone which they never explicitly specify, and they don't
> want to bother with it.  In this case, the client and server time
> zones
> should match (or it was a very bad choice for data type).  If the time
> zones DON'T match, we're dealing with a fundamentally screwed up
> situation at that point -- the schema is fundamentally
> inappropriate for
> the runtime environment.  It seems to me that allowing the client side
> to specify a time zone when presenting the Timestamp to the
> database is
> the reasonable way to go, with the default being the default time zone
> for the JVM.
>
> The solution to Christian's problem seems to me to be that he should
> either get the timestamps from his source as String objects (using a
> database server side function to convert them to the desired
> character-based representation), and generate the values as
> literals in
> loading the new database,
>
> OR
>
> he should ensure that his client and sever use the same time zone and
> that the Timestamp objects actually represent the moments he cares
> about.
>
> Either should work.
>
> -Kevin
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>

Could you post the URL so that I can find the reference without
searching the whole trail?  (My apologies if I missed this in an earlier
message -- there have been so many on this topic that I sometimes
skimmed.)

That would seem to conflict with information from the URL below, from
which I will paste a quote.

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html

Methods added in the JDBC 2.0 core API make it possible for the driver
to take a specified time zone into account when calculating a date,
time, or timestamp. The time zone information is included in a
java.util.Calendar object that is passed to new versions of the methods
for getting and setting Date, Time, and Timestamp values. When no time
zone is specified, the driver uses the time zone of the virtual machine
running the application when it calculates a date, time, or timestamp.


>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>>

On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:

> (1)  When storing a Timestamp object to a database column which is
> defined as a timestamp with a time zone, what time zone should be
> used?
> Regardless of the choice, the value in the column must represent the
> same moment as the original Timestamp object.  It seems clear that
> some
> methods allow you to specify a Calendar object for the sole purpose of
> specifying the time zone, and that in the absence of that, the default
> time zone of the JVM should be used.
>
According to the JDBC API tutorial the time zone of the server

Sorry, I have the book, no url. I'll see if I can resolve this though

Dave
On 21-Jul-05, at 5:09 PM, Kevin Grittner wrote:

> Could you post the URL so that I can find the reference without
> searching the whole trail?  (My apologies if I missed this in an
> earlier
> message -- there have been so many on this topic that I sometimes
> skimmed.)
>
> That would seem to conflict with information from the URL below, from
> which I will paste a quote.
>
> http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html
>
> Methods added in the JDBC 2.0 core API make it possible for the driver
> to take a specified time zone into account when calculating a date,
> time, or timestamp. The time zone information is included in a
> java.util.Calendar object that is passed to new versions of the
> methods
> for getting and setting Date, Time, and Timestamp values. When no time
> zone is specified, the driver uses the time zone of the virtual
> machine
> running the application when it calculates a date, time, or timestamp.
>
>
>
>>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>>
>>>>
>
> On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:
>
>
>> (1)  When storing a Timestamp object to a database column which is
>> defined as a timestamp with a time zone, what time zone should be
>> used?
>> Regardless of the choice, the value in the column must represent the
>> same moment as the original Timestamp object.  It seems clear that
>> some
>> methods allow you to specify a Calendar object for the sole
>> purpose of
>> specifying the time zone, and that in the absence of that, the
>> default
>> time zone of the JVM should be used.
>>
>>
> According to the JDBC API tutorial the time zone of the server
>
>
>

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>That's correct. A Timestamp is essentially a wrapper around
>>milliseconds-since-epoch.
>
>
> OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
> (at the moment anyway --- there's some agitation for putting an
> explicit zone identifier in too, because it looks like the SQL spec
> requires that).

After some thought, an explicit zone identifier would solve the
java-side problems if it also meant that the timestamptz to timestamp
cast was done using that timezone.

i.e. currently the core of the problem is this behaviour:

template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
      timestamp
---------------------
 2005-01-01 18:00:00
(1 row)

This is not the most obvious result until you realize that timestamptz
loses timezone info:

template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
      timestamptz
------------------------
 2005-01-01 18:00:00+13
(1 row)

I'd like to see the first case above produce 15:00:00, which seems
doable if timestamptz is storing a timezone offset as well as a
milliseconds-per-epoch.

If we had that behaviour, then we just always pass down a timestamptz
parameter with an appropriate timezone, and any implicit cast to
timestamp "just works". This is similar in effect to using UNKNOWN in
some ways since with UNKNOWN, if it really is a timestamp that is
needed, the timestamp parser works on the input directly and throws away
the timezone rather than doing the timezone juggling that timestamptz ->
timestamp does:

template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
      timestamp
---------------------
 2005-01-01 15:00:00
(1 row)

-O

Oliver Jowett <oliver@opencloud.com> writes:
> i.e. currently the core of the problem is this behaviour:

> template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
>       timestamp
> ---------------------
>  2005-01-01 18:00:00
> (1 row)

Well, the current interpretation is that timestamptz -> timestamp
produces a timestamp representing what the timestamptz equates to in
your current TimeZone.  I do not foresee changing that behavior when/if
we add explicit TZ info to timestamptz --- it would break just about
every existing app that uses this conversion.

In any case, this wouldn't solve Christian's complaint, because the root
of his problem is that the value ever goes through timestamptz at all.
That is necessarily going to "munge" values that don't correspond to
legal local times in whatever zone you are using.

The more I think about this, the more I think that the correct solution
must include having the driver set TimeZone = UTC (and complain if the
app tries to change it).  Then you can specify parameter types as either
timestamp or timestamptz, it doesn't really matter, because conversions
between them on the server side will be no-ops.  When you convert a Java
Timestamp to send to the server, you always convert it using a UTC
Calendar object.  I'm not sure if the setTimestamp variants with a
Calendar are special in this regime; arguably you should ignore the
supplied Calendar, on the principle that you know what the Timestamp is
supposed to mean.

            regards, tom lane

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>i.e. currently the core of the problem is this behaviour:
>
>
>>template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
>>      timestamp
>>---------------------
>> 2005-01-01 18:00:00
>>(1 row)
>
>
> Well, the current interpretation is that timestamptz -> timestamp
> produces a timestamp representing what the timestamptz equates to in
> your current TimeZone.  I do not foresee changing that behavior when/if
> we add explicit TZ info to timestamptz --- it would break just about
> every existing app that uses this conversion.

I can understand backwards compatibility issues, but it doesn't make my
example above any more obvious :/

> The more I think about this, the more I think that the correct solution
> must include having the driver set TimeZone = UTC (and complain if the
> app tries to change it).  Then you can specify parameter types as either
> timestamp or timestamptz, it doesn't really matter, because conversions
> between them on the server side will be no-ops.  When you convert a Java
> Timestamp to send to the server, you always convert it using a UTC
> Calendar object.  I'm not sure if the setTimestamp variants with a
> Calendar are special in this regime; arguably you should ignore the
> supplied Calendar, on the principle that you know what the Timestamp is
> supposed to mean.

I don't think this works, as we need to support the Calendar variants.
Specifically, given this code:

  // 2005-01-01 05:00:00 UTC
  Timestamp now = new Timestamp(1104555600*1000L);
  Calender localCalendar = Calendar.getInstance(new Locale("en","NZ"));
  ps.setTimestamp(1, now, localCalendar);

I would expect it to end up setting a timestamptz value of '2005-01-01
05:00:00 +0000' (or an equivalent instant as represented in some other
timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in
January). I think this is the correct interpretation given what the
javadoc says about setTimestamp() using the provided calendar to
construct an appropriate SQL TIMESTAMP value. The JDBC spec may be lousy
but I don't think they added the Calendar variants with the intention of
having drivers completely ignore that parameter..

I don't see how we can handle this even with TimeZone = UTC:

If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong
if implicitly cast to timestamp; if we pass it as timestamp then it's
just the wrong value immediately.

If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still
doesn't cast to the correct timestamp value; if we pass it as timestamp
then it is the right timestamp value, but is wrong if it is then cast to
timestamptz.

Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for
both timestamp and timestamptz as there's no implicit casting involved,
but we run the risk of getting the type wrong or not being able to
determine a type at all. Conversely, this is effectively what the V2
protocol path is doing anyway.

If we can't change the casting behaviour, and TimeZone hackery doesn't
work, and we want to support both timestamp and timestamptz via the
standard interfaces, then it seems like passing the parameters as
UNKNOWN is the only practical option :(

-O

Oliver Jowett wrote:

>   Calender localCalendar = Calendar.getInstance(new Locale("en","NZ"));

Sorry, getting locale and timezone confused, I meant something like this:

Calendar localCalendar = Calendar.getInstance(TimeZone.getInstance("NZST"));

-O

I've hacked my local version to track the servers time zone and use
the calendar appropriate to the servers time zone.
This would allow the user to set the driver, and server time zone to
UTC as suggested below.

One issue I have is that the server returns some time zones in
lowercase.

ie EST -> est
     EST5EDT -> est5edt

java doesn't understand these. Why is the server changing them to
lower case?

Dave

On 21-Jul-05, at 9:57 PM, Oliver Jowett wrote:

> Tom Lane wrote:
>
>> Oliver Jowett <oliver@opencloud.com> writes:
>>
>>
>>> i.e. currently the core of the problem is this behaviour:
>>>
>>
>>
>>
>>> template1=# select '2005-01-01 15:00:00
>>> +1000'::timestamptz::timestamp;
>>>      timestamp
>>> ---------------------
>>> 2005-01-01 18:00:00
>>> (1 row)
>>>
>>
>>
>> Well, the current interpretation is that timestamptz -> timestamp
>> produces a timestamp representing what the timestamptz equates to in
>> your current TimeZone.  I do not foresee changing that behavior
>> when/if
>> we add explicit TZ info to timestamptz --- it would break just about
>> every existing app that uses this conversion.
>>
>
> I can understand backwards compatibility issues, but it doesn't
> make my
> example above any more obvious :/
>
>
>> The more I think about this, the more I think that the correct
>> solution
>> must include having the driver set TimeZone = UTC (and complain if
>> the
>> app tries to change it).  Then you can specify parameter types as
>> either
>> timestamp or timestamptz, it doesn't really matter, because
>> conversions
>> between them on the server side will be no-ops.  When you convert
>> a Java
>> Timestamp to send to the server, you always convert it using a UTC
>> Calendar object.  I'm not sure if the setTimestamp variants with a
>> Calendar are special in this regime; arguably you should ignore the
>> supplied Calendar, on the principle that you know what the
>> Timestamp is
>> supposed to mean.
>>
>
> I don't think this works, as we need to support the Calendar variants.
> Specifically, given this code:
>
>   // 2005-01-01 05:00:00 UTC
>   Timestamp now = new Timestamp(1104555600*1000L);
>   Calender localCalendar = Calendar.getInstance(new Locale
> ("en","NZ"));
>   ps.setTimestamp(1, now, localCalendar);
>
> I would expect it to end up setting a timestamptz value of '2005-01-01
> 05:00:00 +0000' (or an equivalent instant as represented in some other
> timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in
> January). I think this is the correct interpretation given what the
> javadoc says about setTimestamp() using the provided calendar to
> construct an appropriate SQL TIMESTAMP value. The JDBC spec may be
> lousy
> but I don't think they added the Calendar variants with the
> intention of
> having drivers completely ignore that parameter..
>
> I don't see how we can handle this even with TimeZone = UTC:
>
> If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong
> if implicitly cast to timestamp; if we pass it as timestamp then it's
> just the wrong value immediately.
>
> If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still
> doesn't cast to the correct timestamp value; if we pass it as
> timestamp
> then it is the right timestamp value, but is wrong if it is then
> cast to
> timestamptz.
>
> Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for
> both timestamp and timestamptz as there's no implicit casting
> involved,
> but we run the risk of getting the type wrong or not being able to
> determine a type at all. Conversely, this is effectively what the V2
> protocol path is doing anyway.
>
> If we can't change the casting behaviour, and TimeZone hackery doesn't
> work, and we want to support both timestamp and timestamptz via the
> standard interfaces, then it seems like passing the parameters as
> UNKNOWN is the only practical option :(
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>

I looked for this in the trail, and couldn't find it.
So here it is verbatim

setTimestamp with a calendar

Sets parameter number parameterIndex to x. When the DBMS does not
store time zone information, the driver will use cal to construct a
JDBC Timestamp value which it then sends to the database. With a
Calendar object the driver can calculate the Timestamp object taking
into account a custom time zone. If no Calendar object is specified,
the driver uses the time zone of the Virtual Machine that is running
the application.


So my interpretation is.

If we are storing a timestamptz then ignore cal and use the server
time zone
If we are storing a timestamp then use cal to calculate the
timestamp; if cal is null then use the JVM's  time zone.

getTimestamp is a little less clear

When a getTimestamp method is called, the driver converts a JDBC
TIMESTAMP instance, which is generally a string, to a
java.sql.Timestamp object, which is a milliseconds value. In order to
calculate the milliseconds, the driver takes into account the time
zone, information a DBMS may or may not store. Because a Timestamp
object itself has no way to keep track of time zone information, the
driver relies on a java.util.Calendar object to get this information.
If no Calendar object
is supplied the 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 time zone information, the driver will simply use that
and ignore a Calendar object that may be passed to it. Therefore to
be portable, an application should supply a Calendar object to a
getTimestamp method thereby not relying on the DBMS to supply a time
zone but still being able to use the time zone supplied by a DBMS
when one is available.


I'm unclear whether "provides time zone information" means the server
time zone, or time zone returned in the data.

That aside:

If the server provides time zone info, use that to create the
timestamp, regardless of calendar supplied
if the server does not provide time zone info, use calendar supplied,
if that is null or not supplied then use JVM calendar.

Dave


On 21-Jul-05, at 5:09 PM, Kevin Grittner wrote:

> Could you post the URL so that I can find the reference without
> searching the whole trail?  (My apologies if I missed this in an
> earlier
> message -- there have been so many on this topic that I sometimes
> skimmed.)
>
> That would seem to conflict with information from the URL below, from
> which I will paste a quote.
>
> http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html
>
> Methods added in the JDBC 2.0 core API make it possible for the driver
> to take a specified time zone into account when calculating a date,
> time, or timestamp. The time zone information is included in a
> java.util.Calendar object that is passed to new versions of the
> methods
> for getting and setting Date, Time, and Timestamp values. When no time
> zone is specified, the driver uses the time zone of the virtual
> machine
> running the application when it calculates a date, time, or timestamp.
>
>
>
>>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>>
>>>>
>
> On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:
>
>
>> (1)  When storing a Timestamp object to a database column which is
>> defined as a timestamp with a time zone, what time zone should be
>> used?
>> Regardless of the choice, the value in the column must represent the
>> same moment as the original Timestamp object.  It seems clear that
>> some
>> methods allow you to specify a Calendar object for the sole
>> purpose of
>> specifying the time zone, and that in the absence of that, the
>> default
>> time zone of the JVM should be used.
>>
>>
> According to the JDBC API tutorial the time zone of the server
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

On 21-Jul-05, at 8:54 PM, Tom Lane wrote:

> Oliver Jowett <oliver@opencloud.com> writes:
>
>> i.e. currently the core of the problem is this behaviour:
>>
>
>
>> template1=# select '2005-01-01 15:00:00
>> +1000'::timestamptz::timestamp;
>>       timestamp
>> ---------------------
>>  2005-01-01 18:00:00
>> (1 row)
>>
>
> Well, the current interpretation is that timestamptz -> timestamp
> produces a timestamp representing what the timestamptz equates to in
> your current TimeZone.  I do not foresee changing that behavior
> when/if
> we add explicit TZ info to timestamptz --- it would break just about
> every existing app that uses this conversion.
>
> In any case, this wouldn't solve Christian's complaint, because the
> root
> of his problem is that the value ever goes through timestamptz at all.
> That is necessarily going to "munge" values that don't correspond to
> legal local times in whatever zone you are using.
>
> The more I think about this, the more I think that the correct
> solution
> must include having the driver set TimeZone = UTC (and complain if the
> app tries to change it).

This really isn't an option. We can't impose limits on the
application like this.

> Then you can specify parameter types as either
> timestamp or timestamptz, it doesn't really matter, because
> conversions
> between them on the server side will be no-ops.  When you convert a
> Java
> Timestamp to send to the server, you always convert it using a UTC
> Calendar object.  I'm not sure if the setTimestamp variants with a
> Calendar are special in this regime; arguably you should ignore the
> supplied Calendar, on the principle that you know what the
> Timestamp is
> supposed to mean.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

Dave Cramer <pg@fastcrypt.com> writes:
> One issue I have is that the server returns some time zones in
> lowercase.

> ie EST -> est
>      EST5EDT -> est5edt

I think that in some contexts the case you will get back is what you
give in the SET command.  So do
    set timezone = 'EST5EDT';
not
    set timezone = EST5EDT;
(the latter is downcased as if it were an identifier).

            regards, tom lane

Dave Cramer <pg@fastcrypt.com> writes:
> On 21-Jul-05, at 8:54 PM, Tom Lane wrote:
>> The more I think about this, the more I think that the correct
>> solution
>> must include having the driver set TimeZone = UTC (and complain if the
>> app tries to change it).

> This really isn't an option. We can't impose limits on the
> application like this.

Why not?  Why should the application care what the server's timezone is?
Especially in a context where it's up to the driver to manage all these
conversion issues...

            regards, tom lane

On 22-Jul-05, at 9:42 AM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>
>> One issue I have is that the server returns some time zones in
>> lowercase.
>>
>
>
>> ie EST -> est
>>      EST5EDT -> est5edt
>>
>
> I think that in some contexts the case you will get back is what you
> give in the SET command.  So do
>     set timezone = 'EST5EDT';
> not
>     set timezone = EST5EDT;
> (the latter is downcased as if it were an identifier).
>
Ick .... this makes things tougher ... The user won't necessarily
quote it if they change it.

I guess if we restrict changing it, as per your other suggestion,
then this goes away.
>             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
>
>

Argh!  I've found some comments on this thread confusing, and I've
discovered the source of this confusion -- I was thinking that a column
defined as type "timestamp with time zone" would store a time zone with
the timestamp.  (Really, I did read the documentation on data types, but
the discussion of how this data type is implemented slid right out of my
head.)  I will have to review everything with the actual implementation
in mind; I now see why this is more complicated than I thought.

Could someone confirm that a timestamp column in a PostgreSQL database
never contains time zone data, regardless of whether or not the "with
time zone" clause was used in its definition.  It seems to me that, if
you ignore internals of how the data is stored (which an application
programmer never need see), the only difference between "timestamp with
time zone" and "timestamp without time zone" is whether a time zone
specified in a literal will (a) be used to determine what moment the
timestamp represents or (b) be ignored in favor of using the server's
time zone to determine what moment the timestamp represents.  In either
case, the value returned when querying the column will be based on the
server's time zone.  Do I have that right?

-Kevin

On 22-Jul-05, at 12:30 PM, Kevin Grittner wrote:

> Argh!  I've found some comments on this thread confusing, and I've
> discovered the source of this confusion -- I was thinking that a
> column
> defined as type "timestamp with time zone" would store a time zone
> with
> the timestamp.  (Really, I did read the documentation on data
> types, but
> the discussion of how this data type is implemented slid right out
> of my
> head.)  I will have to review everything with the actual
> implementation
> in mind; I now see why this is more complicated than I thought.
>
> Could someone confirm that a timestamp column in a PostgreSQL database
> never contains time zone data, regardless of whether or not the "with
> time zone" clause was used in its definition.
yes, that is correct.
> It seems to me that, if
> you ignore internals of how the data is stored (which an application
> programmer never need see), the only difference between "timestamp
> with
> time zone" and "timestamp without time zone" is whether a time zone
> specified in a literal will (a) be used to determine what moment the
> timestamp represents or (b) be ignored in favor of using the server's
> time zone to determine what moment the timestamp represents.  In
> either
> case, the value returned when querying the column will be based on the
> server's time zone.  Do I have that right?
That  is my understanding yhest.
>
> -Kevin
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Could someone confirm that a timestamp column in a PostgreSQL database
> never contains time zone data, regardless of whether or not the "with
> time zone" clause was used in its definition.  It seems to me that, if
> you ignore internals of how the data is stored (which an application
> programmer never need see), the only difference between "timestamp with
> time zone" and "timestamp without time zone" is whether a time zone
> specified in a literal will (a) be used to determine what moment the
> timestamp represents or (b) be ignored in favor of using the server's
> time zone to determine what moment the timestamp represents.  In either
> case, the value returned when querying the column will be based on the
> server's time zone.  Do I have that right?

No, not really.

Timestamp with time zone is effectively equivalent to Java's Timestamp
(as the latter was explained to me in this thread, anyway): all it
stores is an absolute time instant referenced to UTC.  On input, any
time zone specified (or implied --- the default is to use the TimeZone
setting) is handled by rotating the value to UTC.  On output, the server
rotates the time instant into the current TimeZone for display.  So
setting TimeZone is equivalent to using a Calendar for conversion of
a Java Timestamp.

There has been talk of changing this behavior for improved SQL spec
compatibility, but that's how it is at the moment.

Timestamp without time zone is a fundamentally different animal, because
there simply is no concept of time zones at all.  It will store any
y/m/d/h/m/s values that are legal per the rules of the Gregorian
calendar.  Changing TimeZone does not affect either input interpretation
or display of a previously stored value, where TimeZone does affect both
input and display behavior of timestamptz.

I think it's really a mistake to consider timestamp without time zone
as representing any absolutely identifiable time instant at all.
Unfortunately, we are probably going to have to deal with complaints
from people who have used it in ways that amount to assuming it does
represent such an instant ...

            regards, tom lane

I'm not trying to be argumentative here, but I'm afraid I'm missing
something important.  In what way is the behavior you describe
functionally different to someone using the database from what I
described?  What differences in _behavior_ are exposed to someone
_using_ the database, other than ignoring time zone information for the
"timestamp without time zone" values?

Thanks for any clarity you can bring me on this.

-Kevin


>>> Tom Lane <tgl@sss.pgh.pa.us> 07/22/05 12:03 PM >>>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Could someone confirm that a timestamp column in a PostgreSQL database
> never contains time zone data, regardless of whether or not the "with
> time zone" clause was used in its definition.  It seems to me that, if
> you ignore internals of how the data is stored (which an application
> programmer never need see), the only difference between "timestamp
with
> time zone" and "timestamp without time zone" is whether a time zone
> specified in a literal will (a) be used to determine what moment the
> timestamp represents or (b) be ignored in favor of using the server's
> time zone to determine what moment the timestamp represents.  In
either
> case, the value returned when querying the column will be based on the
> server's time zone.  Do I have that right?

No, not really.

Timestamp with time zone is effectively equivalent to Java's Timestamp
(as the latter was explained to me in this thread, anyway): all it
stores is an absolute time instant referenced to UTC.  On input, any
time zone specified (or implied --- the default is to use the TimeZone
setting) is handled by rotating the value to UTC.  On output, the server
rotates the time instant into the current TimeZone for display.  So
setting TimeZone is equivalent to using a Calendar for conversion of
a Java Timestamp.

There has been talk of changing this behavior for improved SQL spec
compatibility, but that's how it is at the moment.

Timestamp without time zone is a fundamentally different animal, because
there simply is no concept of time zones at all.  It will store any
y/m/d/h/m/s values that are legal per the rules of the Gregorian
calendar.  Changing TimeZone does not affect either input interpretation
or display of a previously stored value, where TimeZone does affect both
input and display behavior of timestamptz.

I think it's really a mistake to consider timestamp without time zone
as representing any absolutely identifiable time instant at all.
Unfortunately, we are probably going to have to deal with complaints
from people who have used it in ways that amount to assuming it does
represent such an instant ...

            regards, tom lane

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> What differences in _behavior_ are exposed to someone
> _using_ the database, other than ignoring time zone information for the
> "timestamp without time zone" values?

Well, other than the point that changing TimeZone changes the displayed
values of timestamptz but not timestamp, there's the fact that
timestamptz will reject (or change, actually) some values that timestamp
will consider valid.  For instance '2005-04-03 02:30' is valid to
timestamp, but in any US timezone it will come out as something
different in timestamptz.

regression=# show timezone;
 TimeZone
----------
 EST5EDT
(1 row)

regression=# select '2005-04-03 02:30'::timestamp;
      timestamp
---------------------
 2005-04-03 02:30:00
(1 row)

regression=# select '2005-04-03 02:30'::timestamptz;
      timestamptz
------------------------
 2005-04-03 03:30:00-04
(1 row)

            regards, tom lane

Dave Cramer wrote:
> I looked for this in the trail, and couldn't find it.
> So here it is verbatim
>
> setTimestamp with a calendar
>
> Sets parameter number parameterIndex to x. When the DBMS does not  store
> time zone information, the driver will use cal to construct a  JDBC
> Timestamp value which it then sends to the database. With a  Calendar
> object the driver can calculate the Timestamp object taking  into
> account a custom time zone. If no Calendar object is specified,  the
> driver uses the time zone of the Virtual Machine that is running  the
> application.
>
>
> So my interpretation is.
>
> If we are storing a timestamptz then ignore cal and use the server  time
> zone
> If we are storing a timestamp then use cal to calculate the  timestamp;
> if cal is null then use the JVM's  time zone.

Couple of things..

a) How do we know what type we are storing without invoking UNKNOWN again?
b) The server doesn't actually store timezone information with a
timestamptz (this confused me initially, but Tom clarified), so
shouldn't we be doing what the book says and *using* the calendar when
we store to a timestamptz? (it's just a representation issue, it doesn't
actually affect the value stored in the end.. unless you plug it into a
text column or something..)

> That aside:
>
> If the server provides time zone info, use that to create the
> timestamp, regardless of calendar supplied
> if the server does not provide time zone info, use calendar supplied,
> if that is null or not supplied then use JVM calendar.

That makes sense -- basically it means that if we are reading a
timestamptz we construct the Timestamp directly to represent that
instant, ignoring the calendar supplied. If we are reading a timestamp,
then we turn it into a Timestamp using the provided or JVM-default calendar.

-O

Tom Lane wrote:
> Vadim Nasardinov <vadimn@redhat.com> writes:
>
>>If 8.0 comes with its own copy of a recent zic database, then, in
>>theory, it should be able to handle any timezone known to Java.  (The
>>opposite is not necessarily true, as Java's timezone info seems to be
>>missing a lot of recent changes.)  Does that sound like a correct
>>statement to you or did I forget some important qualifiers?
>
>
> Yeah, it seems that we are just open to version skew issues between
> different copies of the zic database ... which changes enough that
> that's a nontrivial risk, but I fear we are unlikely to do better.

I noticed in passing that Java and postgres have different ideas about
what (for example) "GMT+10" as a timezone name means. Java considers
that to be east of GMT, postgres thinks it is west.

-O