Re: Make time/timestamp tests fair for binary transfers

On Tue, 2007-07-24 at 10:30 +1200, Oliver Jowett wrote:
> Mikko Tiihonen wrote:
>
> > The patch also alters what kind of java.sql.Time values are returned.
> > Before the patch the millisecond value of a ResultSet.getTime could be
> > between -24h<x<24h. The patch changes the returned value to be always
> > between 0h<=x<24h. If someone thinks this change is not acceptable I'll
> > go back and revert it re-fix the test cases to match.
>
> I'm not sure if this is right. In theory Time values are meant to have a
> day/month/year of Jan 1 1970. Time (via Date) uses the local timezone to
> compute the day/month/year. But to represent all time values in all
> local timezones that means you need negative millisecond values (to
> handle timezones ahead of UTC). For example, Jan 1 1970 02:00 +1200 is
> actually Dec 31 1969 14:00 UTC, which is a negative milliseconds value.

Yes. But the Time part is 14:00, and if you store the above to postgres
column with TIME type it will not print out nor return any negative
values.
The negative time is purely a Java problem.

> There's an additional complication that if you're dealing with time
> values in different timezones, it'd imply that 2am +1200 is identical to
> 2pm UTC, which is not entirely true.. they're on different days.
> Currently they'd return Time values that are exactly one day apart, with
> this change they'd return identical Time values.

Yes, but one would want to know both the day and the time of an event
then
a timestamp would have been a better choice.

> As usual, the javadoc is fairly hopeless at telling you how Time is
> actually supposed to behave!
> (I wonder what java.sql.Time.valueOf() returns in timezones ahead of
> UTC? Might be worth testing..)
... next post
> Time.valueOf("00:00:00").getTime() returns -43200000 in a +1200
> timezone, so I'd be happier keeping negative values to be consistent
> with that.

OK, lets keep using old way. It is most likely that the current
functionality is the one to which application writers have pushed all
JDBC driver implementations to converge to thus creating an undocumented
standard. Although everywhere I have seen the design guidelines for
portable JDBC code, the guideline is to store times to database either
in UTC milliseconds or as fully formatted strings with time zone. Thus I
believe that most applications have not exercised the corner cases
caused by varying time zones.

I'll try again if I can get that to work with the binary transfers and
resend the patch.

-Mikko