当前位置: 动力学知识库 > 问答 > 编程问答 >

MySQL "day" - how do I get an exact figure?

问题描述:

I am using this function to filter query results that are older than 60 days:

s.timeSubmitted >= ( CURDATE() - INTERVAL 60 DAY )

The problem is, the "60 days" part doesn't seem to be an exact figure. I want it to filter right where s.timeSubmitted is longer than 60 days, down to the exact second of s.timeSubmitted.

How do I write "60 Days" as an exact figure (down to the second)?

网友答案:

The problem is that CURDATE() returns a DATE type, not a DATETIME type (an instant in time). The result of subtracting an interval from a DATE is also a DATE.
Instead, try this:

s.timeSubmitted >= ( NOW() - INTERVAL 60 DAY )

This gives you what you want, because NOW() returns a DATETIME, so the result of the subtraction is also a DATETIME.

网友答案:

INTERVAL 60 DAY is exact - your problem is that CURDATE() isn't. It returns whole days, not the current time.

Use NOW() instead!

网友答案:

I usually do

now()-interval 60 day
网友答案:

Assuming you want the same time of day 60 days ago;

s.timeSubmitted >= ( now() - interval 60 day);

Maybe an un-necessary note in this case; 1 day ago may be 23, 24 or 25 hours ago depending on DST changes, if you want a specific number of hours as an interval, don't use a day instead of 24 hours.

分享给朋友:
您可能感兴趣的文章:
随机阅读: