CODEWITHSUNDEEP
pythonsqloracle-databasesqlalchemy
Fotis Papadamis
Fotis Papadamis

Reputation: 303

Comparing timestamp to date in Python SQLAlchemy

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))

Upvotes: 1

Views: 4696

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52939

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)

Upvotes: 3

Related Questions