How to insert current_timestamp into Postgres via python

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 t开发者_如何学JAVAhen 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.

---

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

---

Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of month, day, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields.

In other words: just write anything and it will work.

Or check this table with all the unambiguous formats.

---

Sure, just pass a string value for that timestamp column in the format: '2011-05-16 15:36:38' (you can also append a timezone there, like 'PST'). PostgreSQL will automatically convert the string to a timestamp. See http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT

---

from datetime import datetime as dt

then use this in your code:

cur.execute('INSERT INTO my_table (dt_col) VALUES (%s)', (dt.now(),))

---

Just use

now()

or

CURRENT_TIMESTAMP

I prefer the latter as I like not having additional parenthesis but thats just personal preference.