Answer the question
In order to leave comments, you need to log in
How to optimize sqlite datetime query for postgresSQL date?
Hello!
If everything is pretty clear in Sqlite, then Postgres has very laborious documentation.
Please help those who know how to make a similar function on posgresSQL:
There is a given function in sqlite used datetime. But unfortunately it doesn't work in postgre, because date has its own limitations, but is it possible to set them up in the same way? So that a certain point in time is counted from the current date
import psycopg2
def get_records(self, user_id, within="all"):
"""Получаем историю о заказах"""
if (within == "day"):
self.cursor.execute("""SELECT * FROM orders WHERE user_id = %s AND order_date BETWEEN date('now', 'start of day') AND date('now', 'localtime') ORDER BY order_date""",
(str(self.get_user_id(user_id)),))
elif (within == "week"):
self.cursor.execute("""SELECT * FROM orders WHERE user_id = %s AND order_date BETWEEN date('now', '-6 days') AND date('now', 'localtime') ORDER BY order_date""", (str(self.get_user_id(user_id)),))
elif (within == "mounth"):
self.cursor.execute("""SELECT * FROM orders WHERE user_id = %s AND order_date BETWEEN date('now', 'start of month') AND date('now', 'localtime') ORDER BY order_date""",(str(self.get_user_id(user_id)),))
else:
self.cursor.execute("""SELECT * FROM orders WHERE user_id = %s ORDER BY order_date""", (str(self.get_user_id(user_id)),))
Answer the question
In order to leave comments, you need to log in
Current time - now()
date_trunc() - date rounding
-- date('now', 'start of day')
date_trunc('day', now())
-- date('now', 'start of month')
date_trunc('month', now())
-- date('now', '-6 days')
now() - INTERVAL '6 days'
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question