Comparing timestamp to date in Python SQLAlchemy

Question

I have an API in Python using sqlalchemy.

I have a string which represents a date in ISO format. I convert it using datetime.strptime like so: datetime.strptime(ToActionDateTime, '%Y-%m-%dZ').

Now I have to compare the value of a table's column which is a timestamp to that date. After converting the initial ISO string, an example result looks like this 2018-12-06 00:00:00. I have to compare it for equality depending on date and not time but I can't manage to get it right. Any help would be appreciated.

Sample Python code:

ToActionDateTimeObj = datetime.strptime(ToActionDateTime, '%Y-%m-%dZ')
query = query.filter(db.c.Audit.ActionDateTime <= ToActionDateTimeObj)

Edit:

I have also tried to implement cast to both parts of the equation but it does not work either. I can't manage to get the right result when the selected date matches the date of the timestamp.

  from sqlalchemy import Date, cast
  ToActionDateTimeObj = datetime.strptime(ToActionDateTime, '%Y-%m-%dZ')
  query = query.filter(cast(db.c.Audit.ActionDateTime, Date) <= cast(ToActionDateTimeObj, Date))

I had already tried cast sorry for forgetting to mention it. It does not work. I also tried to cast both the column and the object, if I print the casted object what I'm getting is CAST(:param_1 AS DATE) — Fotis Papadamis
– Fotis Papadamis, Commented Dec 18, 2018 at 10:06
I'm using Oracle. My first thought was to increment by 1 day but I got asked by my supervisor to not use that walkaround. — Fotis Papadamis
– Fotis Papadamis, Commented Dec 18, 2018 at 10:33
When I cast the timestamp in Oracle SQL Developer it returns just the date like '11-NOV-2018'. — Fotis Papadamis
– Fotis Papadamis, Commented Dec 18, 2018 at 10:39
It throws this: cx_Oracle.DatabaseError: ORA-01899: bad precision specifier But I don't want to compare only the day. I need to compare the whole date. — Fotis Papadamis
– Fotis Papadamis, Commented Dec 18, 2018 at 10:50

Ilja Everilä · Accepted Answer · 2018-12-19 08:44:55Z

3

Since Oracle DATE datatype actually stores both date and time, a cast to DATE will not rid the value of its time portion, as it would in most other DBMS. Instead the function TRUNC(date [, fmt]) can be used to reduce a value to its date portion only. In its single argument form it truncates to the nearest day, or in other words uses 'DD' as the default model:

ToActionDateObj = datetime.strptime(ToActionDateTime, '%Y-%m-%dZ').date()
...
query = query.filter(func.trunc(db.c.Audit.ActionDateTime) <= ToActionDateObj)

If using the 2-argument form, then the precision specifier for day precision is either 'DDD', 'DD', or 'J'.

But this solution hides the column ActionDateTime from possible indexes. To make the query index friendly increment the date ToActionDateObj by one day and use less than comparison:

ToActionDateObj = datetime.strptime(ToActionDateTime, '%Y-%m-%dZ').date()
ToActionDateObj += timedelta(days=1)
...
query = query.filter(db.c.Audit.ActionDateTime < ToActionDateObj)

edited Dec 19, 2018 at 8:44

answered Dec 18, 2018 at 19:54

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fotis Papadamis Over a year ago

I got asked to avoid incrementing by one day. I managed to solve the issue, I just have to wait two days before marking the question as answered, you can take a look at it.

Ilja Everilä Over a year ago

It'd be interesting to hear why you got asked, since it is the proper solution, if you want to use an existing index on ActionDateTime, as opposed to creating a function-based index. Your own solution suffers from the same, i.e. the column is hidden from possible existing indexes. Also you should not really (man)handle dates/datetimes as text, especially when proper methods such as TRUNC() exist.

Fotis Papadamis Over a year ago

I don't know why I got asked to do so, my first thought was to increment by one day but since this got turned down I had to find another way. I appreciate your reply, you really filled me in about things I didn't take into consideration, I used your first solution since it's better than mine and missing indexes does not affect the functionality I'm aiming for.

Collectives™ on Stack Overflow

Comparing timestamp to date in Python SQLAlchemy

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related