Re: Timestamp confusion
От | Oliver Jowett |
---|---|
Тема | Re: Timestamp confusion |
Дата | |
Msg-id | 4B75CAB1.3030201@opencloud.com обсуждение исходный текст |
Ответ на | Timestamp confusion (Richard Rowell <richard.rowell@gmail.com>) |
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: