Re: plpython timestamp without time zone, showing up as text instead of timestamp

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