Thread: Timestamp weirdness

Timestamp weirdness

From
"emergency.shower@gmail.com"
Date:
Hi,

With PostgreSQL 8.0.3 and the postgresql-jdbc-8.1dev-400
JDBC driver, there are a number of problems when writing timestamps to
and reading them from the database.

First of all, I would expect
 PreparedStatemant#setTimestamp("fld", Timestamp, Calendar)
and
 ResultSet#getTimestamp("fld", Calendar)
to be complementary methods.

When writing a java.sql.Timestamp to a TIMESTAMP WITHOUT TIME ZONE or
to a TIMESTAMP WITH TIME ZONE database field and then reading it using
the same java.util.Calendar should return the same Timestamp value that
was previously written to the database.

However, if

 final java.sql Timestamp ts;
 final java.util.Calendar cal;

 ts = new Timestamp(0L);
 ts.setNanos(0);
 cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

then writing with

 PreparedStatemant#setTimestamp("fld", ts, cal)

and reading with

 ResultSet#getTimestamp("fld", Calendar)

will give a result that differs from the original value by the
server's local time zone offset if "fld" is TIMESTAMP WITHOUT TIME
ZONE.

When reading/writing to a TIMESTAMP WITH TIME ZONE the correctness of
the result depends on the server's time zone. E.g. if the server has
the Newfoundland time zone the result differs from the correct one by
30 minutes.

If we use a Calendar other than UTC, with a non-zero time zone offset,
the results are generally incorrect and difficult to predict. They
seem to depend on

- the database servers local time zone,
- the the Calendar's time zone offset,
- the database field's WITH or WITHOUT TIME ZONE attribute,
- and rounding errors if the Calendar's time zone offset contains
fractions of hours.


Here's the behaviour that I would expect:

1) The values in the database should not depend on the database's
local time zone.

2) When writing to a TIMESTAMP WITH TIME ZONE field, the driver should
not perform any time zone conversions and should store the Timestamp's
(UTC) y, M, d, H, m, s values directly to the database. The Calendar,
if given, should be ignored.

3) When writing to a TIMESTAMP WITHOUT TIME ZONE field, the driver
should calculate the Timestamp's y, M, d, H, m, s values in the given
Calendar's time zone and should store these values in the database.

4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
should create a Timestamp by interpreting the y, M, d, H, m, s values
as UTC timestamp fields. The Calendar, if given, should be ignored.

5) When reading from a TIMESTAMP WITHOUT TIME ZONE field, the driver
should create a Timestamp by interpreting the y, M, d, H, m, s values
in the context of the given Calendar.


Does this make sense?


Regards,
Alex

Re: Timestamp weirdness

From
Oliver Jowett
Date:
emergency.shower@gmail.com wrote:

> With PostgreSQL 8.0.3 and the postgresql-jdbc-8.1dev-400
> JDBC driver, there are a number of problems when writing timestamps to
> and reading them from the database.

Have you read the long and involved threads that started a few days ago
on pgsql-jdbc? It's worth reading them in the archives if you haven't
already.

Basically, these problems are known about and we're trying to hash out a
reasonable solution..

> Here's the behaviour that I would expect:
>
> 1) The values in the database should not depend on the database's
> local time zone.

Since WITH TIME ZONE <-> WITHOUT TIME ZONE conversions use the server's
TimeZone setting, this requires that there are no implicit conversions
between the two happening on the setTimestamp() / getTimestamp() paths.

> 2) When writing to a TIMESTAMP WITH TIME ZONE field, the driver should
> not perform any time zone conversions and should store the Timestamp's
> (UTC) y, M, d, H, m, s values directly to the database. The Calendar,
> if given, should be ignored.
>
> 3) When writing to a TIMESTAMP WITHOUT TIME ZONE field, the driver
> should calculate the Timestamp's y, M, d, H, m, s values in the given
> Calendar's time zone and should store these values in the database.
>
> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
> should create a Timestamp by interpreting the y, M, d, H, m, s values
> as UTC timestamp fields. The Calendar, if given, should be ignored.
>
> 5) When reading from a TIMESTAMP WITHOUT TIME ZONE field, the driver
> should create a Timestamp by interpreting the y, M, d, H, m, s values
> in the context of the given Calendar.

(3 and 5: ... or the JVM's default Calendar if none is specified)

This is also the behaviour I'd want, but I don't think I have 100%
agreement on that from everyone yet.

-O

Re: Timestamp weirdness

From
Tom Lane
Date:
Oliver Jowett <oliver@opencloud.com> writes:
> emergency.shower@gmail.com wrote:
>> 2) When writing to a TIMESTAMP WITH TIME ZONE field, the driver should
>> not perform any time zone conversions and should store the Timestamp's
>> (UTC) y, M, d, H, m, s values directly to the database. The Calendar,
>> if given, should be ignored.
>>
>> 3) When writing to a TIMESTAMP WITHOUT TIME ZONE field, the driver
>> should calculate the Timestamp's y, M, d, H, m, s values in the given
>> Calendar's time zone and should store these values in the database.
>>
>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>
>> 5) When reading from a TIMESTAMP WITHOUT TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> in the context of the given Calendar.

> (3 and 5: ... or the JVM's default Calendar if none is specified)

> This is also the behaviour I'd want, but I don't think I have 100%
> agreement on that from everyone yet.

Surely 4 should read "by interpreting the y...s values as a timestamp
in the zone specified as part of the value", not as necessarily UTC.
5 seems ok to me.

The difficulty with both 2 and 3 is that the driver has no very good way
of knowing whether it's writing to a timestamp with tz or one without.
We can know the parameter datatype we send, but if that gets converted
to the other type within the server, you're going to get burnt.

            regards, tom lane

Re: Timestamp weirdness

From
Oliver Jowett
Date:
Tom Lane wrote:

>>emergency.shower@gmail.com wrote:
>>
>>>4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>>>should create a Timestamp by interpreting the y, M, d, H, m, s values
>>>as UTC timestamp fields. The Calendar, if given, should be ignored.

> Surely 4 should read "by interpreting the y...s values as a timestamp
> in the zone specified as part of the value", not as necessarily UTC.

Yes, you're right.

> The difficulty with both 2 and 3 is that the driver has no very good way
> of knowing whether it's writing to a timestamp with tz or one without.
> We can know the parameter datatype we send, but if that gets converted
> to the other type within the server, you're going to get burnt.

I'm leaning towards using UNKNOWN as the least-bad option for now, plus
some extension mechanism so you can override it if the type inference
does go wrong. Hopefully that should make the commonly-used cases work
without applications needing to do anything weird.

-O

Re: Timestamp weirdness

From
Dave Cramer
Date:
On 24-Jul-05, at 7:41 PM, Oliver Jowett wrote:

> Tom Lane wrote:
>
>
>>> emergency.shower@gmail.com wrote:
>>>
>>>
>>>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>>>> should create a Timestamp by interpreting the y, M, d, H, m, s
>>>> values
>>>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>>>
>
>
>> Surely 4 should read "by interpreting the y...s values as a timestamp
>> in the zone specified as part of the value", not as necessarily UTC.
>>
>
> Yes, you're right.
>
>
>> The difficulty with both 2 and 3 is that the driver has no very
>> good way
>> of knowing whether it's writing to a timestamp with tz or one
>> without.
>> We can know the parameter datatype we send, but if that gets
>> converted
>> to the other type within the server, you're going to get burnt.
>>
>
> I'm leaning towards using UNKNOWN as the least-bad option for now,
> plus
> some extension mechanism so you can override it if the type inference
> does go wrong. Hopefully that should make the commonly-used cases work
> without applications needing to do anything weird.

Seems like this is the only way to go for now. +1 from me.
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>

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


Re: Timestamp weirdness

From
Tom Lane
Date:
Dave Cramer <pg@fastcrypt.com> writes:
> On 24-Jul-05, at 7:41 PM, Oliver Jowett wrote:
>> I'm leaning towards using UNKNOWN as the least-bad option for now,

> Seems like this is the only way to go for now. +1 from me.

I haven't got a better idea either.  Seems like we should cobble this up
and see how badly it fails, at least ;-)

            regards, tom lane

Re: Timestamp weirdness

From
"Kevin Grittner"
Date:
The Timestamp object in java doesn't have time zone info -- it is UTC.
The goal for timestamp with time zone columns, however it can be done,
is to have the UTC from a Timestamp object match the UTC of the column,
every time, in both directions.  Time zone information should be
irrelevant for this column type.

In Java the role of a time zone with a TImestamp object is to render the
moment in time as a String representation of the local time for that
moment in a particular time zone, or to create a Timestamp moment from a
given local date and time.

I think Alex hit the nail on the head, with the elaboration that when a
Calendar object is not supplied (either the method without it is called
or a null is passed on invocation) the default time zone of the client
JVM should be used.

I don't have my head around the protocol used between the client and the
server, so I don't know if standard behavior can be acheived within that
protocol.  To try to pin that down, could someone help me out and
clarify the following:

  -  I've seen mention of timestamp and timestampz, but I don't know the
scope of them.  (Client side object types?  Server side data structures?
 Server data types?  Protocol data element?)  What are their
characteristics?

  -   I think I've seen mention that the value is turned into a
character representation of year, month, etc. for transfer over the wire
within the protocol.  I don't know whether time zone info is allowed in
that context.

  -   I think I've also seen mention that the client side has no way of
knowing whether or not it is dealing with a column "with time zone".

If the protocol doesn't support passing time zone, and the client
doesn't know whether or not the data type it's sending is for a column
"with time zone", I have a hard time seeing how we can even come close
to handling both correctly.

If (hypothetically) timestampz is a protocol data element which does
include time zone, we might get to acceptable behavior if the JDBC
driver always converted the timestamp representation to the time zone
specified by the Calendar object and passed that time zone along.  The
server would convert back to UTC for "with time zone" data; otherwise it
would ignore the time zone from timestampz and store the year, month,
etc. "as is".  In the other direction, the server could pass "with time
zone" columns as timestampz using whatever time zone it wished (as long
as, with time zone info, it represented the right moment in time) -- the
JDBC driver would use the time zone to build the Timestamp object with
the right UTC offset.  The server would have to pass "without time zone"
values as timestamp (no z), and the JDBC driver would take that as an
indication that it should use the given (or default) time zone to
interpret the value.

That last paragraph is all based a (hopeful) guess as to what goes over
the wire.

-Kevin


>>> Tom Lane <tgl@sss.pgh.pa.us> 07/24/05 5:48 PM >>>
Oliver Jowett <oliver@opencloud.com> writes:
> emergency.shower@gmail.com wrote:
>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> as UTC timestamp fields. The Calendar, if given, should be ignored.

Surely 4 should read "by interpreting the y...s values as a timestamp
in the zone specified as part of the value", not as necessarily UTC.
5 seems ok to me.



Re: Timestamp weirdness

From
Dave Cramer
Date:
Kevin,

FYI, over the wire we have

The Oid of the type. Either timestamp, or timestamptz
then a string representation of the instance in time.

Now unless we look up the column type before sending it, we really
don't know which of the two it is.

We also have the option of using Oid unknown, which will invoke the
backends usual rules for unknown data.

Dave

On 25-Jul-05, at 11:47 AM, Kevin Grittner wrote:

> The Timestamp object in java doesn't have time zone info -- it is UTC.
> The goal for timestamp with time zone columns, however it can be done,
> is to have the UTC from a Timestamp object match the UTC of the
> column,
> every time, in both directions.  Time zone information should be
> irrelevant for this column type.
>
> In Java the role of a time zone with a TImestamp object is to
> render the
> moment in time as a String representation of the local time for that
> moment in a particular time zone, or to create a Timestamp moment
> from a
> given local date and time.
>
> I think Alex hit the nail on the head, with the elaboration that
> when a
> Calendar object is not supplied (either the method without it is
> called
> or a null is passed on invocation) the default time zone of the client
> JVM should be used.
>
> I don't have my head around the protocol used between the client
> and the
> server, so I don't know if standard behavior can be acheived within
> that
> protocol.  To try to pin that down, could someone help me out and
> clarify the following:
>
>   -  I've seen mention of timestamp and timestampz, but I don't
> know the
> scope of them.  (Client side object types?  Server side data
> structures?
>  Server data types?  Protocol data element?)  What are their
> characteristics?
>
>   -   I think I've seen mention that the value is turned into a
> character representation of year, month, etc. for transfer over the
> wire
> within the protocol.  I don't know whether time zone info is
> allowed in
> that context.
>
>   -   I think I've also seen mention that the client side has no
> way of
> knowing whether or not it is dealing with a column "with time zone".
>
> If the protocol doesn't support passing time zone, and the client
> doesn't know whether or not the data type it's sending is for a column
> "with time zone", I have a hard time seeing how we can even come close
> to handling both correctly.
>
> If (hypothetically) timestampz is a protocol data element which does
> include time zone, we might get to acceptable behavior if the JDBC
> driver always converted the timestamp representation to the time zone
> specified by the Calendar object and passed that time zone along.  The
> server would convert back to UTC for "with time zone" data;
> otherwise it
> would ignore the time zone from timestampz and store the year, month,
> etc. "as is".  In the other direction, the server could pass "with
> time
> zone" columns as timestampz using whatever time zone it wished (as
> long
> as, with time zone info, it represented the right moment in time)
> -- the
> JDBC driver would use the time zone to build the Timestamp object with
> the right UTC offset.  The server would have to pass "without time
> zone"
> values as timestamp (no z), and the JDBC driver would take that as an
> indication that it should use the given (or default) time zone to
> interpret the value.
>
> That last paragraph is all based a (hopeful) guess as to what goes
> over
> the wire.
>
> -Kevin
>
>
>
>>>> Tom Lane <tgl@sss.pgh.pa.us> 07/24/05 5:48 PM >>>
>>>>
> Oliver Jowett <oliver@opencloud.com> writes:
>
>> emergency.shower@gmail.com wrote:
>>
>>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>>> should create a Timestamp by interpreting the y, M, d, H, m, s
>>> values
>>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>>
>
> Surely 4 should read "by interpreting the y...s values as a timestamp
> in the zone specified as part of the value", not as necessarily UTC.
> 5 seems ok to me.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>


Re: Timestamp weirdness

From
"Kevin Grittner"
Date:
Hi Dave,

If the string representation of the timestampz value is allowed to
contain a time zone, I think what I describe would answer everyone's
needs WITHOUT forcing an unusual time zone in the JVM or setting the
server or the connection to a specific time zone.  Am I missing
something?  Would this require some change on the server side which
would break things?  (I don't think so, from what I've read.)

-Kevin


>>> Dave Cramer <pg@fastcrypt.com> 07/25/05 12:39 PM >>>
Kevin,

FYI, over the wire we have

The Oid of the type. Either timestamp, or timestamptz
then a string representation of the instance in time.

Now unless we look up the column type before sending it, we really
don't know which of the two it is.

We also have the option of using Oid unknown, which will invoke the
backends usual rules for unknown data.

Dave

On 25-Jul-05, at 11:47 AM, Kevin Grittner wrote:

> The Timestamp object in java doesn't have time zone info -- it is UTC.
> The goal for timestamp with time zone columns, however it can be done,
> is to have the UTC from a Timestamp object match the UTC of the
> column,
> every time, in both directions.  Time zone information should be
> irrelevant for this column type.
>
> In Java the role of a time zone with a TImestamp object is to
> render the
> moment in time as a String representation of the local time for that
> moment in a particular time zone, or to create a Timestamp moment
> from a
> given local date and time.
>
> I think Alex hit the nail on the head, with the elaboration that
> when a
> Calendar object is not supplied (either the method without it is
> called
> or a null is passed on invocation) the default time zone of the client
> JVM should be used.
>
> I don't have my head around the protocol used between the client
> and the
> server, so I don't know if standard behavior can be acheived within
> that
> protocol.  To try to pin that down, could someone help me out and
> clarify the following:
>
>   -  I've seen mention of timestamp and timestampz, but I don't
> know the
> scope of them.  (Client side object types?  Server side data
> structures?
>  Server data types?  Protocol data element?)  What are their
> characteristics?
>
>   -   I think I've seen mention that the value is turned into a
> character representation of year, month, etc. for transfer over the
> wire
> within the protocol.  I don't know whether time zone info is
> allowed in
> that context.
>
>   -   I think I've also seen mention that the client side has no
> way of
> knowing whether or not it is dealing with a column "with time zone".
>
> If the protocol doesn't support passing time zone, and the client
> doesn't know whether or not the data type it's sending is for a column
> "with time zone", I have a hard time seeing how we can even come close
> to handling both correctly.
>
> If (hypothetically) timestampz is a protocol data element which does
> include time zone, we might get to acceptable behavior if the JDBC
> driver always converted the timestamp representation to the time zone
> specified by the Calendar object and passed that time zone along.  The
> server would convert back to UTC for "with time zone" data;
> otherwise it
> would ignore the time zone from timestampz and store the year, month,
> etc. "as is".  In the other direction, the server could pass "with
> time
> zone" columns as timestampz using whatever time zone it wished (as
> long
> as, with time zone info, it represented the right moment in time)
> -- the
> JDBC driver would use the time zone to build the Timestamp object with
> the right UTC offset.  The server would have to pass "without time
> zone"
> values as timestamp (no z), and the JDBC driver would take that as an
> indication that it should use the given (or default) time zone to
> interpret the value.
>
> That last paragraph is all based a (hopeful) guess as to what goes
> over
> the wire.
>
> -Kevin
>
>
>
>>>> Tom Lane <tgl@sss.pgh.pa.us> 07/24/05 5:48 PM >>>
>>>>
> Oliver Jowett <oliver@opencloud.com> writes:
>
>> emergency.shower@gmail.com wrote:
>>
>>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>>> should create a Timestamp by interpreting the y, M, d, H, m, s
>>> values
>>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>>
>
> Surely 4 should read "by interpreting the y...s values as a timestamp
> in the zone specified as part of the value", not as necessarily UTC.
> 5 seems ok to me.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>


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

               http://archives.postgresql.org


Re: Timestamp weirdness

From
Oliver Jowett
Date:
Kevin Grittner wrote:

> If (hypothetically) timestampz is a protocol data element which does
> include time zone,

timestamptz is TIMESTAMP WITH TIME ZONE; it does include a timezone. The
server uses that timezone to determine the instant the timestamptz
represents when it parses the value.

> we might get to acceptable behavior if the JDBC
> driver always converted the timestamp representation to the time zone
> specified by the Calendar object and passed that time zone along.

This only works if the target type really is timestamptz.

If the target type is actually timestamp (TIMESTAMP WITHOUT TIME ZONE),
the server converts the instant identified by the timestamptz value
using the server's TimeZone setting to get a local date/time, and stores
that. This produces the wrong value if the timezone supplied by the
client isn't the same as the server TimeZone.

The JDBC driver doesn't know ahead of time whether the target type is
timestamp or timestamptz.

What I've suggested elsewhere is to invoke the server behaviour that
usually operates on untyped strings (by passing the "unknown" oid,
rather than either timestamp or timestamptz) when supplying type
information for parameters. The string -> timestamp conversion
*completely ignores* the supplied timezone, just using the specified
date/time directly, so we get the behaviour we want when the desired
type is really a timestamp. There are some disadvantages to this -- the
server doesn't necessarily always get it right, and it'd mask errors
such as using setTimestamp() on a text field -- but it seems the least
worst option.

-O

Re: Timestamp weirdness

From
"emergency.shower@gmail.com"
Date:
On 7/25/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [...]
> The difficulty with both 2 and 3 is that the driver has no very good way
> of knowing whether it's writing to a timestamp with tz or one without.
> We can know the parameter datatype we send, but if that gets converted
> to the other type within the server, you're going to get burnt.

If I TIMESTAMP WITH TIME ZONE is implemented on the server as a
(timestamp, time zone)-pair, the server should be able to cast
TIMESTAMP WITH TIME ZONE correctly to TIMESTAMP WITHOUT TIME ZONE as
described in the SQL standard:

From TIMESTAMP WITH TIME ZONE to TIMESTAMP WITHOUT TIME ZONE
 TargetValue = SourceValue.UTC + SourceValue.TimeZone

This cast is something like a conversion from a instance in time to a
local time representation.

In this case I would propose to send (if possible) timestamp and time
zone to the server, no matter if we'll write a TIMESTAMP WITH TIME
ZONE or a TIMESTAMP WITHOUT TIME ZONE value. We could have some hope
to get correct results then.

Regards,
Alex

Re: Timestamp weirdness

From
Oliver Jowett
Date:
emergency.shower@gmail.com wrote:

> If I TIMESTAMP WITH TIME ZONE is implemented on the server as a
> (timestamp, time zone)-pair

It's not, it's just a timestamp in UTC with no timezone stored.

> , the server should be able to cast
> TIMESTAMP WITH TIME ZONE correctly to TIMESTAMP WITHOUT TIME ZONE as
> described in the SQL standard:
>
> From TIMESTAMP WITH TIME ZONE to TIMESTAMP WITHOUT TIME ZONE
>  TargetValue = SourceValue.UTC + SourceValue.TimeZone

I'd like to see this too, but Tom doesn't like it for
backwards-compatibility reasons.

-O

Re: Timestamp weirdness

From
"Kevin Grittner"
Date:
This explains why my suggestion would not work -- I was aware that
specifying a timezone to a TIMESTAMP WITHOUT TIME ZONE resulted in the
time zone being completely ignored -- I assumed that handling of the
value string for a timestampz within the protocol would follow the same
rules.  This also explains why it works correctly when people convert
the timestamp into a string with the desired time zone and insert that
as a literal in place of the ? within the PreparedStatement.

Thanks for the info.

I assume that the behavior of the server when receiving a timestampz
within the protocol couldn't be changed to match the handling of a
literal without breaking significant existing code.

-Kevin


>>> Oliver Jowett <oliver@opencloud.com> 07/25/05 6:08 PM >>>

If the target type is actually timestamp (TIMESTAMP WITHOUT TIME ZONE),
the server converts the instant identified by the timestamptz value
using the server's TimeZone setting to get a local date/time, and stores
that.

The string -> timestamp conversion
*completely ignores* the supplied timezone, just using the specified
date/time directly

Re: Timestamp weirdness

From
Oliver Jowett
Date:
Kevin Grittner wrote:

> I assume that the behavior of the server when receiving a timestampz
> within the protocol couldn't be changed to match the handling of a
> literal without breaking significant existing code.

(One more time!)

The parsing of timestamptz is just fine, it's the cast to timestamp that
breaks things.

If we get the server to infer a type for the parameter rather than
explicitly specifying it as timestamptz, then we can avoid that cast.
Then things don't break.

-O

Re: Timestamp weirdness

From
"Kevin Grittner"
Date:
This raises two questions for me, as someone trying to become acclimated
to the PostgreSQL development community.

(1)  Is there any long-term goal of making the TIMESTAMP WITH TIME ZONE
more compatible with the standard by storing a time zone with the
timestamp?

(2)  What is the general position of the PostgreSQL community regarding
the sticky questions of whether to improve compliance with standards
versus preserving existing (noncompliant) behavior to avoid breaking
existing PostgreSQL-specific application code?

Coming from years of working with Sybase databases, I have to say that I
appreciate the strategy they use in their JDBC driver.  They provide a
connection property to specify the version of the to which driver you're
coding, and the behavior of existing code never changes (other than
clear bug fixes) for a given value of that property.  If you want the
modified behavior, you change your code to work with it, and change the
property to match.  It's a little clumsy, because there may be several
changes in one new version, and you need to adopt all or none, but it
does help.  A more refined approach would provide properties to control
each individual behavior, and the version number would provide defaults.

-Kevin


>>> Oliver Jowett <oliver@opencloud.com> 07/25/05 8:12 PM >>>
emergency.shower@gmail.com wrote:

> If I TIMESTAMP WITH TIME ZONE is implemented on the server as a
> (timestamp, time zone)-pair

It's not, it's just a timestamp in UTC with no timezone stored.

> , the server should be able to cast
> TIMESTAMP WITH TIME ZONE correctly to TIMESTAMP WITHOUT TIME ZONE as
> described in the SQL standard:
>
> From TIMESTAMP WITH TIME ZONE to TIMESTAMP WITHOUT TIME ZONE
>  TargetValue = SourceValue.UTC + SourceValue.TimeZone

I'd like to see this too, but Tom doesn't like it for
backwards-compatibility reasons.


Re: Timestamp weirdness

From
Dave Cramer
Date:
On 26-Jul-05, at 10:28 AM, Kevin Grittner wrote:

> This raises two questions for me, as someone trying to become
> acclimated
> to the PostgreSQL development community.
>
> (1)  Is there any long-term goal of making the TIMESTAMP WITH TIME
> ZONE
> more compatible with the standard by storing a time zone with the
> timestamp?
This looks like it may happen
>
> (2)  What is the general position of the PostgreSQL community
> regarding
> the sticky questions of whether to improve compliance with standards
> versus preserving existing (noncompliant) behavior to avoid breaking
> existing PostgreSQL-specific application code?
>
Generally we are more SQL compliant than any other database. This is the
first instance I've heard of that we aren't
> Coming from years of working with Sybase databases, I have to say
> that I
> appreciate the strategy they use in their JDBC driver.  They provide a
> connection property to specify the version of the to which driver
> you're
> coding, and the behavior of existing code never changes (other than
> clear bug fixes) for a given value of that property.  If you want the
> modified behavior, you change your code to work with it, and change
> the
> property to match.  It's a little clumsy, because there may be several
> changes in one new version, and you need to adopt all or none, but it
> does help.  A more refined approach would provide properties to
> control
> each individual behavior, and the version number would provide
> defaults.
There are connection parameters to do exactly that.
http://jdbc.postgresql.org/documentation/80/connect.html#connection-
parameters
>
> -Kevin
>
>
>
>>>> Oliver Jowett <oliver@opencloud.com> 07/25/05 8:12 PM >>>
>>>>
> emergency.shower@gmail.com wrote:
>
>
>> If I TIMESTAMP WITH TIME ZONE is implemented on the server as a
>> (timestamp, time zone)-pair
>>
>
> It's not, it's just a timestamp in UTC with no timezone stored.
>
>
>> , the server should be able to cast
>> TIMESTAMP WITH TIME ZONE correctly to TIMESTAMP WITHOUT TIME ZONE as
>> described in the SQL standard:
>>
>> From TIMESTAMP WITH TIME ZONE to TIMESTAMP WITHOUT TIME ZONE
>>  TargetValue = SourceValue.UTC + SourceValue.TimeZone
>>
>
> I'd like to see this too, but Tom doesn't like it for
> backwards-compatibility reasons.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>


Re: Timestamp weirdness

From
"Kevin Grittner"
Date:
Hi Oliver,

You're saying that a timestampz read from the protocol stream on the
server side is always parsed into a TIMESTAMP WITH TIME ZONE.  When it
is assigned to a TIMESTAMP WITHOUT TIME ZONE, there is a cast.  The cast
converts the moment into the local time string for that moment in the
time zone of the database server.  I get that.

My question (not well stated, I'm sure) was whether there is a way to
know, on the server, at the point of handling the timestampz from the
protocol stream, that it is going to be assigned to a TIMESTAMP WITHOUT
TIME ZONE and use literal assignment rules (i.e., ignore the time zone
info) instead of the cast rules.  The next question was whether this
would break existing code.

It sounds like you're well along on another solution, so this becomes
acedemic.  I'm not sure how you're inferring a type with your approach,
but I guess I was thinking that it might be possible to infer
alternative types for a timestampz.

I'm new to PostgreSQL, so I appreciate the patience of those who are
intimately familiar with the code.  It's takes quite a while to scan
through source code and infer it all.  I'm hoping to gain a firm enough
understanding to contribute to the code without too many missteps.

-Kevin


>>> Oliver Jowett <oliver@opencloud.com> 07/26/05 9:20 AM >>>
Kevin Grittner wrote:

> I assume that the behavior of the server when receiving a timestampz
> within the protocol couldn't be changed to match the handling of a
> literal without breaking significant existing code.

(One more time!)

The parsing of timestamptz is just fine, it's the cast to timestamp that
breaks things.

If we get the server to infer a type for the parameter rather than
explicitly specifying it as timestamptz, then we can avoid that cast.
Then things don't break.

-O


Re: Timestamp weirdness

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> You're saying that a timestampz read from the protocol stream on the
> server side is always parsed into a TIMESTAMP WITH TIME ZONE.  When it
> is assigned to a TIMESTAMP WITHOUT TIME ZONE, there is a cast.

No, he's not saying that.  He's saying that *if* the server initially
converts to timestamptz and then timestamp, there's a problem.  It won't
ordinarily do that unless told to ... but sending the parameter
explicitly typed as timestamptz tells it to.  So part of the problem
here is that the driver is sending all Timestamp parameters explicitly
typed as timestamptz, which it does because it hasn't got any very good
basis for knowing whether the value is going to be assigned to a
timestamptz or plain timestamp column.

            regards, tom lane

Re: Timestamp weirdness

From
Dave Cramer
Date:
On 26-Jul-05, at 1:51 PM, Kevin Grittner wrote:

> Hi Oliver,
>
> You're saying that a timestampz read from the protocol stream on the
> server side is always parsed into a TIMESTAMP WITH TIME ZONE.  When it
> is assigned to a TIMESTAMP WITHOUT TIME ZONE, there is a cast.  The
> cast
> converts the moment into the local time string for that moment in the
> time zone of the database server.  I get that.
>
> My question (not well stated, I'm sure) was whether there is a way to
> know, on the server, at the point of handling the timestampz from the
> protocol stream, that it is going to be assigned to a TIMESTAMP
> WITHOUT
> TIME ZONE and use literal assignment rules (i.e., ignore the time zone
> info) instead of the cast rules.  The next question was whether this
> would break existing code.
Sort of.... postgres has automatic casting rules, if you tell it that
it is going to be a timestamptz and
it is being assigned to a timestamp then there is a cast function
which is invoked, and vice versa
>
> It sounds like you're well along on another solution, so this becomes
> acedemic.  I'm not sure how you're inferring a type with your
> approach,
> but I guess I was thinking that it might be possible to infer
> alternative types for a timestampz.
He is telling the server that he doesn't know what the type is
(INVALID) and the server is going
to cast it appropriately. Much like you do if you do "insert into foo
values('2005-01-01 03:15:34')


>
> I'm new to PostgreSQL, so I appreciate the patience of those who are
> intimately familiar with the code.  It's takes quite a while to scan
> through source code and infer it all.  I'm hoping to gain a firm
> enough
> understanding to contribute to the code without too many missteps.
>
> -Kevin
>
>
>
>>>> Oliver Jowett <oliver@opencloud.com> 07/26/05 9:20 AM >>>
>>>>
> Kevin Grittner wrote:
>
>
>> I assume that the behavior of the server when receiving a timestampz
>> within the protocol couldn't be changed to match the handling of a
>> literal without breaking significant existing code.
>>
>
> (One more time!)
>
> The parsing of timestamptz is just fine, it's the cast to timestamp
> that
> breaks things.
>
> If we get the server to infer a type for the parameter rather than
> explicitly specifying it as timestamptz, then we can avoid that cast.
> Then things don't break.
>
> -O
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>


Configuring Connections

From
Christian Cryder
Date:
On 7/26/05, Dave Cramer <pg@fastcrypt.com> wrote:
> There are connection parameters to do exactly that.
> http://jdbc.postgresql.org/documentation/80/connect.html#connection-
> parameters

Can someone explain to me how to configure a connection like this when
using DataSource to get the connection?

Thanks...
Christian

Re: Timestamp weirdness

From
Oliver Jowett
Date:
Kevin Grittner wrote:

> My question (not well stated, I'm sure) was whether there is a way to
> know, on the server, at the point of handling the timestampz from the
> protocol stream, that it is going to be assigned to a TIMESTAMP WITHOUT
> TIME ZONE and use literal assignment rules (i.e., ignore the time zone
> info) instead of the cast rules.  The next question was whether this
> would break existing code.

As I said:

>>>>Oliver Jowett <oliver@opencloud.com> 07/26/05 9:20 AM >>>

> If we get the server to infer a type for the parameter rather than
> explicitly specifying it as timestamptz, then we can avoid that cast.
> Then things don't break.

-O

Re: Configuring Connections

From
Oliver Jowett
Date:
Christian Cryder wrote:
> On 7/26/05, Dave Cramer <pg@fastcrypt.com> wrote:
>
>>There are connection parameters to do exactly that.
>>http://jdbc.postgresql.org/documentation/80/connect.html#connection-
>>parameters
>
>
> Can someone explain to me how to configure a connection like this when
> using DataSource to get the connection?

Currently the DataSource properties are somewhat out of sync with the
full range of URL parameters. Patches welcome -- it's somewhere on my
todo list to get it all back in sync (and perhaps expose a setURL()
property) but the timestamp work has basically sucked up all the time I
have to spend on the driver for the next month or more.

-O