英文:
T-SQL statement for getting minute from time
问题
我正在使用pyodbc进行SQL Server连接。我尝试检索当前分钟内的所有数据行（项目），但是在minute(Time)和now()部分遇到错误。我认为我可能使用了MySQL的语法，而不是SQL Server的语法。我不确定SQL Server的语法版本是什么。
如果有人能帮助，我将不胜感激。谢谢！
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=sys;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute('SELECT item from sys.table1 where minute(Time) = minute(getdate())')
for i in cursor:
print(i)
#数据示例
#Time #Item
#2023-06-30 00:00:00 #Apple
#2023-06-30 00:01:00 #Orange
#2023-06-30 00:01:00 #Banana
#例如，现在是2023-06-30 00:01:00，输出将是Orange和Banana
英文:
I am using pyodbc for my SQL Server connection. I am trying to retrieve all the data rows (items) that belongs to the minute now (ie. real time). However I am getting an error for the minute(Time) and now(). I think i might have used the syntax for MySQL instead of SQL Server. I am not sure what is the syntax version for SQL Server.
I'll appreciate if anyone can help. Thank you!
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=sys;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute('SELECT item from sys.table1 where minute(Time) = minute(now())")')
for i in cursor:
print(i)
#example of Data
#Time #Item
#2023-06-30 00:00:00 #Apple
#2023-06-30 00:01:00 #Orange
#2023-06-30 00:01:00 #Banana
#eg now is 2023-06-30 00:01:00, the output would be Orange & Banana
答案1
得分: 1
Microsoft SQL Server中的语法是DATEPART，您需要指定一个时间间隔，所以在您的情况下可以这样使用：
SELECT DATEPART(minute, GETDATE()) as CurrentMinute
不过，这似乎不是您想要的。如果您要获取不到一分钟的记录，请使用DATEDIFF来查找它们的年龄。
例如：
SELECT *
FROM MyTable
WHERE DATEDIFF(second, [Time], GETDATE()) < 60
顺便说一下，之所以这样做是因为如果您比较记录的分钟与当前时间的分钟，那么只有分钟部分匹配的所有事件都会匹配，而不管其他部分是否匹配。因此，如果您在10:29运行查询，您将获得从今天早上8:29 AM、昨天晚上10:29 PM等的记录。
英文:
The syntax in Microsoft SQL Server is DATEPART and you specify an interval, so in your case
SELECT DATEPART(minute, GETDATE()) as CurrentMinute
However, that doesn't sound like what you want. If you're trying to get the records that are less than a minute old, use DATEDIFF to find their age.
For example
SELECT *
FROM MyTable
WHERE DATEDIFF(second, [Time], GETDATE()) < 60
EDIT: BTW, the reason why is if you compare the minute of the record to the minute of the current time, you will get all occurrences where just the minute part matches regardless of what other parts don't match. So if you ran the query at 10:29, you'd get entries from 8:29 AM this morning, 10:29 PM yesterday, etc.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论