mysql - 根据时间戳选择一条记录

Question

我有一个表格，其中的列行填充了 Javascript 的 Date.now() 函数。列名称为 update_time，其示例值为 1571152209099。

如何为具有今天日期的所有行进行选择(仅考虑年、月和日)？

我试过类似的方法，但它让我无处可去。

Select *
from program_end
where workout_rate between 0 and 1
  AND FROM_UNIXTIME(update_time, '%YYYY-%MM-%DD') = CURDATE()

我还尝试更改时间格式:

Select *
from program_end
where FROM_UNIXTIME(update_time, '%Y-%m-%d') = CURDATE()

但是没有任何结果。感谢任何见解。

Answer 1

为了获得洞察力，请考虑:

 SELECT '1970-01-01' + INTERVAL ( 1571152209099 /1000) SECOND

返回:

_dt                         
--------------------------
2019-10-15 15:10:09.099000

我们认识到来自 JavaScipt Date.now() 的 1571152209099 值是从纪元开始(1970 年 1 月 1 日午夜 UTC)开始的整数毫秒。

还要考虑:

SELECT TIMESTAMPDIFF(SECOND,'1970-01-01 00:00','2019-10-15 00:00')*1000 AS _dt_1
     , TIMESTAMPDIFF(SECOND,'1970-01-01 00:00','2019-10-16 00:00')*1000 AS _dt_2 

返回:

        _dt_1           _dt_2
-------------  --------------
1571097600000   1571184000000

TIMESTAMPDIFF 函数以秒为单位返回差异；我们乘以 1000 得到毫秒。

我们认识到任何大于或等于 _dt_1 且小于 _dt_2 的 JavaScript Date.now() 毫秒值都会发生某个时候“在”日期 '2109-10-15'

---

因此，给定 update_time 是自纪元开始以来的毫秒数...

在查询中，我会将裸update_time 列与两个文字值进行比较:

WHERE update_time >= 1571097600000 
  AND update_time <  1571184000000

引用裸列的优点是 MySQL 可以利用有效的范围扫描操作，并提供合适的索引。

要从给定的日期值中导出这些毫秒值，我们可以在表达式中进行计算，如下所示:

WHERE update_time >= TIMESTAMPDIFF(SECOND,'1970-01-01', '2019-10-15' )*1000 
  AND update_time <  TIMESTAMPDIFF(SECOND,'1970-01-01', '2019-10-15' + INTERVAL 1 DAY)*1000
                                                         ^^^^^^^^^^

右侧的那些表达式仅在语句执行开始时计算一次。

请注意，如果我们将 update_time 包装在一个函数中，那么该函数必须针对 update_time 的每个值、表中的每一行(即“t 被其他谓词过滤掉。)