0

I have a query that looks like this

select count(*)
from table
where created_at<TIMESTAMP '2019-10-18 06:14:33'

that returns 1262 when ran in python, and 1118 when ran in dbeaver. The correct number is 1118. Is there any reason why the statements are processed differently? Is this a known bug with psycopg2?

Bruno E
  • 156
  • 11
  • You need to create some list/set of examples that exist in one result set and not the other. At the moment, it's just our imagination – roganjosh Mar 03 '20 at 14:30
  • I’ll generate a few examples after work. They all have the created_at field 1-20 seconds AFTER the specified Timestamp. – Bruno E Mar 03 '20 at 15:07

1 Answers1

0

Same problem have. I have more complicated query but the problem is the same. It doesn't matter about details of DB, will enough that you operate with dates then results are different. I sure it is regarding time zones. When Python is trying to execute he somehow covert it into different timezone than client

Similar problem raised here: python script keeps converting dates to utc

Update: So the problem has been solved You should defined on both sides which time zone you want to use. When you use PosgreSQL client as DBeaver he is using DB timezone defined

SELECT * 
FROM pg_timezone_names 
WHERE name = current_setting('TIMEZONE');

So this simplified my query:

    select count(id) 
    from realestates_realestate
    where create_date::date = now()::date – 1

Should look like this:

    select count(id) 
    from realestates_realestate
    where (create_date AT TIME ZONE 'CET')::date = (now() AT TIME ZONE 'CET')::date – 1

In that case results will be the same, cause Python is executing query on different timezone (as it seem to me on UTC time zone). When you define in SQL which time zone use then problem solved.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michał
  • 18
  • 4
  • If you have any doubts or want to ask how to write better answers comment just below this comment – bhucho Dec 12 '20 at 09:35
  • Sorry I tried to add this as comment, but didint allowed me (I should have 50 points or something). But at least I provided a link with page where according to commnets provided solution – Michał Dec 12 '20 at 09:39
  • @bhucho: When there isn’t an accepted answer, partial answers that help isolate or clarify the problem—such as by narrowing the conditions or eliminating possible solutions—are acceptable: “Still no answer to the question, and you have the same problem? Help us find a solution by researching the problem, then contribute the results of your research and anything additional you’ve tried as a partial answer. That way, even if we can’t figure it out, the next person has more to go on.” ([source](https://stackoverflow.com/help/how-to-answer)). – Jeremy Caney Dec 12 '20 at 09:49
  • @Michał: That’s much more useful; thank you. – Jeremy Caney Dec 12 '20 at 11:09
  • `explain analyze ...` could be more useful to diagnose such problems. – Abelisto Dec 12 '20 at 11:26