Unable to convert string to timestamp on postgresql with sqlalchemy. I have tried all possible ways but nothing seems to work

Question

I am working on a Flask project with Postgresql as my database. I have a schema called interest which looks like

create table interest(
usr int,
activity_category int,
activity_subcategory int, 
pid int,
start_time timestamp,
end_time timestamp,
budget int check (budget >= 0),
primary key (usr, activity_category, activity_subcategory, pid, start_time, end_time),
foreign key (usr) references users(userid),
foreign key(pid, activity_category, activity_subcategory) references location(pid, aid, aaid)

);

I am trying to insert values into the interest table. At present, I have starttime and endtime as strings but I guess I need to somehow typecast them to timestamp. I have tried these two methods but none of them work.

entry_by_location = g.conn.execute("insert into interest(usr, activity_category, activity_subcategory, pid, start_time, end_time, budget) values (%d, %d, %d, %d, to_timestamp(%s::text, 'YYYY-MM-DD HH:MI'), to_timestamp(%s::text, 'YYYY-MM-DD HH:MI'), %d)" %(int(uid), int(aaid), int(aid), int(pid), starttime, endtime, budget))

and

entry_by_location = g.conn.execute("insert into interest(usr, activity_category, activity_subcategory, pid, start_time, end_time, budget) values (%d, %d, %d, %d, %s::timestamp, %s::timestamp, %d)" %(int(uid), int(aaid), int(aid), int(pid), starttime, endtime, budget))

The error that I get is -

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "10" LINE 1: ... end_time, budget) values (1, 2, 7, 1, 2016-11-15 10:13::tim... ^ [SQL: 'insert into interest(usr, activity_category, activity_subcategory, pid, start_time, end_time, budget) values (1, 2, 7, 1, 2016-11-15 10:13::timestamp, 2016-10-15 11:24::timestamp, 1000)']

I've spent two days trying to figure this out. Any sort of help would be really appreciated.

Thank you!

That worked! Wow. This is interesting. I need to put quotes with %s so that a date with a string data type gets converted to '2014-20-10' instead of simply being 2014-20-10. — Dhruv Shekhawat
– Dhruv Shekhawat, Commented Nov 9, 2016 at 7:14
I will post this solution as an answer and kindly make it as the best answer, so that it would be helpful for others — Viki888
– Viki888, Commented Nov 9, 2016 at 7:19

Viki888 · Accepted Answer · 2016-11-09 07:24:03Z

2

In the error message it is clear that, there is an error near 10. That is the sql cannot recognise the datatype of 2016-11-15 10:13 due to space exist between Date and Time.

So adding quotes to %s will solve the issue like '%s'.

answered Nov 9, 2016 at 7:24

Viki888

2,7742 gold badges15 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Unable to convert string to timestamp on postgresql with sqlalchemy. I have tried all possible ways but nothing seems to work

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