Re: Timestamp Conversion Woes Redux - Mailing list pgsql-jdbc
From | Oliver Jowett |
---|---|
Subject | Re: Timestamp Conversion Woes Redux |
Date | |
Msg-id | 42E0529E.7060807@opencloud.com Whole thread Raw |
In response to | Re: Timestamp Conversion Woes Redux (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Timestamp Conversion Woes Redux
Re: Timestamp Conversion Woes Redux |
List | pgsql-jdbc |
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
pgsql-jdbc by date: