Thread: How to make PostgreSQL JDBC drive get PGTZ?

My system time zone (TZ) is set to US Eastern Standard Time is -5 hours
of
GMT time

I set
export PGTZ=GMT
then go to psql do

insert into a_table (a_column) values ('2001-02-08 18:30:00+00');
select a_column from a_table;

will get result exactly what I inserted
2001-02-08 18:30:00+00

But when I used JDBC drive from org.postgresql.Driver to insert the same
value,
I got

2001-02-08 23:30:00+00

This is the value of GMT time at 18:30 of EST time! Which tell me
the Postgres JDBC drive insert the value in EST time. Why is that?

I already did
Properties p = new Properties();
p.put("PGTZ", "GMT");
before connect to PostgreSQL server, but no luck!

Can someone out there tell me how to make PostgreSQL JDBC drive
get PGTZ environment variable effect?

Thank you very much in advance!

--Raymond

What version of the database and jdbc driver are you using?  This should
be fixed and work correctly in current sources.

thanks,
--Barry

Raymond Chui wrote:

> My system time zone (TZ) is set to US Eastern Standard Time is -5 hours
> of
> GMT time
>
> I set
> export PGTZ=GMT
> then go to psql do
>
> insert into a_table (a_column) values ('2001-02-08 18:30:00+00');
> select a_column from a_table;
>
> will get result exactly what I inserted
> 2001-02-08 18:30:00+00
>
> But when I used JDBC drive from org.postgresql.Driver to insert the same
> value,
> I got
>
> 2001-02-08 23:30:00+00
>
> This is the value of GMT time at 18:30 of EST time! Which tell me
> the Postgres JDBC drive insert the value in EST time. Why is that?
>
> I already did
> Properties p = new Properties();
> p.put("PGTZ", "GMT");
> before connect to PostgreSQL server, but no luck!
>
> Can someone out there tell me how to make PostgreSQL JDBC drive
> get PGTZ environment variable effect?
>
> Thank you very much in advance!
>
> --Raymond

Quoting Raymond Chui <raymond.chui@noaa.gov>:

>
> My system time zone (TZ) is set to US Eastern Standard Time is -5 hours
> of
> GMT time
>
> I set
> export PGTZ=GMT
> then go to psql do
>
> insert into a_table (a_column) values ('2001-02-08 18:30:00+00');
> select a_column from a_table;
>
> will get result exactly what I inserted
> 2001-02-08 18:30:00+00
>
> But when I used JDBC drive from org.postgresql.Driver to insert the
> same
> value,
> I got
>
> 2001-02-08 23:30:00+00
>
> This is the value of GMT time at 18:30 of EST time! Which tell me
> the Postgres JDBC drive insert the value in EST time. Why is that?

JDBC (& Java in general) works on a standard timezone so your local date is
converted into GMT (or rather UTC) when it's stored, so what you should use is
getDate() and then use a Calendar object (eg GregorianCalendar) to convert to
your local timezone.

7.1 has the extra methods that can pass a Calendar object implemented (not
fully tested yet, but will be in CVS by the weekend).

There are still a few date/time problems. I'm due in the next few evenings to
start writing the TestCases for Date/Time/Timestamp's so when they are done,
we'll have a firm base to work on (rather than guess work used so far).

>
> I already did
> Properties p = new Properties();
> p.put("PGTZ", "GMT");
> before connect to PostgreSQL server, but no luck!

The JDBC driver only accepts a few properties, and PGTZ isn't one of them, and
never will because Java uses it's own time zone handling.

I'd advise you read up on Calendar to see how timezones are handled.

Peter



--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

I solved this problem by execute a SQL statement below after JDBC
connection.

SET TIME ZONE 'GMT';

--Raymond