How to insert current_timestamp into Postgres via python
If you use psycopg2
(and possibly some other client library), you can simply pass a Python datetime
object as a parameter to a SQL-query:
from datetime import datetime, timezone
dt = datetime.now(timezone.utc)
cur.execute('INSERT INTO mytable (mycol) VALUES (%s)', (dt,))
(This assumes that the timestamp with time zone
type is used on the database side.)
More Python types that can be adapted into SQL (and returned as Python objects when a query is executed) are listed here.
A timestamp does not have "a format".
The recommended way to deal with timestamps is to use a PreparedStatement where you just pass a placeholder in the SQL and pass a "real" object through the API of your programming language. As I don't know Python, I don't know if it supports PreparedStatements and how the syntax for that would be.
If you want to put a timestamp literal into your generated SQL, you will need to follow some formatting rules when specifying the value (a literal does have a format).
Ivan's method will work, although I'm not 100% sure if it depends on the configuration of the PostgreSQL server.
A configuration (and language) independent solution to specify a timestamp literal is the ANSI SQL standard:
INSERT INTO some_table
(ts_column)
VALUES
(TIMESTAMP '2011-05-16 15:36:38');
Yes, that's the keyword TIMESTAMP
followed by a timestamp formatted in ISO style (the TIMESTAMP
keyword defines that format)
The other solution would be to use the to_timestamp()
function where you can specify the format of the input literal.
INSERT INTO some_table
(ts_column)
VALUES
(to_timestamp('16-05-2011 15:36:38', 'dd-mm-yyyy hh24:mi:ss'));
Just use 'now'
http://www.postgresql.org/docs/8.0/static/datatype-datetime.html