Thread: Timestamp weirdness
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
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
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
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
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 )
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
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.
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 > >
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
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
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
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
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
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
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.
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 > >
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
"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
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
>
>
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
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
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