How to insert current_timestamp into Postgres via Python?
Last Updated :
18 Mar, 2022
For processing timestamps, PostgreSQL supports two data types timestamp and timestamptz. timestamp datatype helps us create a timestamp without timezone, and timestamptz helps us create a timestamp with a timezone. One may store both date and time with the timestamp datatype. It does not, however, include time zone information. It implies that if you alter your database server’s timezone, the timestamp value saved in the database will not automatically update, in these situations timestamptz datatype is used.
Example 1:
The below code is an example of the datatypes. psycopg2.connect() method is used to establish a connection to the database. The cursor is created using the connection.cursor() method. execute() method executes the sql commands given. A table named timestamp_data is created. A string of the formatted timestamp is inserted in the table created. Values are fetched from the table. In the table, we can see column timestamp_timezone also shows timezone.
Python3
import psycopg2
from datetime import datetime, timezone
conn = psycopg2.connect(
database = "TIMESTAMP_DATA" , user = 'postgres' , password = 'pass' ,
host = '127.0.0.1' , port = '5432'
)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(
)
cursor.execute(
)
sql1 =
cursor.execute(sql1)
for i in cursor.fetchall():
print (i)
conn.commit()
conn.close()
|
Output:
Example 2 :
In this example, psycopg2 and DateTime packages are imported. psycopg2.connect() method is used to establish a connection to the database. The cursor is created using the connection.cursor() method. execute() method executes the SQL commands given. Values are inserted in the table created. datetime.now() is used to calculate the current_timestamp, it’s further inserted into the table. cursor.fetchall() method is used to fetch all the rows.
Python3
import psycopg2
from datetime import datetime, timezone
conn = psycopg2.connect(
database = "Banking" , user = 'postgres' , password = 'pass' ,
host = '127.0.0.1' , port = '5432'
)
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(
'create table bank_records(amount_deposited decimal , Date timestamptz);' )
deposit_amount = 4565.89
dt = datetime.now(timezone.utc)
cursor.execute( 'insert into bank_records values(%s,%s)' , (deposit_amount, dt,))
sql1 =
cursor.execute(sql1)
for i in cursor.fetchall():
print (i)
conn.commit()
conn.close()
|
Output:
(Decimal(‘4565.89’), datetime.datetime(2022, 3, 6, 19, 2, 3, 669114,
tzinfo=datetime.timezone(datetime.timedelta(seconds=19800))))
Share your thoughts in the comments
Please Login to comment...