不精确搜索最近的时间值
我正在寻找一种在编程和语法上有效的方法来搜索基于时间戳的值。本质上，我想找到最接近的时间戳匹配...
例如，在我有的 MySQL 表中：
ID TIME Blob
1 4:03:10 abc
2 4:04:30 def
3 4:04:45 ghi
我想根据时间 4:04:40 查询该表。我想返回记录 ID #3...如果我搜索 4:04:35 我想返回 ID #2...我该如何实现这个？我在这个表中有数百万行，我在想像 levenshtein dist 这样的东西会太慢..？
干杯!
I'm looking for a programmatically and syntactically efficient way to go about searching for a value based on a Timestamp. Essentially I want to find the closest timestamp match...
Say for example in a MySQL table I have:
ID TIME Blob
1 4:03:10 abc
2 4:04:30 def
3 4:04:45 ghi
And I want to query this table based on the time 4:04:40. I would want to return record ID #3... If I searched for 4:04:35 I would want to return ID #2... How do I go about implementing this? I have many millions of rows in this table and I was thinking something like levenshtein dist will be too slow..?
Cheers!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
怎么样：
How about:
请耐心等待，我更习惯 Oracle，如果需要，可以像数字一样操作日期，但我认为您应该查找日期与搜索日期差异最小的行。差异可能是正数，也可能是负数，因此需要 ABS() 来解决这个问题。
怎么样？
如果看起来不错，那么您必须选择[差异]最小的 ID
Bear with me, I'm more used to Oracle where dates can be manipulated exactly like numbers if necessary, but I think you should look for the row that has the date with the smallest difference to your search date. The difference could be positive or negative so ABS() will be needed to take care of that.
How about
If that looks OK you then have to select the ID that has the minimum [difference]
SELECT MIN(time) FROM 表 WHERE TIME > [日期时间.现在]
SELECT MIN(time) FROM table WHERE TIME > [datetime.now]