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.89dt = 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))))
