Re: Timestamp confusion - Mailing list pgsql-jdbc

Thank you Oliver for the followup.  I did search the archives and read
the group FAQ and even STFW before I posted, but obviously I have
missed much.

On Feb 12, 4:40 pm, oli...@opencloud.com (Oliver Jowett) wrote:
> 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
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc