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.
This question is related to
python
postgresql
datetime
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
Just use
now()
or
CURRENT_TIMESTAMP
I prefer the latter as I like not having additional parenthesis but thats just personal preference.
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.
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.
from datetime import datetime as dt
then use this in your code:
cur.execute('INSERT INTO my_table (dt_col) VALUES (%s)', (dt.now(),))
Source: Stackoverflow.com