如何在针对 unixtimestamp (bigint) 列的 where 子句中使用 curdate() [英] how to use curdate() in where clause against unixtimestamp (bigint) column

问题描述

我正在尝试提取过去 7 天的记录.这是我一直在努力工作的选择语句:

I am trying to pull records in the past 7 days. This is my select statement that I have been trying to get to work:

select from_unixtime(time,'%m/%d/%y') as fdate, from_unixtime(time,'%h:%m:%s') as ftime
from mdl_log
where from_unixtime(time,'%y-%m-%d')  between curdate() and curdate() - INTERVAL 7 DAY

我尝试了 where 子句的各种化身

I have tried various incarnations of the where clause like

where time between curdate() and curdate() - INTERVAL 7 DAY

和

where from_unixtime(time,'%yyyy-%mm-%dd')  between curdate() and curdate() - INTERVAL 7 DAY

和

where date(time) between curdate() and curdate() - INTERVAL 7 DAY

select curdate() - 结果以这种格式显示日期 2012-11-08

select curdate() - results in the date showing in this format 2012-11-08

推荐答案

您的最后一次转世就快到了.但是，您需要将苹果与苹果进行比较.由于time 是整数，您需要将其转换为要使用的 MySQL 日期/时间函数.

You were almost there with your last incarnation. However, you need to compare apples to apples. Since time is an integer, you need to convert it for MySQL date/time functions to use.

WHERE DATE(FROM_UNIXTIME(time)) between CURDATE() and CURDATE() - INTERVAL 7 DAY

鉴于您的用例，您真的只需要 FROM_UNIXTIME():

Given your use case, you really only need FROM_UNIXTIME():

WHERE FROM_UNIXTIME(time) between CURDATE() and CURDATE() - INTERVAL 7 DAY

这篇关于如何在针对 unixtimestamp (bigint) 列的 where 子句中使用 curdate()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！