Problems with timestamp with time zone and old dates?

From: Michael Clark <codingninja(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Problems with timestamp with time zone and old dates?
Date: 2012-08-21 21:29:14
Message-ID: CACAT_AdOT50a4HvidEzFTGNE_i6U4Wj=2i+jT6qtD8W2N0vwGg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I have a weird situation I am trying to work through, and could use some
help if anyone can provide some.

I have a table with a column to store timestamp with time zone, and when I
store an older take (before 1895 or so), the value I get back from PG when
doing a select seems odd and is causing my client some issues with the
format string.

For example, if I insert like so:
INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');

I get the following when I select:
SELECT startdate FROM sometable;
startdate
------------------------------
1750-08-21 15:59:28-05:17:32
(1 row)

It's the odd offset that is causing my client problems, and I was wondering
if this is expected?
(I am using PG9.1.3)

This contrasts:
INSERT INTO sometable (startdate) values ('2012-08-21 21:17:00+00:00');

I get the following when I select:
startdate
------------------------
2012-08-21 17:17:00-04
(1 row)

Can anyone shed some light on if this is expected, or if I am doing
something odd?

Much appreciated!
Michael

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Crawford 2012-08-21 21:50:59 Re: Problems with timestamp with time zone and old dates?
Previous Message Darren Duncan 2012-08-21 21:17:50 Re: redundant fields in table for "performance optimizations"