3

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)

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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?

Improve this answer

5 Comments

I updated the question to reflect correct Postgres version: 9.2.4
How do I check the timezone spec in Postgres? Is there a specific command?
9.2.4 has tzinfo version 2013b (taken from the release notes). I have 2013c installed on Debian, but the differences between 2013b and 2013c don't seem to cover this.
At least on Debian, I think postgresql is using the system's timezone data rather than having its own redundantly packaged.
I think the problem is on Java side. My test harness is report offset of -04 for 2013-11-14 which is obviously wrong. Investigating further ... edit .. user error. timezone on java side is correct (-05).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.