how to execute the sql query for 1 week data.

Hi Experts,

I have the below query to get the Details table ids.
here i am passing inputs as timestamp in secs.
i want to execute the below query for last one week data

date is  #Sun Aug 09 20:52:53 IST 2015

Monday Start 1438540200 End 1438626599 -->start (1438540200) 8/3/2015, 12:00:00 AM  end-->8/3/2015, 11:59:59 PM
Tuesday Start 1438626600 End 1438712999
Wednesday Start 1438713000 End 1438799399
Thursday Start 1438799400 End 1438885799
Friday Start 1438885800 End 1438972199
Saturday Start 1438972200 End 1439058599
Sunday Start 1439058600 End 1439144999

SELECT  DTL_ID FROM `DTL_TBL` `t` WHERE IFNULL(t.DTL_ID,0) != 0 AND  UNIX_TIMESTAMP(t.DOWN_TIME) BETWEEN '1438540200' AND '1438712999' GROUP BY t.DTL_ID

In the above query i am passing inputs for eachday monday onetime tuesday one time ------7times i am executing the query

is it possible to execute the below query at one shot.

Thanks,
LVL 2
srikoteshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
This will display the day of week and detail id for the previous week:
select day_name(t.down_time), dtl_id
from dtl_tabl t
WHERE  IFNULL(t.DTL_ID,0) != 0
and t.down_time BETWEEN DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY)
    AND DATE_ADD(CURDATE(), INTERVAL 7-DAYOFWEEK(CURDATE()) DAY)
0
srikoteshAuthor Commented:
Hi Mark,

I am getting only last two date data only can you verify the query

i have executed the below queries :
first query is fine but second one something went wrong please verify it

mysql> select DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY);
+----------------------------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY) |
+----------------------------------------------------------+
| 2015-08-09                                               |
+----------------------------------------------------------+

select DATE_ADD(CURDATE(), INTERVAL 7-DAYOFWEEK(CURDATE()) DAY);

2015-08-15
0
Mark BullockQA Engineer IIICommented:
Running these on Aug 15 you get
select DATE_ADD(CURDATE(), INTERVAL 6-DAYOFWEEK(CURDATE()) DAY); -- Aug 14
select DATE_ADD(CURDATE(), INTERVAL 0-DAYOFWEEK(CURDATE()) DAY); -- Aug 8
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

srikoteshAuthor Commented:
I need last one week data
why future date required.
date between August 11 to August 4.
0
Mark BullockQA Engineer IIICommented:
Sorry. My mistake.
select DATE_ADD(CURDATE(), INTERVAL -0 DAY);  --Aug 11
select DATE_ADD(CURDATE(), INTERVAL -7 DAY);  --Aug 4
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.