Morbo Posted July 23, 2008 Share Posted July 23, 2008 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 Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/ Share on other sites More sharing options...
accident Posted July 23, 2008 Share Posted July 23, 2008 Switch the WHERE statement with this one WHERE t1.dateline >= CURDATE() and t1.dateline < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AND t4.title<>'' Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-597583 Share on other sites More sharing options...
Morbo Posted July 23, 2008 Author Share Posted July 23, 2008 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 Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-597618 Share on other sites More sharing options...
accident Posted July 23, 2008 Share Posted July 23, 2008 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<>'' Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-597776 Share on other sites More sharing options...
Morbo Posted July 25, 2008 Author Share Posted July 25, 2008 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 Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-599333 Share on other sites More sharing options...
Morbo Posted July 25, 2008 Author Share Posted July 25, 2008 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..... Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-599371 Share on other sites More sharing options...
Barand Posted July 25, 2008 Share Posted July 25, 2008 In what format is your date stored in the table "swtickettimetrack"? Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-599817 Share on other sites More sharing options...
Morbo Posted July 28, 2008 Author Share Posted July 28, 2008 Its setup as an Integer YYYY-MM-DD Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-601513 Share on other sites More sharing options...
Barand Posted July 28, 2008 Share Posted July 28, 2008 try WHERE DATE(FROM_UNIXTIME(t1.dateline)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() AND t4.title<>'' Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-601515 Share on other sites More sharing options...
Morbo Posted July 28, 2008 Author Share Posted July 28, 2008 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 Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-601790 Share on other sites More sharing options...
fenway Posted July 30, 2008 Share Posted July 30, 2008 What version of mysql? What is the full query you are using? Link to comment https://forums.phpfreaks.com/topic/116208-help-code-to-determine-date/#findComment-603697 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.