Getting wrong datetime in database using insert into table query.

From: Saksham Joshi <sakshamjoshi64(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Getting wrong datetime in database using insert into table query.
Date: 2024-04-02 08:58:52
Message-ID: CAPRm4pNrYGE4mbpGKUHJOD5vrWKP-ffK680Nmd-_0qqsWoL36A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

OS: Windows 10
Psycopg version: 2.9.9
Python version: 3.11.6
PostgreSQL version: 11
pip version : 24.0
1: what you did
We are using 'psycopg2-binary' library to connect to my postgresql hosted
on Azure.We have created a table named 'apilog' to store our api logs using
'Insert Into table' query.We have specifically added two columns named
create_date and update_date with 'timestamp with time zone' property
enabled.I only update create_date for each log locally using python and i
expected update_date column to automatically update the datetime when the
transaction is committed at the end in python.
2: what you expected to happen
I expected to see update_date column returning datetime values which are
similar to the time the transaction is committed in python however instead
the value seems to returning datetime which is more closer to the time db
connection is established.
3: what happened instead
The datetime value in update_date is coming earlier than the create_date
value of even the very first log which is creating discrepancy and making
it difficult to track the exact time logs are committed into database.

For example:
This query INSERT INTO api_log(log_detail,create_date)
VALUES('example log 1', datetime.datetime.utcnow'),('example log 2',
datetime.datetime.utcnow')
Should ideally return update_date which is older than 'example log 2'
create_date but it is returning a datetime which is even earlier than
'example log 1' create_date.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sándor Daku 2024-04-02 09:48:18 Re: Getting wrong datetime in database using insert into table query.
Previous Message Laurenz Albe 2024-04-02 08:16:00 Re: Not able to purge partition