Get records where where timestamp is not today's date

Question

I have code as follows to get todays date:

today = str(date.today()) //2019-01-07

I want to get all records from the database where timestamp column is not as today. The function is as follows:

def get_vehicle_references():
  conn = db_connection()
  cur = conn.cursor()
  s = "SELECT reference, reference_url FROM vehicles v WHERE (NOT EXISTS (select reference from daily_run_vehicle rv WHERE (handled = %s or (handled = %s and retries >= %s)) and DATE(rv.timestamp) <> %s AND reference = v.reference))"
  cur.execute(s, (True, False, 5, today))
  return cur.fetchall()

The timestamp column is in following format: 2019-01-07 11:10:18.491368

I want to get all records from vehicles table where timestamp column in daily_run_vehicle table is not today.

But this query gives me also records with todays date.

Any idea?

UPDATE:

Thus I tried to cast it, and the query is as follows:

s = "SELECT reference, reference_url, timestamp FROM vehicles v WHERE (NOT EXISTS (select reference from daily_run_vehicle rv WHERE (handled = %s or (handled = %s and retries >= %s)) AND rv.timestamp::date = %s AND reference = v.reference))"
cur.execute(s, (True, False, 5, today))

But I still have the same problem.

You need to compare against the date portion, see the duplicate on how to get that portion. — Martijn Pieters, Jan 07 '19 at 12:18
You need to be explicit as to what happens instead. Try to narrow down the issue, only focus on the subselect, for example. — Martijn Pieters, Jan 07 '19 at 12:54
it's locked but from doublicated answer, try this: `WHERE vehicles. timestamp::timestamp::date != clock_timestamp()::timestamp::date` it should work — confiq, Oct 21 '21 at 13:54

Get records where where timestamp is not today's date

0 Answers0