First of all, thank you for your time and feedback. I really apologize for my inattention. The problem wasn't related with sqlalchemy or python/sql timezones. I run raw sql query using Pycharm + Database console <https://www.jetbrains.com/help/pycharm/database-console.html>. The results using *database console* and *psql* in docker container are different.
<https://lh3.googleusercontent.com/-18asnuj5Du4/Wz4o33WJV3I/AAAAAAAAAKY/Ij0OXJMr4CUYiIVBipTqz7A6OA7nrT_WACLcBGAs/s1600/Screen%2BShot%2B2018-07-05%2Bat%2B5.15.28%2BPM.png> <https://lh3.googleusercontent.com/-wLNQbg6MyJ8/Wz4o7Z_oJjI/AAAAAAAAAKc/B4omNeIoGGAKMgWZhT4mkZLF83xALwiSQCLcBGAs/s1600/Screen%2BShot%2B2018-07-05%2Bat%2B5.18.40%2BPM.png> So it was my mistake. Thank you one more time. On Friday, June 29, 2018 at 6:45:01 PM UTC+3, Mike Bayer wrote: > check that the timezones are matching up, e.g. that your python times > aren't X hours off from the PG one, stuff like that > > On Fri, Jun 29, 2018 at 11:44 AM, Mike Bayer <mik...@zzzcomputing.com > <javascript:>> wrote: > > you need to look at the SQL output and the results and additionally > > log in to the psql command line and check individual elements of what > > you are doing. like what the current time is from now(), what are > > the rows in the table without any WHERE criterion, etc. > > > > > > On Fri, Jun 29, 2018 at 11:41 AM, Danila Ganchar > > <danila....@gmail.com <javascript:>> wrote: > >> Thank you for your feedback. I apologize for perseverance. > >> I understand the difference between python now() and db now(). > >> Why I didn't use sleep() in previous example? Because when I run the > script > >> again a few seconds has already passed. And I should see at least one > or two > >> record. > >> > >> I checked your script. In my case I got the error: > >> > >> Traceback (most recent call last): > >> File "path_to_test.py", line 45, in <module> > >> assert len(with_error.all()) == 10 > >> AssertionError > >> > >> I checked one more time. My docker-compose: > >> > >> version: '2' > >> services: > >> db: > >> image: postgres > >> ports: > >> - "5432:5432" > >> environment: > >> POSTGRES_DB: test > >> POSTGRES_USER: postgres > >> POSTGRES_PASSWORD: postgres > >> restart: always > >> > >> > >> My script: > >> > >> import datetime > >> > >> from sqlalchemy import * > >> from sqlalchemy.ext.declarative import declarative_base > >> from sqlalchemy.orm import * > >> > >> Base = declarative_base() > >> > >> > >> class User(Base): > >> __tablename__ = 'test_user' > >> > >> id = Column(Integer, primary_key=True) > >> timeout = Column(Integer) > >> last_receive_time = Column(TIMESTAMP) > >> > >> e = create_engine("postgresql://postgres:postgres@localhost:5432/test", > >> echo='debug') > >> Base.metadata.drop_all(e) > >> Base.metadata.create_all(e) > >> > >> session = Session(e) > >> for i in range(10): > >> session.add( User( timeout=1, > last_receive_time=datetime.datetime.now() > >> )) > >> > >> session.commit() > >> > >> import time > >> time.sleep(1) > >> > >> correct = session.query(User)\ > >> .from_statement(text(""" > >> SELECT * > >> FROM test_user > >> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < > :now > >> """)).params(now=datetime.datetime.now()) > >> > >> assert len(correct.all()) == 10 > >> > >> with_error = session.query(User)\ > >> .from_statement(text(""" > >> SELECT * > >> FROM test_user > >> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < > NOW() > >> """)) > >> > >> assert len(with_error.all()) == 10 > >> > >> > >> What I doing wrong? Sorry but it's really strange. > >> > >> On Wednesday, June 27, 2018 at 7:06:04 PM UTC+3, Mike Bayer wrote: > >>> > >>> I apologize for my inpatient tone in my previous email. I would hope > >>> that everyone in this mailing list feels welcome. > >>> > >>> On Wed, Jun 27, 2018, 10:59 AM Mike Bayer <mik...@zzzcomputing.com> > wrote: > >>>> > >>>> this is a very simple issue to debug with basic programming > >>>> techniques, first off, put echo='debug' in your create_engine so you > >>>> can see all SQL and results: > >>>> > >>>> e = create_engine("postgresql://scott:tiger@localhost/test", > >>>> echo='debug') > >>>> > >>>> > >>>> next, if you are running over to a SQL command line vs. running a > >>>> program, the big difference is that *time has passed*. Put a sleep > in > >>>> your program and there is your row: > >>>> > >>>> from sqlalchemy import * > >>>> from sqlalchemy.orm import * > >>>> from sqlalchemy.ext.declarative import declarative_base > >>>> from sqlalchemy.ext.declarative import declared_attr > >>>> import datetime > >>>> > >>>> Base = declarative_base() > >>>> > >>>> class User(Base): > >>>> __tablename__ = 'test_user' > >>>> > >>>> id = Column(Integer, primary_key=True) > >>>> timeout = Column(Integer) > >>>> last_receive_time = Column(TIMESTAMP) > >>>> > >>>> e = create_engine("postgresql://scott:tiger@localhost/test", > >>>> echo='debug') > >>>> Base.metadata.drop_all(e) > >>>> Base.metadata.create_all(e) > >>>> > >>>> session = Session(e) > >>>> for i in range(10): > >>>> session.add( User( timeout=1, > >>>> last_receive_time=datetime.datetime.now() )) > >>>> > >>>> session.commit() > >>>> > >>>> import time > >>>> time.sleep(1) > >>>> > >>>> query = session.query(User)\ > >>>> .from_statement(text(""" > >>>> SELECT * > >>>> FROM test_user > >>>> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < > NOW() > >>>> """)) > >>>> > >>>> assert len(query.all()) == 10 > >>>> > >>>> > >>>> > >>>> On Wed, Jun 27, 2018 at 6:30 AM, Danila Ganchar > >>>> <danila....@gmail.com> wrote: > >>>> > Ok. I ran the script 10 times on a clean table. > >>>> > Now I run raw sql: > >>>> > > >>>> > SELECT * > >>>> > FROM test_user > >>>> > WHERE (last_receive_time + INTERVAL '1 second' * timeout) < NOW(); > >>>> > > >>>> > The result: 10 records. > >>>> > Now I run script one more time. Still 0 records using > from_statement. > >>>> > > >>>> > amount records using NOW() = 0 > >>>> > > >>>> > As I understand it, is this correct? This is just unclear because > >>>> > from_statement + text works not like raw query. > >>>> > > >>>> > On Tue, Jun 26, 2018 at 7:42 PM, Mike Bayer < > mik...@zzzcomputing.com> > >>>> > wrote: > >>>> >> > >>>> >> definitely, triyng to match up python .now() with database .now() > is > >>>> >> not going to work, in particular since .now() in the database is > often > >>>> >> the transaction start time, not the actual time. > >>>> >> > >>>> >> On Tue, Jun 26, 2018 at 12:28 PM, Jonathan Vanasco > >>>> >> <jona...@findmeon.com> wrote: > >>>> >> > the difference is possibly because this is calculated in Python, > >>>> >> > each > >>>> >> > time > >>>> >> > it is executed: > >>>> >> > > >>>> >> > datetime.datetime.now() > >>>> >> > > >>>> >> > this is calculated in Postgres, and refers to the beginning of > the > >>>> >> > transaction; it does not change across the transaction. > >>>> >> > > >>>> >> > NOW() > >>>> >> > > >>>> >> > -- > >>>> >> > SQLAlchemy - > >>>> >> > The Python SQL Toolkit and Object Relational Mapper > >>>> >> > > >>>> >> > http://www.sqlalchemy.org/ > >>>> >> > > >>>> >> > To post example code, please provide an MCVE: Minimal, Complete, > and > >>>> >> > Verifiable Example. See http://stackoverflow.com/help/mcve for > a > >>>> >> > full > >>>> >> > description. > >>>> >> > --- > >>>> >> > You received this message because you are subscribed to the > Google > >>>> >> > Groups > >>>> >> > "sqlalchemy" group. > >>>> >> > To unsubscribe from this group and stop receiving emails from > it, > >>>> >> > send > >>>> >> > an > >>>> >> > email to sqlalchemy+...@googlegroups.com. > >>>> >> > To post to this group, send email to sqlal...@googlegroups.com. > >>>> >> > Visit this group at https://groups.google.com/group/sqlalchemy. > >>>> >> > For more options, visit https://groups.google.com/d/optout. > >>>> >> > >>>> >> -- > >>>> >> SQLAlchemy - > >>>> >> The Python SQL Toolkit and Object Relational Mapper > >>>> >> > >>>> >> http://www.sqlalchemy.org/ > >>>> >> > >>>> >> To post example code, please provide an MCVE: Minimal, Complete, > and > >>>> >> Verifiable Example. See http://stackoverflow.com/help/mcve for > a > >>>> >> full > >>>> >> description. > >>>> >> --- > >>>> >> You received this message because you are subscribed to the Google > >>>> >> Groups > >>>> >> "sqlalchemy" group. > >>>> >> To unsubscribe from this group and stop receiving emails from it, > send > >>>> >> an > >>>> >> email to sqlalchemy+...@googlegroups.com. > >>>> >> To post to this group, send email to sqlal...@googlegroups.com. > >>>> >> Visit this group at https://groups.google.com/group/sqlalchemy. > >>>> >> For more options, visit https://groups.google.com/d/optout. > >>>> > > >>>> > > >>>> > -- > >>>> > SQLAlchemy - > >>>> > The Python SQL Toolkit and Object Relational Mapper > >>>> > > >>>> > http://www.sqlalchemy.org/ > >>>> > > >>>> > To post example code, please provide an MCVE: Minimal, Complete, > and > >>>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a > full > >>>> > description. > >>>> > --- > >>>> > You received this message because you are subscribed to the Google > >>>> > Groups > >>>> > "sqlalchemy" group. > >>>> > To unsubscribe from this group and stop receiving emails from it, > send > >>>> > an > >>>> > email to sqlalchemy+...@googlegroups.com. > >>>> > To post to this group, send email to sqlal...@googlegroups.com. > >>>> > Visit this group at https://groups.google.com/group/sqlalchemy. > >>>> > For more options, visit https://groups.google.com/d/optout. > >> > >> -- > >> SQLAlchemy - > >> The Python SQL Toolkit and Object Relational Mapper > >> > >> http://www.sqlalchemy.org/ > >> > >> To post example code, please provide an MCVE: Minimal, Complete, and > >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full > >> description. > >> --- > >> You received this message because you are subscribed to the Google > Groups > >> "sqlalchemy" group. > >> To unsubscribe from this group and stop receiving emails from it, send > an > >> email to sqlalchemy+...@googlegroups.com <javascript:>. > >> To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > >> Visit this group at https://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
