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

User · Answer

Just use      now     or      CURRENT TIMESTAMP   I prefer the latter as I like not having additional parenthesis but thats just personal preference

User · Answer

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

User · 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

User · Answer

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

User · Answer

from datetime import datetime as dt   then use this in your code   cur execute  INSERT INTO my table  dt col  VALUES   s     dt now

User · Answer

Just use  now   http   www postgresql org docs 8 0 static datatype-datetime html

User · Answer

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

[python] How to insert current_timestamp into Postgres via python

Examples related to python

Examples related to postgresql

Examples related to datetime