Data corruption in PostgreSQL timestamp field

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
);

      

Then I inserted some data into a data table like this -

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

      

And if I check the DB for 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 to insert some data with a 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)

      

Take a look at the above result. Note that the Timezone value has changed from +05: 30 to +06: 30

It actually gets worse when I insert any date that was 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 time zone value is completely garbled and the date cannot be parsed.

I would appreciate any help with this.

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

Update: I tried to enter data by specifying 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)

      

+1


a source to share


3 answers


What country are you in? PostgreSQL probably assumes the dates are for your current locale and apply the appropriate timezone and DST rules, which is wrong if the dates and times are (for example) UTC.

Do you really need time zone functionality? A timestamp without time zone

demonstrates cleaner behavior as it doesn't need to implement strange rules. But if you want a timezone, then you definitely want to fix that, not kludge it.

The best solution is to just specify the timezone: '04:05:06-08:00'

for GMT-08: 00 or possibly '04:05:06z'

for GMT / UTC / "Zulu" (hence the "z").



Edit: Most of the real weirdness comes from the Asia / Kolkata timezone. 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 are not describing what you expect from the behavior, so it is difficult to tell where you want to go from here.

+3


a source


This looks like a problem with a summer day. (From what I get the timezone is UTC + 05.30, DST is set around March / April and one hour is added).

Have you tried inserting the same date and just changing the year to eliminate this possibility?



For this latter, strange. I was unable to reproduce it, but it may be due to the half hour zones. Same if you change the TZ environment variable?

+1


a source


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

  • One of the columns is of type TimeStamp with TimeZone. The time value of this column changes with the change in the system time zone. Can we avoid this?
  • After each lookup from the table, half an hour gets to the thid for each tuple.

I was unable to trace the origin of this issue.


Rgds Nithin

0


a source







All Articles