How to insert current_timestamp into Postgres via python


Question

I need to insert rows into PG one of the fields is date and time with time stamp, this is the time of incident, so I can not use --> current_timestamp function of Postgres at the time of insertion, so how can I then insert the time and date which I collected before into pg row in the same format as it would have been created by current_timestamp at that point in time.

1
42
5/16/2011 2:50:41 PM

Accepted Answer

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'));
63
5/16/2011 2:58:43 PM

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

dt = datetime.now()
cur.execute('INSERT INTO some_table (somecol) VALUES (%s)', (dt,))

More Python types that can be adapted into SQL (and returned as Python objects when a query is executed) are listed here.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon