我在MariaDB中使用日期包括毫秒精度的between条件得到了不同的结果
我在MariaDB中使用日期包括毫秒精度的between条件得到了不同的结果
提问于 2020-11-04 00:46:11
我得到了下面的Sales表，其中包含列productName Varchar(50)和saleDate bigint(20)。
假设它存储了100条记录。
示例：
productName saleDate
----------- ------------
TV 1601555334694
Radio 1603585354888
...saleDate列存储在时间戳号中，日期的精度为毫秒。然后，我运行以下查询来获得2020年10月的销售数量：
-- This returns a result of 70
SELECT COUNT(*)
FROM (
SELECT productName,
DATE_FORMAT(SUBSTRING(DATE_ADD((FROM_UNIXTIME(SUBSTRING(saleDate, 1, 10)) -
INTERVAL (10+5*60) MINUTE), INTERVAL SUBSTRING(saleDate, 11, 13) SECOND_MICROSECOND),1,23), '%Y-%m-%d 00:00:00') AS saleDate
FROM Sales
) s
WHERE s.saleDate between '2020-10-01 00:00:00' and '2020-10-31 23:59:59.999'
-- This returns a result of 20
SELECT COUNT(*)
FROM (
SELECT productName,
DATE_FORMAT(SUBSTRING(DATE_ADD((FROM_UNIXTIME(SUBSTRING(saleDate, 1, 10)) -
INTERVAL (10+5*60) MINUTE), INTERVAL SUBSTRING(saleDate, 11, 13) SECOND_MICROSECOND),1,23), '%Y-%m-%d 00:00:00') AS saleDate
FROM Sales
) s
WHERE s.saleDate between '2020-10-01 00:00:00.000' and '2020-10-31 23:59:59.999'在这两个查询中，我减去了5个小时的日期，但减少了10分钟。这是系统要求。
因此，当我用.000过滤开始日期时，结果会发生变化。70的结果不应该是一样的吗？
我使用的是Mariadb 10.2.13
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-11-04 01:22:39
我建议将间隔界限转换为unix时间戳，而不是相反。这更简单，效率也更高：where谓词是SARGable (这意味着它可以利用saledate上的索引)，而在原始查询中，需要转换整个列，然后才能对其进行过滤。
此外，使用半开放间隔可节省处理后续毫秒的时间。
所以：
select count(*)
from sales
where saledate >= unix_timestamp('2020-10-01') * 1000
and saledate < unix_timestamp('2020-11-01') * 1000如果你想偏移5小时10分钟，那么这是一个简单的技巧：
select count(*)
from sales
where saledate >= (unix_timestamp('2020-10-01') + 5 * 60 * 60 + 10 * 60) * 1000
and saledate < (unix_timestamp('2020-11-01') + 5 * 60 * 60 + 10 * 60) * 1000Stack Overflow用户
发布于 2020-11-04 01:34:07
问题是您的DATE_FORMAT()格式字符串不包括毫秒。因此，如果saleDate的值恰好是2020-10-01 00:00:00，它将不满足BETWEEN条件，因为2020-10-01 00:00:00在字典顺序上不高于2020-10-01 00:00:00.000。
将毫秒添加到格式字符串'%Y-%m-%d 00:00:00.000'中，或从BETWEEN中使用的时间中删除毫秒。
Stack Overflow用户
发布于 2020-11-04 01:20:11
只是想一想..。
SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
SELECT 99-i j FROM ints ORDER BY i;
+----+
| j |
+----+
| 99 |
| 98 |
| 97 |
| 96 |
| 95 |
| 94 |
| 93 |
| 92 |
| 91 |
| 90 |
+----+
SELECT 99-i i FROM ints ORDER BY i;
+----+
| i |
+----+
| 90 |
| 91 |
| 92 |
| 93 |
| 94 |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
+----+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64667039
复制相关文章
相似问题
腾讯云开发者
