Thread: plpython timestamp without time zone, showing up as text instead of timestamp
My full function works fine as a standalone python script, but I was having trouble getting it to work in Posgres.
Outside of Postgres it worked fine (because I was feeding in lists of dates to test on).
The I did the script with psycopg2 calling in the below table and it worked fine. (in the docs for psycopg2 it says it converts the timestamps into python dates, this would explain why it is working in this scenario)
I finally figured out what the issue is, when using plpython and pulling a date out of Postgres, it is treating it as text - is that intended or am I doing something wrong?
Here is a quick/simple example to demonstrate:
--##################################
CREATE OR REPLACE FUNCTION some_test(subject_a timestamp without time zone[], bt_len integer)
RETURNS timestamp without time zone AS
$BODY$
from datetime import datetime, timedelta
defined_period = timedelta(days=bt_len)
return subject_a[0] + defined_period
$BODY$
LANGUAGE plpythonu;
create table hold_dates
(
initials timestamp without time zone[]
);
insert into hold_dates values('{2014-01-09 10:10:03, 2014-02-18 10:10:03}');
select
some_test(initials, 2)
from
hold_dates;
--##################################
The result of the above is:
ERROR: TypeError: cannot concatenate 'str' and 'datetime.timedelta' objectsCONTEXT: Traceback (most recent call last):PL/Python function "some_test", line 5, in <module>return subject_a[0] + defined_periodPL/Python function "some_test"
So the question is - why is plpython returning subject_a[0] as text rather than an actual date that python can operate on?, is that intended? (trying to learn how to think about this is a Postgres way)
Granted,
There is a workaround, I can just change my function to read:
return datetime.strptime(subject_a[0], '%Y-%m-%d %H:%M:%S') + defined_period
instead of:
return subject_a[0] + defined_period
However,
I am just trying to figure out why if postgres knows it is a day, it is not telling python that in the function - like it would when I use psycopg2?
Re: plpython timestamp without time zone, showing up as text instead of timestamp
From
Adrian Klaver
Date:
On 03/23/2014 08:19 AM, jared wrote: > My full function works fine as a standalone python script, but I was > having trouble getting it to work in Posgres. > > Outside of Postgres it worked fine (because I was feeding in lists of > dates to test on). > > The I did the script with psycopg2 calling in the below table and it > worked fine. (in the docs for psycopg2 it says it converts the > timestamps into python dates, this would explain why it is working in > this scenario) > > I finally figured out what the issue is, when using plpython and pulling > a date out of Postgres, it is treating it as text - is that intended or > am I doing something wrong? Intended: http://www.postgresql.org/docs/9.3/interactive/plpython-data.html FYI, it would be a good idea to include the Postgres version. plpython has been undergoing a lot of changes recently, so it would help to peg where you are at in that sequence. > > > > So the question is - why is plpython returning subject_a[0] as text > rather than an actual date that python can operate on?, is that > intended? (trying to learn how to think about this is a Postgres way) See above. > > > > Granted, > There is a workaround, I can just change my function to read: > return datetime.strptime(subject_a[0], '%Y-%m-%d %H:%M:%S') + defined_period > instead of: > return subject_a[0] + defined_period > > > However, > I am just trying to figure out why if postgres knows it is a day, it is > not telling python that in the function - like it would when I use psycopg2? Because psycopg2 has an additional typecasting/adaptation code: http://initd.org/psycopg/docs/usage.html#adaptation-of-python-values-to-sql-types I find dateutil(https://labix.org/python-dateutil) a handy package to have in this situation. Use parse() from the parser module to do the heavy lifting of converting from a string into a date/datetime object. > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Mar 23, 2014 at 12:11 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Intended:
http://www.postgresql.org/docs/9.3/interactive/plpython-data.html
FYI, it would be a good idea to include the Postgres version. plpython has been undergoing a lot of changes recently, so it would help to peg where you are at in that sequence.
Ah, I see it now:
"All other data types, including the PostgreSQL character string types, are converted to a Python str."
Thanks (as an aside Postgres 9.3 and python 2.7)
I find dateutil(https://labix.org/python-dateutil) a handy package to have in this situation. Use parse() from the parser module to do the heavy lifting of converting from a string into a date/datetime object.
I will look into that - thanks again.
Re: plpython timestamp without time zone, showing up as text instead of timestamp
From
Adrian Klaver
Date:
On 03/23/2014 09:47 AM, jared wrote: > > > > On Sun, Mar 23, 2014 at 12:11 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > > Intended: > > http://www.postgresql.org/__docs/9.3/interactive/plpython-__data.html <http://www.postgresql.org/docs/9.3/interactive/plpython-data.html> > > FYI, it would be a good idea to include the Postgres version. > plpython has been undergoing a lot of changes recently, so it would > help to peg where you are at in that sequence. > > > Ah, I see it now: > "All other data types, including the PostgreSQL character string types, > are converted to a Python str." > Thanks (as an aside Postgres 9.3 and python 2.7) > > I find dateutil(https://labix.org/__python-dateutil > <https://labix.org/python-dateutil>) a handy package to have in this > situation. Use parse() from the parser module to do the heavy > lifting of converting from a string into a date/datetime object. > > > I will look into that - thanks again. Should have added previously, if you use dateutil you can dispense with: from datetime import datetime, timedelta and use the relativedelta module from dateutil to do your offsets -- Adrian Klaver adrian.klaver@aklaver.com