CODEWITHSUNDEEP
mysql
300
300

Reputation: 1031

MySQL How to select date field exactly 7 days before today and for time last 1 hour

I have a query that selects records created from 1 hour in past from current time.

select ts from <table_name> where ts >= DATE_SUB(NOW(), interval 1 hour);

I can also select date before 7 days using

select count(*) from <table_name> where ts >= DATE_SUB(NOW(), interval 7 day);

How can I use these two date features to get records before 7 days from today and time 1 hour in past from current time.

For example, if the present time is 2015-11-06 10:03:00 then how can I get data for time between 2015-10-30 09:03:00 to 2015-10-30 10:03:00

I tried something like this, but it gives syntax error:

select ts from <table_name> where ts >= DATE_SUB(DATE(NOW()), INTERVAL 7 DAY), interval 1 hour)
select ts from <table_name> where ts >= DATE_SUB(NOW(), INTERVAL 7 DAY), interval 1 hour)

Upvotes: 1

Views: 6306

Answers (2)

elixenide
elixenide

Reputation: 44841

Your examples have syntax errors (too many closing parentheses )). If you want to use DATE_SUB(), you need to use it twice. To get entries between one time and another, use WHERE ... BETWEEN ... AND ...

You can use this:

SELECT ts
FROM iv_split_skill_metrics
WHERE ts BETWEEN
    DATE_SUB(
        DATE_SUB(DATE(NOW()), INTERVAL 7 DAY),
        interval 1 hour)
    AND
    DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)

Or, even better, skip DATE_SUB() entirely and just do subtraction, like this:

SELECT ts
FROM iv_split_skill_metrics
WHERE ts BETWEEN NOW() - INTERVAL 7 DAY - INTERVAL 1 HOUR
    AND NOW() - INTERVAL 7 DAY

Edit: For some reason, you edited your question after I posted this and replaced iv_split_skill_metrics with <table_name> in your question, but the examples above will work regardless. Just use the correct table and column names, of course!

Edit 2: I see now that you want entries between 7 days plus 1 hour ago and 7 days ago. I have tweaked my answer to show you how to do that.

Upvotes: 4

Alex
Alex

Reputation: 17289

Your goal is not 100% clear but just my attempt:

SELECT  ts 
FROM table_name 
WHERE ts >= DATE_ADD(DATE_ADD(NOW(), INTERVAL -7 DAY), INTERVAL -1 HOUR)
    AND ts <= DATE_ADD(NOW(), INTERVAL -7 DAY);

but form performance perspective this query would be much faster:

http://sqlfiddle.com/#!9/9edd1/2

SET @end = DATE_ADD(NOW(), INTERVAL -7 DAY);
SET @start = DATE_ADD(@end, INTERVAL -1 HOUR);

SELECT  ts 
    FROM table_name 
    WHERE ts BETWEEN @start AND @end;

Upvotes: 1

Related Questions