Re: [GENERAL] Problems with timestamp with time zone and old dates?
HI Scott.
Thanks a lot for the feedback.
I ended up setting the client time zone to GMT on my connections, and that
has fixed the problem for us.
It's actually an awesome solution, we can now expect all timestamps to be
returned in a consistent fashion.
Thanks for prodding me on that and sending me down that road!
Michael.
On Wed, Aug 22, 2012 at 12:19 AM, Scott Marlowe scott.marl...@gmail.comwrote:
On Tue, Aug 21, 2012 at 10:12 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Tue, Aug 21, 2012 at 10:08 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark codingni...@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.
Re: [GENERAL] Problems with timestamp with time zone and old dates?
On Wed, Aug 22, 2012 at 5:00 AM, hubert depesz lubaczewski
dep...@depesz.com wrote:
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.
Ah, I see. That makes sense. Thanks for the info! It's appreciated!
Michael.
Re: [GENERAL] Problems with timestamp with time zone and old dates?
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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with timestamp with time zone and old dates?
Hello.
Thanks for the response.
The value being returned from PG, with the odd offset is expected?
Thanks again,
Michael.
On Tue, Aug 21, 2012 at 5:50 PM, Steve Crawford
scrawf...@pinpointresearch.com wrote:
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_databasehttp://en.wikipedia.org/wiki/Tz_database
.
N.B the offset prior to November 18, 1883.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with timestamp with time zone and old dates?
On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark codingni...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with timestamp with time zone and old dates?
On Tue, Aug 21, 2012 at 10:08 PM, Scott Marlowe scott.marl...@gmail.com wrote:
On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark codingni...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with timestamp with time zone and old dates?
On Tue, Aug 21, 2012 at 10:12 PM, Scott Marlowe scott.marl...@gmail.com wrote:
On Tue, Aug 21, 2012 at 10:08 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Tue, Aug 21, 2012 at 3:29 PM, Michael Clark codingni...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
