Re: Timestamp confusion

Richard Rowell wrote:
> I'm perplexed by the behaviour of ResultSet.getTimestamp() when passed
> a calendar.

I ran your testcase and it seems to be working fine. Here's my output:

>
> Default TZ: New Zealand Standard Time
> Application time: 2010-02-13 10:15:31.327
>
> Default Timezone:
> Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327)
> Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327)
>
> PST Timezone:
> Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327)
> Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327)
>
> PG Timezone adjust to PST8PDT, Default Calendar:
> Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327)
> Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327)
>
> PG Timezone adjust to PST8PDT, PST Calendar
> Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327)
> Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327)

> CREATE TABLE dtest (
> uid integer NOT NULL,
> d1 timestamp with time zone,
> d2 timestamp without time zone
> );

I made uid a SERIAL so your test code actually worked, and turned on
autocommit so I could look at the results after the fact:

testdb=# select * from dtest;
uid | d1 | d2
-----+----------------------------+-------------------------
1 | 2010-02-13 10:15:31.327+13 | 2010-02-13 10:15:31.327
2 | 2010-02-13 10:15:31.327+13 | 2010-02-12 13:15:31.327
(2 rows)

d1 identifies a particular instant in time, and you are always
formatting it with the default calendar (because you're just using
Timestamp.toString()), so it always ends up as exactly the same value
when displayed, regardless of what Calendar is passed.

d2 identifies a particular *clock time*, which is a different instant in
time depending on what timezone you interpret it in. The server timezone
is entirely irrelevant. The Calendar you pass determines what timezone
it is interpreted in.

So row 1's d2 is the clock time in the default timezone when the test
was run.
Row 2's d2 is the clock time in PST when the test was run.

As you can see from the raw data shown by psql, row 2 is 21 hours behind
row 1. That's right, because PST is 21 hours behind NZ.

Then all the output for d2 just follows from that. When you pass the
default calendar, you get Timestamps that are correct for the default
calendar. Timestamp.toString() uses the default calendar, so those cases
just reflect the values in the table directly.

When you pass a PST Calendar when retrieving d2 from row 1, what you're
saying is "give me a Timestamp for the instant in time that is
2010-02-13 10:15:31.327 in PST". This is not the same instant in time as
when the test is run - it is 21 hours *later* than the test ran, i.e.
2010-02-14 07:15:31.327 in the NZ timezone.

Then you print that value in the *default timezone*, which is
essentially saying "what time is 2010-02-13 10:15:31.327 PST in the NZ
timezone?". And it displays that correctly as 2010-02-14 07:15:31.327.

The same sort of argument applies to row 2.

Usually, you want to format timestamps using the same Calendar you used
to retrieve them. You're not doing that in your test code - Timestamp
stores no timezone information, and Timestamp.toString() always uses the
default JVM timezone.

I've written this explanation, or a similar one, many times in the past.
You probably want to search the list archives.

-O