Re: [BUGS] BUG #5325: Timestamp w/ timezone + interval not functioning correctly
"Eric Vollnogel" writes:
> The output in this example is incorrect.
Well, that's debatable. As you say, the result of the subtraction is
interval
--
44 days 23:00:00
(1 row)
If we add 44 days to timestamp1, we get
select cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' + interval '44 days';
?column?
2010-04-14 00:00:00-04
(1 row)
and if we then add another 23 hours to that, we get
select cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' + interval '44 days' + interval '23 hours';
?column?
2010-04-14 23:00:00-04
(1 row)
both of which are reasonable answers.
There has been some discussion of changing timestamp subtraction so that
it doesn't reduce the interval to days, but just produces '1079 hours'
in this example. If it did that then you'd get the result you were
expecting. Unfortunately, it would also break a whole lot of other
cases. So far the decision has been to leave it alone.
In the meantime, if you would like that behavior you can get it using
arithmetic on the epoch equivalents, ie
(extract(epoch from timestamp1) - extract(epoch from timestamp2))
* interval '1 second'
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5325: Timestamp w/ timezone + interval not functioning correctly
The following bug has been logged online:
Bug reference: 5325
Logged by: Eric Vollnogel
Email address: edvollno...@dstsystems.com
PostgreSQL version: 8.4.2
Operating system: Windows XP
Description:Timestamp w/ timezone + interval not functioning
correctly
Details:
I have encountered a problem in which adding a timestamp with timezone to a
duration is resulting in an incorrect timestamp with timezone depending on
if the duration causes the sum to cross a daylight savings boundary.
For example, the following query shows a calculation without crossing a
daylight savings boundary:
SELECT cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME
ZONE 'US/Eastern' AS timestamp1, cast('2010-03-02 00:00:00' AS timestamp
without time zone) AT TIME ZONE 'US/Eastern' AS timestamp2, cast('2010-03-02
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' -
cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' as interval, (cast('2010-03-02 00:00:00' AS timestamp without
time zone) AT TIME ZONE 'US/Eastern' - cast('2010-03-01 00:00:00' AS
timestamp without time zone) AT TIME ZONE 'US/Eastern') + (cast('2010-03-01
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern') as
shouldbetimestamp1
Results in the following output:
timestamp1, timestamp2, interval, shouldbetimestamp1
"2010-02-28 23:00:00-06";"2010-03-01 23:00:00-06";"1 day";"2010-03-01
23:00:00-06"
This output is correct. The output shows timestamp1, timestamp2,
timestamp2-timestamp1, and (timestamp2-timestamp1)+timestamp1. timestamp2
should always be equal to (timestamp2-timestamp1)+timestamp1.
For the next example, we cross the daylight savings time boundary:
SELECT cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME
ZONE 'US/Eastern' AS timestamp1, cast('2010-04-15 00:00:00' AS timestamp
without time zone) AT TIME ZONE 'US/Eastern' AS timestamp2, cast('2010-04-15
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' -
cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' as interval, (cast('2010-04-15 00:00:00' AS timestamp without
time zone) AT TIME ZONE 'US/Eastern' - cast('2010-03-01 00:00:00' AS
timestamp without time zone) AT TIME ZONE 'US/Eastern') + (cast('2010-03-01
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern') as
shouldbetimestamp1
This results in the following output:
timestamp1, timestamp2, interval, shouldbetimestamp1
"2010-02-28 23:00:00-06";"2010-04-14 23:00:00-05";"44 days
23:00:00";"2010-04-14 22:00:00-05"
The output in this example is incorrect. Because 1 hour is lost when
daylight savings takes effect, the interval is correct: 45 days - 1 hour.
(44 days 23:00:00). However the last computation,
(timestamp2-timestamp1)+timestamp1 should equal timestamp2, but clearly does
not.
2010-04-14 23:00:00-05 does not equal 2010-04-14 22:00:00-05.
Thank you for your assistance in this matter,
ERIC
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5324: Server not starting
On Thu, Feb 11, 2010 at 11:45 PM, sourabh bansal wrote: > > The following bug has been logged online: > > Bug reference: 5324 > Logged by: sourabh bansal > Email address: sourabh.k.ban...@gmail.com > PostgreSQL version: 8.4 > Operating system: windows XP SP2 > Description: Server not starting > Details: > > While Starting Server the Error message is coming like "The service name is > invalid net helpmsg 2185" > I have installed the through One click installation and done > successfully... > > Please reply ... > I have tried by reinstalling also... > By checking post connectivity and host Address also.. > > But no result > Pls provide solution Sounds to me like you're probably doing something wrong, but I don't know what it is. Wrong service name, maybe? http://wiki.postgresql.org/wiki/Guide_to_reporting_problems ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5323: plperl and plperlu interaction segfaults
On Fri, Feb 12, 2010 at 5:04 AM, Alexey Klyukin wrote:
>
> On Feb 12, 2010, at 6:21 AM, Robert Haas wrote:
>
>> On Thu, Feb 11, 2010 at 10:47 PM, Robert Haas wrote:
>>> On Thu, Feb 11, 2010 at 3:12 PM, Alexey Klyukin
>>> wrote:
> I think this might be the same problem previously discussed here:
> http://archives.postgresql.org/pgsql-bugs/2010-01/msg00224.php
Seems to be the same problem. Backtrace I'm getting on 8.4 is almost
identical to the one at the end of this post:
http://archives.postgresql.org/pgsql-bugs/2010-01/msg00144.php
> Does that patch fix it?
The problem doesn't appear on 9.0 alpha with that patch committed.
>>>
>>> It looks like the plperl.c change applies cleanly back to 8.2, though
>>> I haven't yet verified that it actually fixes the problem on all those
>>> branches.
>>
>>
>> Not sure where to go from here. The prerequisite patch ("Various
>> small improvements and cleanups for PL/Perl") is not suitable for
>> backpatching, so I think someone needs to prepare a cut-down patch
>> that addresses this issue in a more surgical fashion. Also, would
>> like to understand why the test case fails to fail on CVS HEAD.
>>
>
> Andrew Dunstan backpatched all versions down to 8.2 yesterday:
> http://archives.postgresql.org/message-id/20100212043203.08c6e754...@cvs.postgresql.org
>
> This eliminated the bug on my system (checked on 8.4).
> The problem appeared due to the context mismatch between the interpreter that
> created the reference
> to the compiled subroutine and the one that tried to decrement the reference
> count. Setting a correct context fixed the problem.
Yeah, I saw that commit. Cool.
...Robert
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5323: plperl and plperlu interaction segfaults
On Feb 12, 2010, at 6:21 AM, Robert Haas wrote:
> On Thu, Feb 11, 2010 at 10:47 PM, Robert Haas wrote:
>> On Thu, Feb 11, 2010 at 3:12 PM, Alexey Klyukin
>> wrote:
I think this might be the same problem previously discussed here:
http://archives.postgresql.org/pgsql-bugs/2010-01/msg00224.php
>>>
>>> Seems to be the same problem. Backtrace I'm getting on 8.4 is almost
>>> identical to the one at the end of this post:
>>> http://archives.postgresql.org/pgsql-bugs/2010-01/msg00144.php
>>>
Does that patch fix it?
>>> The problem doesn't appear on 9.0 alpha with that patch committed.
>>
>> It looks like the plperl.c change applies cleanly back to 8.2, though
>> I haven't yet verified that it actually fixes the problem on all those
>> branches.
>
>
> Not sure where to go from here. The prerequisite patch ("Various
> small improvements and cleanups for PL/Perl") is not suitable for
> backpatching, so I think someone needs to prepare a cut-down patch
> that addresses this issue in a more surgical fashion. Also, would
> like to understand why the test case fails to fail on CVS HEAD.
>
Andrew Dunstan backpatched all versions down to 8.2 yesterday:
http://archives.postgresql.org/message-id/20100212043203.08c6e754...@cvs.postgresql.org
This eliminated the bug on my system (checked on 8.4).
The problem appeared due to the context mismatch between the interpreter that
created the reference
to the compiled subroutine and the one that tried to decrement the reference
count. Setting a correct context fixed the problem.
--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
