Help! Code to Determine date?

[Morbo]

Hi guys, ive been using code to extract info from a database at the end of each week, and it works fine. the only issue is i have to go in at the end of every week and change the dates before i run the code, i would love to have it automated so that it would pick up that i needed the info from the past 7 days...if that makes sense?! Any help would be awesome, thanks..

 

heres the code that im using

 

SELECT

(SUM(t1.timespent) /60) ,

(SUM(t1.timebillable) /60 ) ,

MAX(t4.title) ,

FROM_UNIXTIME(t1.dateline)

FROM swtickettimetrack AS t1

LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid)

LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid)

LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid)

LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid)

WHERE t1.dateline >= UNIX_TIMESTAMP('2008-07-01') and t1.dateline <= UNIX_TIMESTAMP('2008-07-31') AND t4.title<>''

GROUP BY t4.usergroupid

[accident]

 

Switch the WHERE statement with this one

 

WHERE t1.dateline >= CURDATE() and t1.dateline < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND t4.title<>''

[Morbo]

Thanks! if i wanted to cahnge this to a week, it should be

 

WHERE t1.dateline >= CURDATE() and t1.dateline < DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND t4.title<>''

 

should it not? Ive tried it and it doesnt seem to work

 

Thanks for the code though, its works fine

 

Cheers

[accident]

oh sorry I was basing it off of your where statement where it shows 1 month

If you run it at end of week for last 7 days try

 

WHERE t1.dateline <= CURDATE() and t1.dateline >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND t4.title<>''

 

 

[Morbo]

for some reason both of these sets of code are bring back results that are much older than a month or a week, some as old as 2007  ???

 

Am i doing something wrong?  Heres how the code is set out now....cheers for your help if you can give any!

 

SELECT

  (SUM(t1.timespent) /60) ,

  (SUM(t1.timebillable) /60 ) ,

  MAX(t4.title) ,

  FROM_UNIXTIME(t1.dateline)

FROM swtickettimetrack AS t1

LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid)

LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid)

LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid)

LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid)

WHERE t1.dateline >= CURDATE() and t1.dateline < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND t4.title<>''

GROUP BY t4.usergroupid

[Morbo]

i also got the following code from a website

 

WHERE t1.dateline >= UNIX_TIMESTAMP(DATE(CONCAT(YEAR(CURDATE()),'-',MONTH(CURDATE()),'-1')))

AND t1.dateline <= UNIX_TIMESTAMP(LAST_DAY(CURDATE()))

 

but it doesnt work either

 

Im in over my head here.....

[Barand]

In what format is your date stored in the table "swtickettimetrack"?

[Morbo]

Its setup as an Integer YYYY-MM-DD

[Barand]

try

WHERE DATE(FROM_UNIXTIME(t1.dateline)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() AND t4.title<>''

[Morbo]

HI thanks for that, that line of code is bringing back a syntax error 

 

"You have an error in your SQL syntax near '(FROM_UNIXTIME(t1.dateline)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() AND' at line 13"

 

sorry but i know next to nothing about mySQL, i have only been using it for about a week

 

thanks for all your help

[fenway]

What version of mysql? What is the full query you are using?