Thread: Problems with timestamp with time zone and old dates?

On 08/21/2012 02:29 PM, Michael Clark wrote:
> 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
>
PostgreSQL derives its timezone rules from the Olson database:
http://en.wikipedia.org/wiki/Tz_database.

N.B the offset prior to November 18, 1883.

Cheers,
Steve

On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark <codingninja@gmail.com> wrote:
> For example, if I insert like so:
> INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');

What's the reason for you inserting with an offest instead of letting
the client timezone set it for you?  Just wondering.

On Tue, Aug 21, 2012 at 10:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark <codingninja@gmail.com> wrote:
>> For example, if I insert like so:
>> INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');
>
> What's the reason for you inserting with an offest instead of letting
> the client timezone set it for you?  Just wondering.

Note that if you just want to get out what you're putting in (GMT) you
can do this:

select startdate at time zone 'GMT' from sometable ;
      timezone
---------------------
 1750-08-21 21:17:00

On Tue, Aug 21, 2012 at 10:12 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Aug 21, 2012 at 10:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark <codingninja@gmail.com> wrote:
>>> For example, if I insert like so:
>>> INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');
>>
>> What's the reason for you inserting with an offest instead of letting
>> the client timezone set it for you?  Just wondering.
>
> Note that if you just want to get out what you're putting in (GMT) you
> can do this:
>
> select startdate at time zone 'GMT' from sometable ;
>       timezone
> ---------------------
>  1750-08-21 21:17:00

Or you could just use plain timezones, not timezones with timestamp.

On Tue, Aug 21, 2012 at 05:29:14PM -0400, Michael Clark wrote:
> 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 impossible to tell without knowing what is your time zone, but
I don't see anything particularly strange about it. Non-integer offsets
do happen, and in the past there were more commonly used.

Based on what I can see in timezone data, it looks that time zone
America/Toronto
used this offset until 1895.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/