Re: Having a hard time understanding time zone

Okay, I've got this narrowed down and it seems crazy to me.

=> show time zone;
TimeZone
----------
UTC
(1 row)

=> CREATE TABLE test (id INTEGER, ts TIMESTAMP DEFAULT
CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);
CREAT TABLE

=> insert into test(id) values(1);
INSERT 0 1
warm-headland-9732::OLIVE=> select * from test;
id | ts | tswz
----+----------------------------+-------------------------------
1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00
(1 row)

This is all as I would expect it to be. But if I run this same insert from
my JVM I get this!

=> select * from test;
id | ts | tswz
----+----------------------------+-------------------------------
1 | 2014-04-07 18:29:30.990622 | 2014-04-07 18:29:30.990622+00
1 | 2014-04-07 11:32:28.692483 | 2014-04-07 18:32:28.692483+00
(2 rows)

Note that the ts defaults to a PDT value while the tswz defaults to the
proper UTC value. So I'm stumped. What is special about Java that causes
CURRENT_TIMESTAMP to evaluate differently for a TIMESTAMP WITHOUT TIME ZONE
field? I'm not even specifying the "ts" field from Java, I am just letting
it default? Any ideas? The same query from PSQL gives different results
than from my JVM for default values. Here is the Java code so you can
recreate the test.

@Test
public void testTimestamp() throws Exception {

try ( Connection con = dataSource.getConnection() ) {
try ( Statement st = con.createStatement() ) {
st.execute( "insert into test(id) values(1)");
}

con.commit();
}
}

Note that the Java insert does not specify "ts" or "tswz" so there is no
time transformations going on.

R.

On Sun, Apr 6, 2014 at 1:45 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> Robert,
>
> The driver certainly doesn't do anything to a statement like
>
> insert into foo (datecol) values (now())
>
> it does use the calendar of the vm to get the string representation.
>
> Time, and date are two things not very well handled in java or JDBC. What
> is the exact column type and what does it store in the database
>
> But to answer your question you can do:
>
> stmt.setTimestamp(1, t, Calendar.getInstance( TimeZone.getTimeZone("UTC")))
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On 6 April 2014 13:56, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
>
>> Does the JDBC driver set the timezone to the origin timezone for each
>> statement?
>>
>> I have a date column in a table. My Postgres server is running in UTC. My
>> java app is running in "America/Los_Angeles".
>>
>> I would expect a DEFAULT column of NOW() to insert the current UTC time.
>> While if I specify the time with new Date() from Java I would expect the
>> Java timezone.
>>
>> But oddly both set the date field to the localized time in Java and not
>> the UTC time.
>>
>> This makes me think that the driver is somehow forcing the session
>> timezone.
>>
>> If so is there any way to make the driver communicate with the server in
>> UTC?
>>
>> Thanks!
>>
>
>