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)

Καrτhικ
  • 3,833
  • 2
  • 29
  • 42

2 Answers2

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

Καrτhικ
  • 3,833
  • 2
  • 29
  • 42
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?

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • I updated the question to reflect correct Postgres version: 9.2.4 – Καrτhικ Nov 14 '13 at 14:13
  • How do I check the timezone spec in Postgres? Is there a specific command? – Καrτhικ Nov 14 '13 at 14:14
  • 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. – araqnid Nov 14 '13 at 14:18
  • At least on Debian, I think postgresql is using the system's timezone data rather than having its own redundantly packaged. – araqnid Nov 14 '13 at 14:24
  • 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). – Καrτhικ Nov 14 '13 at 14:27