CODEWITHSUNDEEP
pythonpostgresqldatedatetimesqlalchemy
JarochoEngineer
JarochoEngineer

Reputation: 1787

Discrepancies when subtracting dates with timestamp in SQLAlchemy and Postgresql

I have some discrepancy when subtracting dates in Postgresql and SQLAlchemy. For instance, I have the following in Postgresql:

SELECT trunc(EXTRACT(EPOCH FROM ('2019-07-05 15:20:10.111497-07:00'::timestamp - '2019-07-04 11:45:17.293328-07:00'::timestamp)))

--99292

and the following query in SQLAlchemy:

date_diff = session.query(func.trunc((func.extract('epoch',
                                                    func.date('2019-07-05 15:20:10.111497-07:00'))-
                                      func.extract('epoch',
                                                    func.date('2019-07-04 11:45:17.293328-07:00'))))).all()
print(date_diff)

#[(86400.0,)]

We can see that the most exact difference is coming from Postgresql query. How can I get the same result using SQLAlchemy? I have not been able to spot what is the cause of this difference. If you know please let me know.

Thanks a lot.

Upvotes: 0

Views: 144

Answers (1)

Graphight
Graphight

Reputation: 11

Have never used SQLAlchemy before but it looks like you are trying to truncate to a date instead of a timestamp or datetime

Don't worry, this is an easy mistake to make. DateTime libraries can be confusing with their definitions (a date is a literally a Date so YYYY-MM-DD whereas a timestamp includes both the date and time to some denomination)

This is why you have a difference of 86,400 (one day) because it is comparing the dates of the two objects (2019-07-05 - 2019-07-04)

Try using the func.time.as_utc() or something similar to get a timestamp

You want to be comparing the WHOLE timestamp

EDIT: Sorry, didn't see your comment until after posting.

Upvotes: 1

Related Questions