asked on

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,

Mark Bullock

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)

srikotesh

ASKER

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

Mark Bullock

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

srikotesh

ASKER

I need last one week data
why future date required.
date between August 11 to August 4.

ASKER CERTIFIED SOLUTION

Mark Bullock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srikotesh

ASKER

thanks