0

I am creating a report where I have to show last 7 days data and previous 7 days data. But I can fetch last 7 days data but for previous 7 days I don't know how to write the sql!

Suppose today is 14th March. I am fetching data from database for last 7 days which is 8th March to 14th March is,

WHERE my_date >= DATE_ADD(NOW(), INTERVAL -7 DAY)

But how to write the sql for previous 7 days? Which is 1st March to 7th March. I have tried this,

WHERE my_date BETWEEN DATE_SUB(NOW(),INTERVAL 7 DAY) and NOW()

I don't think it's working! How am I going to get data for 1st March to 7th March and skip 8th March to 14th March?

Bearded
  • 45
  • 8
  • Does this answer your question? [MySQL - select data from database between two dates](https://stackoverflow.com/questions/8458290/mysql-select-data-from-database-between-two-dates) – adampweb Mar 17 '22 at 06:49
  • No, its saying between two dates but I want previous 7 days and skip last 7 days. – Bearded Mar 17 '22 at 06:57
  • Provide an example as CREATE TABLE + INSERT INTO and desired output (for 2, not 7, days, for to decrease the source rows amount needed). – Akina Mar 17 '22 at 07:01
  • Does `WHERE my_date CURRENT_DATE - INTERVAL 14 DAY AND CURRENT_DATE - INTERVAL 8 DAY` solves? – Akina Mar 17 '22 at 07:02
  • Do I have to put any operator between `my_date [ HERE ] CURRENT_DATE - INTERVAL 14 DAY AND CURRENT_DATE - INTERVAL 8 DAY `? if no then it's generating error. – Bearded Mar 17 '22 at 07:11
  • @Alaindeseine answer is working perfectly. Do I have to edit my question anymore? – Bearded Mar 17 '22 at 07:41
  • What type is my_date? – ysth Mar 17 '22 at 08:23
  • @ysth `timestamp` – Bearded Mar 17 '22 at 10:15
  • ah, then the question is do you want calendar days or exactly 14 days ago from the time it is now to a week later than that? if calendar days, use current_date, otherwise use now() – ysth Mar 17 '22 at 19:49

1 Answers1

1

Have you try this :

WHERE my_date BETWEEN DATE_SUB(NOW(),INTERVAL 14 DAY) and DATE_SUB(NOW(),INTERVAL 7 DAY)
Alaindeseine
  • 3,260
  • 1
  • 11
  • 21