How to use datetime instance as a timestamp in postgresql?

Question

I want to create postgresql function (CREATE FUNCTION) with PL/Python.

How do I convert from python datetime object to postgresql timestamp object in procedure?

"postgres function in python"? Huh? You want to access postgres from python? or use python to issue a create procedure-type call to pg? — Marc B
– Marc B, Commented Jul 20, 2016 at 14:19
@MarcB yes, I want to create stored procedure in postgresql. — KiYugadgeter
– KiYugadgeter, Commented Jul 20, 2016 at 14:33
then you convert your python datetime to a format that PG accepts, and do the same in reverse with the results that PG outputs. — Marc B
– Marc B, Commented Jul 20, 2016 at 14:34

Clodoaldo Neto · Accepted Answer · 2016-07-20 17:03:57Z

1

Tell the prepare method the type of the parameter value, a timestamp, then pass a datetime value to the execute method:

create or replace function pytest()
returns timestamp as $$

    from datetime import datetime
    plan = plpy.prepare('select $1 as my_datetime', ['timestamp'])
    rv = plpy.execute(plan, (datetime.now(),))
    return rv[0]['my_datetime']

$$ language plpythonu;

select pytest();
           pytest           
----------------------------
 2016-07-20 13:57:59.625991

Database Access from PL/Python

answered Jul 20, 2016 at 17:03

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use datetime instance as a timestamp in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related