[Pdx-pm] Postgres and DBI issue

[Selena Deckelmann]

On Nov 21, 2007 8:50 AM, Chris Dawson <xrdawson at gmail.com> wrote:
> Hi there,
>
> I'm running a query which fails inside of DBI, but works without issue
> from the psql client.  Does anyone have suggestions?  This is not a
> complicated query, and I am unclear as to why the perl version
> requires a cast of some sort.

I'm using DBD::Pg 1.49, and postgresql version 8.2.4 for my testing.

I am having a hard time reproducing your error. The error doesn't seem
to match either of the queries that you presented. Do you have any
other queries running after your foreach statement?

That error "time without timezone + time without timezone" comes about
because two independent "time" values don't mean anything when added
together unless one or both are cast to "interval" -- a simple example
is: (11pm + 1am).  The system can't figure out what you mean unless
one of those time values is cast to an interval of time (as in, 1am
really means "1 hour").

And be careful with intervals without a time as an anchor.  Intervals
are not very smart - they don't know anything about when they started,
so you can get into trouble around the end of the month, leap years,
etc.

The date/time functions and the operators with data types they are
defined for are here:
http://www.postgresql.org/docs/8.2/static/functions-datetime.html

Also, unrelated to your error, I  wonder if you used CURRENT_DATE
instead of now() in your addition operators if that would fix a bug in
$recurring_query? I'm not sure about the specific context, but using
now() there doesn't seem quite right because it would include the
current time.  psql is doing an implicit cast from TIME to INTERVAL --
which is probably not what you want.

See below:

mytest=# select now() + start_time, start_time from schedule;
           ?column?            | start_time
-------------------------------+------------
 2007-11-22 08:10:33.746351-08 | 21:00:00
 2007-11-22 08:10:33.746351-08 | 21:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00
 2007-11-21 20:10:33.746351-08 | 09:00:00


-selena

-- 
Selena Deckelmann
PDXPUG - Portland PostgreSQL Users Group
http://pugs.postgresql.org/pdx
http://www.chesnok.com/daily