Home > mailing lists

Thread: [MASSMAIL]Getting wrong datetime in database using insert into table query.

[MASSMAIL]Getting wrong datetime in database using insert into table query.

From

Saksham Joshi

Date:

02 April, 11:58:52

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.

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

From

Sándor Daku

Date:

02 April, 12:48:18

On Tue, 2 Apr 2024 at 10:59, Saksham Joshi <sakshamjoshi64@gmail.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

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

From

Adrian Klaver

Date:

02 April, 17:43:35

On 4/2/24 01:58, Saksham Joshi 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.


Read:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

-- 
Adrian Klaver
adrian.klaver@aklaver.com