0

The following query in SQL Developer returns 1 record:

SELECT * FROM myview WHERE to_date(acq_date_time) = to_date(CURRENT_DATE)

However, when I execute the same query in Python using cx_Oracle, I get no records:

query = "SELECT * FROM myview WHERE to_date(acq_date_time) = to_date(CURRENT_DATE)"
cursor.execute(query)
results = cursor.fetchall()

More puzzling, is that when I use this query I get 5 results (the one I expect from today plus all from the previous day):

query = "SELECT * FROM myview WHERE to_date(acq_date_time) > to_date(CURRENT_DATE - 1)"
cursor.execute(query)
results = cursor.fetchall()

I have verified the connection address in both SQL Developer and cx_Oracle are the same. I'm not getting any errors. Any ideas why this is happening? Do SQL Developer and cx_Oracle get the CURRENT_DATE from different places?

Kes Perron
  • 455
  • 5
  • 12
  • 24
  • it may be a matter of DATE formatting. Try using the TO_DATE function with the format String, using the date format models. See here https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924 – Massimo Petrus Aug 24 '20 at 17:05
  • 1
    Why are you using `to_date()` with something that is already a date? That is redundant, and going to cause you problems with different NLS settings, causing errors or wrong results. It isn't clear if that is the problem (or part of it) here; more likely the SQL Developer and Python sessions are starting with different locales, and [`current_date` uses the session timezone](https://stackoverflow.com/a/17925834/266304). What does `select sessiontimezone from dual` show in both? I imagine one will be -04:00 and the other will be something different, maybe UTC? – Alex Poole Aug 24 '20 at 17:26

1 Answers1

2

From the documentation:

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of data type DATE.

The important part there is in the session time zone. Your SQL Developer and Python sessions seem to be in different time zones. You can check what they have with

select sessiontimezone from dual

You have a further problem in that you are doing to_date(CURRENT_DATE), which implicitly converts the date to a string and then explicitly converts that back to a date, both using the session's NLS_DATE_FORMAT, which may also be different. That may do what you want, sometimes, but will break one day when your code is run in a session with a setting you weren't expecting. That could include ending up in the year 0020 instead of 2020, or keeping the time part when you didn't want to.

Presumably you're trying to remove (or rather, zero) the time portion, but if that is the intent then you should do trunc(CURRENT_DATE) instead. You still need the session time zone to be correct, of course - i.e. matching the data you are comparing it with. (And depending on the data type and how it is populated you might actually want SYSDATE rather than CURRENT_DATE; read about the difference.)

Truncating the view column can have a performance impact as it could prevent an index on the column (of the underlying table, depending on what the view is doing) being used, so - assuming the right time zone, again - you may be better off doing:

WHERE acq_date_time >= trunc(CURRENT_DATE)
Alex Poole
  • 183,384
  • 11
  • 179
  • 318