首页 > python > 比较Python SQLAlchemy中的时间戳和日期

比较Python SQLAlchemy中的时间戳和日期 (Comparing timestamp to date in Python SQLAlchemy)

问题

我使用sqlalchemyPython中有一个API 。

我有一个字符串,表示ISO格式的日期。我用它转换它datetime.strptimedatetime.strptime(ToActionDateTime, '%Y-%m-%dZ')

现在我必须将表的列的值timestamp与该日期进行比较。转换初始ISO字符串后,示例结果如下所示2018-12-06 00:00:00。我必须根据日期而不是时间来比较它的平等性,但我无法做到正确。任何帮助,将不胜感激。

示例Python代码:

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

编辑:

我也试图实现cast等式的两个部分,但它也不起作用。当所选日期与时间戳的日期匹配时,我无法获得正确的结果。

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

解决方法

由于Oracle DATE数据类型实际上存储了日期和时间,因此强制转换DATE不会消除其时间部分的值,就像在大多数其他DBMS中一样。相反,该函数TRUNC(date [, fmt])可用于仅将值减少到其日期部分。在其单个参数形式中,它截断到最近的一天,或者换句话说'DD'用作默认模型:

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

如果使用2个参数的形式,则精度说明为天精度是任一'DDD''DD''J'

但是这个解决方案隐藏了ActionDateTime可能的索引列。要使查询索引友好,将日期增加ToActionDateObj一天并使用小于比较:

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

问题

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

解决方法

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)
相似信息