CODEWITHSUNDEEP
javapostgresqljdbctimezonetimestamp
Emilio
Emilio

Reputation: 51

JDBC inserting now() different to the server timestamp

For this PostgreSQL test table:

CREATE TABLE public.test (
  id BIGSERIAL,
  "datetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  value INTEGER,
  CONSTRAINT test_pkey PRIMARY KEY(id)
)

I am using this SQL insert:

INSERT INTO it.test (value) VALUES (1);

No problem when I execute the INSERT from a remote client like EMS or PgAdmin. The field datetime is populated with the server timestamp exactly, but when I execute the INSERT from a Java program using Postgres/JDBC Driver (postgresql-9.2-1002.jdbc4.jar), the field datetime is populated with a timestamp different to the server.

I read that the JVM use the local PC time zone. There is any way to avoid this?

If I change the datatype from timestamp to timestampz, java populate the field with the server timestamp, but I want solve it without change the datatype, because the table is used by another programs and reports.

Upvotes: 5

Views: 2286

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324621

The issue here is that PgJDBC sets the TimeZone variable to the JVM timezone when it connects. It requires this for its timestamp code to operate correctly and (IIRC) to correctly comply with the JDBC specification. (See createPostgresTimeZone in org/postgresql/core/v3/ConnectionFactoryImpl.java for details).

now() is an alias for current_timestamp, which returns a timestamp with time zone.

A cast from timestamp with time zone to timestamp without time zone is the same as writing current_timestamp at time zone [the current server TimeZone], i.e. it re-interprets the timestamp as being in the new time zone.

Since the TimeZone is different when you're connected via the other clients, you get different results. So your DDL would also be wrong for clients that set TimeZone to their local time.

To make this consistent, make the column timestamp with time zone (generally preferable), or redefine the default as:

"datetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() AT TIME ZONE 'CEST',

... or whatever the server time zone is. If doing this, be careful! There are three ways to specify time zones, and you must make sure you use the same one as in the server configuration, otherwise it'll have different daylights savings time handling!

Needless to say, I strongly recommend just using UTC:

"datetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT current_timestamp AT TIME ZONE 'UTC',

or preferably a timestamp with time zone:

"datetime" TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,

Upvotes: 6

Related Questions