From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | jared <afonit(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: plpython timestamp without time zone, showing up as text instead of timestamp |
Date: | 2014-03-23 16:11:10 |
Message-ID: | 532F079E.80406@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | jared | 2014-03-23 16:47:25 | Re: plpython timestamp without time zone, showing up as text instead of timestamp |
Previous Message | jared | 2014-03-23 15:19:11 | plpython timestamp without time zone, showing up as text instead of timestamp |