Data corruption in PostgreSQL timestamp field

Question

I have a PostgreSQL table with the following schema -

CREATE TABLE test (
  id serial NOT NULL PRIMARY KEY,
  username varchar(100) NOT NULL, -- The user name
  dob timestamp with time zone NOT NULL -- The date of birth
);

I then inserted some data into the table with data like this -

INSERT INTO "test" ("username", "dob") VALUES (E'Scotty', E'2009-05-14 15:44:43');

And if I check the DB for the data, I get this -

mydb=> select username, dob from test where username='Scotty';
 username |            dob            
----------+---------------------------
 Scotty   | 2009-05-14 15:44:43+05:30
(1 row)

Everything is fine and dandy until I try inserting some data with the date before 1946 -

INSERT INTO "test" ("username", "dob") VALUES (E'James T Kirk', E'1945-01-01 11:30:11');

mydb=> select username, dob from test where username='James T Kirk';
      username |            dob            
-------------- +---------------------------
 James T Kirk  | 1945-01-01 11:30:11+06:30
(1 row)

Look at the above result. Notice how the Timezone value has changed from +05:30 to +06:30

It actually gets worse when I insert any date which is before 1942 -

INSERT INTO "test" ("username", "dob") VALUES (E'Spock', E'1941-01-01 11:30:11');

mydb=> select username, dob from test where username='Spock';
 username |             dob              
----------+------------------------------
 Spock    | 1941-01-01 11:30:11+05:53:20
(1 row)

Now the Timezone value has got completely mangled and the date can't be parsed.

I would appreciate any help with this.

My Timezone is Asia/Kolkata (GMT+05:30).

Update: I tried entering the data by specifying the TZ explicitly like this -

INSERT INTO "test" ("username", "dob") VALUES (E'McCoy', E'1941-01-25 00:20:30+05:30');

Even then it didn't work.

mydb=> select username, dob from test where username='McCoy';
 username |             dob              
----------+------------------------------
 McCoy    | 1941-01-25 00:43:50+05:53:20
(1 row)

Answer 1

This looks like a Daylight Saving issue. (From what I get, the timezone is UTC+05.30, DST sets in around March/April, and adds one hour).

Have you tried with inserting the same date and just changing the year to rule out that possibility?

For that last one, It's strange. I have not managed to reproduce it, but it could be because of the half hour zones. Is it the same if you change the TZ environment variable?

Answer 2

What locale are you in? Probably PostgreSQL is assuming that the dates are for your current locale, and applying appropriate time zone and DST rules, which isn't the right thing to do if the dates and times are (say) UTC.

Do you really need the timezone functionality? A timestamp without time zone will exhibit saner behavior, since it doesn't have to implement weird rules. But if you need the time zone, then you definitely want to fix this rather than kludge it.

The best fix is just to specify the timezone explicitly: '04:05:06-08:00' for GMT–08:00, or perhaps '04:05:06z' for GMT/UTC/"Zulu" (hence the 'z').

Edit: Most of the real weirdness is coming from the Asia/Kolkata time zone. From tzdata2009g:

# India
# Zone  NAME  GMTOFF RULES FORMAT [UNTIL]
Zone    Asia/Kolkata 5:53:28 - LMT 1880 # Kolkata
      5:53:20 - HMT 1941 Oct    # Howrah Mean Time?
      6:30 - BURT 1942 May 15 # Burma Time
      5:30 - IST 1942 Sep
      5:30 1:00 IST 1945 Oct 15
      5:30 - IST

You don't describe what you expect the behavior to be, so it's hard to say where you want to go from here.

Answer 3

I have one similar but different problem. We have PostgreSQL8.0 installed on WinXP.

1. One of the columns is of type TimeStamp with TimeZone. The Time value of this column changes with the change in system time zone. Can we avoid this?
2. After every retrieval from table, half hour is getting to thid column for every tuple.

I haven'nt been able to trace origin to this problem.

---

rgds nithin