时间戳

首页 > 解决方案 > MS SQL Python 日期时间晚于但不等于 '>' 返回结果为 '>='

python - MS SQL Python 日期时间晚于但不等于 '>' 返回结果为 '>='

问题描述

我正在尝试查询 Microsoft SQL 数据库以获取上一个循环中返回的日期时间之后的所有记录。我使用“>”运算符，但我看到的结果是“>=”（大于或等于）。

Python（3.6.5 Win10）脚本：

import pyodbc  # version 4.0.24
import time
import datetime
import json
con = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=mydb;UID=myuid;PWD=mypwd")
cur = con.cursor()
i = 0
lastDatetime = datetime.datetime.strptime('01Jan1970', '%d%b%Y')
while i < 60:
    print("{0}: SELECT * FROM dbo.Data WHERE datetime > '{1}'".format(str(i), lastDatetime))
    cur.execute("SELECT * FROM dbo.Data WHERE datetime > ?", lastDatetime)
    rows = cur.fetchall()
    for k, v in enumerate(rows):
        jsonMsg = json.dumps({
            "transaction_id": v[1],
            "plu": v[2].strip(),
            "qty": int(v[3]),
            "dateTime": str(v[4])
        }, separators=(',', ':'))
        print(str(jsonMsg))
        lastDatetime = v[6]
        print("lastDatetime set to: {0}".format(lastDatetime))
    i = i + 1
    time.sleep(5)

产生：

0: SELECT * FROM dbo.Data WHERE datetime > '1970-01-01 00:00:00'
{"transaction_id":3201,"plu":"3","qty":1,"dateTime":"2018-08-01 10:45:40.560000"}
lastDatetime set to: 2018-08-01 10:45:40.560000
{"transaction_id":3202,"plu":"5","qty":1,"dateTime":"2018-08-01 10:45:51.563000"}
lastDatetime set to: 2018-08-01 10:48:47.653000
{"transaction_id":3230,"plu":"8","qty":2,"dateTime":"2018-08-01 10:48:47.653000"}
lastDatetime set to: 2018-08-01 10:48:47.653000
1: SELECT * FROM dbo.Data WHERE datetime > '2018-08-01 10:48:47.653000'
{"transaction_id":3230,"plu":"8","qty":2,"dateTime":"2018-08-01 10:48:47.653000"}
lastDatetime set to: 2018-08-01 10:48:47.653000
2: SELECT * FROM dbo.Data WHERE datetime > '2018-08-01 10:48:47.653000'
{"transaction_id":3230,"plu":"8","qty":2,"dateTime":"2018-08-01 10:48:47.653000"}
lastDatetime set to: 2018-08-01 10:48:47.653000

我在 SQL Server Management Studio 中运行查询，得到了预期的结果：

SELECT * FROM dbo.Data WHERE datetime > '2018-08-01 10:48:47.653'

不返回任何记录。

我缺少什么或我应该在 python 脚本中更改什么以获得预期的结果？

谢谢。

编辑：将 '' 添加到数据时间周围的 sql 字符串的打印中。对结果没有影响。

标签： pythonsqlsql-serverpython-3.x

解决方案

您正在达到此处指出的 SQL 2016+ 的重大更改：

在数据库兼容级别 130 下，从 datetime 到 datetime2 数据类型的隐式转换通过考虑小数毫秒来显示提高的准确性，从而导致不同的转换值。只要存在 datetime 和 datetime2 数据类型之间的混合比较方案，就使用显式转换为 datetime2 数据类型。有关详细信息，请参阅此Microsoft 支持文章。

SQL Server 2016 中的重大更改

问题是您的表仍在使用旧datetime数据类型。这是一种非常奇怪的类型。它没有毫秒分辨率。它的分辨率实际上是 1/300 秒。所以实际存储的值并不总是看起来像的那样。存储不精确，例如浮点数。

新的更好datetime2的精度更高，并且始终存储十进制小数秒。无论如何，SQL 2016 改变了如何datetime转换为datetime2，这具有使涉及隐式转换的比较有时会中断的副作用。

要解决此问题，请将表上的数据类型更改为 datetime2(3) 以获得 1 毫秒的精度。或强制参数为日期时间，而不是日期时间2。我不知道 pyodbc 是否允许这样做，但您可以轻松地在查询中强制转换：

cur.execute("SELECT * FROM dbo.Data WHERE datetime >= cast(? as datetime)", lastDatetime)

现在，不是将表列转换为，datetime2而是将参数值转换为datetime，并且比较按预期工作。

或者将您的数据库兼容级别降低到 120。

推荐阅读