Re: Getting wrong datetime in database using insert into table query.

On Tue, 2 Apr 2024 at 10:59, Saksham Joshi <sakshamjoshi64(at)gmail(dot)com> wrote:

> 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.
>

Without seeing the definition of your table it's only a guess, but if you
using 'now' or now() as the default value for update_date then this might
be the source of your problem:

https://www.postgresql.org/docs/11/functions-datetime.html
now() timestamp with time zone Current date and time (start of current
transaction);I think clock_timestamp is what you need.

Regards,
Sándor