How to insert current_timestamp into Postgres via Python?

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 packages
import psycopg2
from datetime import datetime, timezone

# establish a connection
conn = psycopg2.connect(
    database="TIMESTAMP_DATA", user='postgres', password='pass',
    host='127.0.0.1', port='5432'
)


conn.autocommit = True

# creating a cursor
cursor = conn.cursor()

# creating a table
cursor.execute('''CREATE TABLE timestamp_data
(timestamp TIMESTAMP,timestamp_timezone TIMESTAMPTZ);''')

# inserting timestamp values
cursor.execute('''INSERT INTO timestamp_data VALUES
('2021-05-20 12:07:18-09','2021-05-20 12:07:18-09');''')

# fetching data
sql1 = '''select * from timestamp_data;'''
cursor.execute(sql1)
for i in cursor.fetchall():
    print(i)

conn.commit()

# closing the connection
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 packages
import psycopg2
from datetime import datetime, timezone

# establish connection
conn = psycopg2.connect(
    database="Banking", user='postgres', password='pass',
    host='127.0.0.1', port='5432'
)

# autocommit is set to True
conn.autocommit = True

# creating a cursor
cursor = conn.cursor()

# creating a table
cursor.execute(
    'create table bank_records(amount_deposited decimal , Date timestamptz);')

deposit_amount = 4565.89
dt = datetime.now(timezone.utc)

# inserting values
cursor.execute('insert into bank_records values(%s,%s)', (deposit_amount, dt,))

# fetching rows
sql1 = '''select * from bank_records;'''
cursor.execute(sql1)
for i in cursor.fetchall():
    print(i)

conn.commit()

# closing the connection
conn.close()

Output:

(Decimal(‘4565.89’), datetime.datetime(2022, 3, 6, 19, 2, 3, 669114, 

tzinfo=datetime.timezone(datetime.timedelta(seconds=19800))))