英文:
Postgres NOW() returns incorrect time when called inside asyncio task
问题
Your code appears to log the time correctly, but Postgres is returning a different time. This time discrepancy could be due to time zone settings or issues with the database connection.
To address this issue, consider the following steps:
-
Check Time Zone Settings: Verify the time zone settings in your Postgres database. Ensure that it's set to the same time zone as your Ubuntu system. You can set the time zone in Postgres using SQL commands like
SET TIME ZONE. -
Database Connection: Make sure your database connection is using the correct time zone. You can set the time zone for the database session when connecting to Postgres. For example:
self.connection = psycopg2.connect(database_url, options="-c timezone=your_timezone")Replace
your_timezonewith the appropriate time zone name. -
Docker Container Time Zone: Ensure that the time zone in your Docker container matches the time zone of your host machine. You can set the container's time zone using environment variables or Docker Compose settings.
-
Python Application Time Zone: Check the time zone settings in your Python application. Make sure it's consistent with your system's time zone. You can set the time zone in Python using
datetimeas follows:import datetime import pytz # Import pytz library # Set the time zone your_timezone = pytz.timezone('Your/Timezone') now = datetime.datetime.now(your_timezone) -
Debugging: If the issue persists, consider adding more debugging statements to track the time zone at different points in your code to identify where the time difference is introduced.
By ensuring that all components (Postgres, Docker, Python) are using the same time zone settings, you should be able to resolve the time discrepancy issue.
英文:
My code runs once an hour and logs the time correctly, but for some reason postgres HOW() returns the wrong time.
The code looks something like this:
async def run():
await before_startup()
# ...
async def before_startup() -> None:
loop = asyncio.get_event_loop()
loop.create_task(interval_handler())
async def interval_handler():
while True:
logger.info("Handler:", datetime.datetime.now())
for data in process_validity():
pass
# Wait to the next hour
delta = datetime.timedelta(hours=1)
now = datetime.datetime.now()
next_hour = (now + delta).replace(microsecond=0, second=0, minute=0)
wait_seconds = (next_hour - now).seconds
await asyncio.sleep(1) # Ensure unique per interval
await asyncio.sleep(wait_seconds)
class Singleton(type):
_instances = {}
def __call__(cls, *args, **kwargs):
if cls not in cls._instances:
cls._instances[cls] = super(
Singleton, cls).__call__(*args, **kwargs)
return cls._instances[cls]
class Database(metaclass=Singleton):
def __init__(self):
self.connection = None
def connect(self):
self.connection = psycopg2.connect()
def fetchmany(self, query: str, per: int, params: tuple | dict = None):
cursor = self.connection.cursor(cursor_factory=RealDictCursor)
cursor.execute(query, params)
while True:
result = cursor.fetchmany(per)
yield result
if not result:
cursor.close()
break
db = Database()
db.connect()
def process_validity() -> Generator[DataInterface, None, None]:
db_now: datetime.datetime = db.fetchone("SELECT NOW()")['now'].replace(tzinfo=None)
logger.info("NOW() is:", db_now)
for users_set in db.fetchmany("SELECT ...", 100, ()):
for user in users_set:
yield user
if __name__ == "__main__":
asyncio.run(run())
The logs looks like this:
2023-05-20 15:00:00 +0000 INFO Handler: 2023-05-20 15:00:00.775156
2023-05-20 15:00:00 +0000 INFO NOW() is: 2023-05-20 13:49:35.873942
Note that the logger and datetime.datetime.now() get the time correctly (15:00), while Postgres returns the wrong one (13:49). What could be the problem? Over time, the gap widens.
Also, I connect via psql to the container and get the correct time; the correct time is obtained by connecting via pycharm too. In addition, after the bot is launched, the task is processed immediately, and at that moment everything is correct. Then the delta increases.
My environment: Ubuntu 20.04.5 LTS (GNU/Linux 5.4.0-137-generic x86_64), Docker version 20.10.23, build 7155243, postgres:15.1-alpine, python 3.11.
答案1
得分: 0
解决了评论中的答案，并得到以下结果：
- 使用 psycopg2 连接池，并编写一个类来创建一个上下文，其中包含一个连接，该连接在一个或一组事务或选择语句后会自动关闭。
- 使用 CURRENT_TIMESTAMP 函数。
英文:
Solved the problem with the answers in the comments and the following as a result:
- Use psycopg2 connection pool and write a class to create a context with a connection that would close automatically after one or set of transactions or select statements
- Use CURRENT_TIMESTAMP function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论