CODEWITHSUNDEEP
javapostgresqldatetime
Καrτhικ
Καrτhικ

Reputation: 3915

One hour discrepancy in UTC offset between Postgresql and Java

I have a java program that generates timezone values in the binary format expected by Postgresql's COPY command in binary format. The data is written to a binary file which I then insert into Postgresql using the copy command into a table with a timestamp column (no time-zone).

The format is essentially the number 8 as a 4-byte value followed by microseconds since 2000-01-01 as an 8-byte value.

I am finding a difference in Postgresql and Java's interpretation of the timezone offset. When I attempt to write the date:

2004-11-01 09:34:42.432

which in the postgres binary format is 

0x00, 0x00, 0x00, 0x08, 0x00, 0x00, 0x8a, 0xcd, 0xe3, 0x10, 0x68, 0x00

and postgres reports the date correctly. However, if I enter the date

2010-11-01 09:34:42.432

which in binary goes as 

0x00, 0x00, 0x00, 0x08, 0x00, 0x01, 0x36, 0xf8, 0x72, 0xcb, 0x64, 0x00

I get 2010-11-01 08:34:42.432 as the timestamp in Postgres.

Further investigation shows that Postgres thinks that the UTC offset is -5 for 2010 while Java thinks its -4 (which is correct I believe) leading to the one hour difference. Anyone know what the solution to this is?

Pertinent information:

Postgresql version 9.2.4

Java: 7

Working in Eastern Time Zone (America/New_York).

OS: Linux (timezone set correctly)

Upvotes: 3

Views: 594

Answers (2)

Καrτhικ
Καrτhικ

Reputation: 3915

Figured it out. Turns out the microseconds that Postgres expects for a timestamp column is the microseconds since Jan 1 2000 UTC. In other words, one needs to subtract current time from Jan 1 2000 UTC not current time from Jan 1 2000 local. I was doing the latter.

Additional information to help others:

  1. If your column is timestamp without time zone: Send microseconds from current time - Jan 1 2000 UTC + tz offset in microseconds
  2. If your column is timestamp with time zone: Send microseconds from current time - Jan 1 2000 UTC.

(1) effectively becomes current time "as-is" in UTC (i.e., if you are trying 9:00 am local time, don't make it 12:00 pm UTC if offset is -3, instead encode it as 9:00 am UTC) minus Jan 1 2000 UTC. The important fact is that this is NOT the same as (current time in local time zone) - (Jan 1 2000 in local time zone).

Gratuitous plug I have a Java library for directly inserting rows into Postgresql using the COPY command from a collection of JPA annotated entities. Available at github: https://github.com/eclecticlogic/pedal-dialect

Upvotes: 1

araqnid
araqnid

Reputation: 133482

A quick test from the Linux commandline suggests that 2010-11-01 in America/New_York was EDT (-4). Do you have the same timezone spec in both Postgresql and Java? Which Java release are you using -- they use the same TZif database, but might be on diifferent versions. Do you get the same result if you test with Postgresql 9.2.4 or later?

Upvotes: 0

Related Questions