/    Sign up×
Community /Pin to ProfileBookmark

How to select past 7 days records from MySQL?

Copy linkTweet thisAlerts:
Feb 01.2007

Hi,
Ok Im trying to select past X days, or Month records from MySQL, I have date1 fild formted as 0000-00-00 and I tyied to use following SQL but it didnt work, so maybe some one can help me out with corecting my SQL statment?

Thanks!

[code=php]
SELECT * FROM `rec_log` WHERE (unix_timestamp(date_format(date1,”y-m-d”)))
between (unix_timestamp(date_format(curdate(),”y-m-d”)))
and (unix_timestamp(date_format((curdate()+interval 7 day),”y-m-d”)))
[/code]

to post a comment
PHP

22 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogFeb 01.2007 — Is date1 a date or datetime column? If so, there is no need for all that stuff to convert it to a unix timestamp type.
<i>
</i>SELECT * FROM <span><code>rec_log</code></span> WHERE <span><code>date1</code></span> BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
Copy linkTweet thisAlerts:
@alexusauthorFeb 01.2007 — yey thas way easier :-)

Thanks a lot
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — SELECT * FROM <span><code>rec_log</code></span> WHERE <span><code>date1</code></span> BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()[/QUOTE]Not a major contribution... but since we're not testing for a "traditional" date range, the use of the second CURDATE() is redundant and the statement could be simplied a tad by just going:[code=php]SELECT * FROM rec_log WHERE date1 >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)[/code]
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — thanks
Copy linkTweet thisAlerts:
@NogDogFeb 02.2007 — Not a major contribution... but since we're not testing for a "traditional" date range, the use of the second CURDATE() is redundant and the statement could be simplied a tad by just going:[code=php]SELECT * FROM rec_log WHERE date1 >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)[/code][/QUOTE]
I wasn't sure if there might be dates in the future which are not to be selected.
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — so as i understand the NogDog example can have any daye range where other one is just last 7 day

actually this rises another question:

What would be the easy way to get data for each of those 7 dyas?

what I have in DB is this

userid, date

what I wanna do is to build the graph showing volume of rerurning vs new users, so I do group by user and count for group occurance if at the moment the userid count is 1 then its new usere if more then that then iit is returning...

but how can I see if the user ever viseted outside the the povided interva and how can I make SQL say to potput rows as days and then how many new useras and returning users per that day?

My table looks like this

userid date
-------------


11 0000-00-00

11 0000-00-00

34 0000-00-00
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — No, as they are written, both examples do exactly the same.

As for the rest, you will need to use different queries "on demand" (in a loop) to check on the status of individual visitors.

Usually, the IP numbers are used as "visitors", even though that is not totally correct. To get more details from your visitors, you'll have to write your own logging script, possibly using cookies and write your own log as the visitors progress through your site.

There a number of programs that can provide the basic mechanism for you.
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — well ,

I seen some llong time ago the example of the script thta can do some sort of grouping by date with the separation by month....
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — Of course, there's no question that it can be done but it all depends on what and how you want to do it.

Your question was related to the last 7 days worth of log lines.

If you have another question, just ask.
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — ya I asked another one too,

well im curently writing php to manipulate the SQL .... although I wanted to do it in pure SQL
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — Depending on the nature of the data that you need, it can sometimes be easier to dynamically build SQL statement with PHP and even to perform more than one query. It will depend on what you want to do.

As to your previous question, it was fairly general and my answer could only be as general as the question. I don't know what your data looks like, how it's stored, what is stored, etc.

If you can provide more details, we can dig deeper...
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — well let me see what I will get w/ php and then I get back in herer
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — ok i got not bad results with PHP but when I try to do the same for last month I can only do basicaly for the last 30 day from now because selection goes like today today 02-02-2007 so Interval 1 month will give me 01-02-2007

How can I fix it so inetravl would return me 01-00-2007 to 02-00-2007?
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — Instead of:[code=php]SELECT * FROM rec_log WHERE date1 >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)[/code]do:[code=php]<?php
$date_from = "2007-01-01";
$date_to = "2007-01-31";
$sql = "SELECT * FROM rec_log WHERE date1 BETWEEN '$date_from' AND '$date_to'";
?>[/code]
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — but in this case how can I find out what are the previous monthes are?
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — Previous months from where? Today?
[code=php]<?php
$thisYEAR = date("Y");
$thisMONTH = date("m");

$lastMONTH = date("Y-m", mktime(0,0,0,$thisMONTH-1,1,$thisYEAR) . "-%";

$sql = "SELECT * FROM rec_log WHERE date1 LIKE '$lastMONTH'";
?>[/code]
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — ok will try that :-)

whtat that 1 stends for in maketime CurentMonth-1, >>1<<, ThisYear)
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — It means this month (Feb=2) minus 1 (Jan=1).

If you want December, then [b]$thisMONTH-2[/b]...
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — oh now i got it,,,,

now time to get back to my php and try to make it work

meen why do i want to give users ability to see the activity by all type of dates :-(
Copy linkTweet thisAlerts:
@NightShift58Feb 02.2007 — That's what programmers do best: find solutions to problems no one had ?
Copy linkTweet thisAlerts:
@alexusauthorFeb 02.2007 — yea I know I had day off so I decided to do my graph more adjastable for the use side, now I only have headach and 1of 3 days down :-)
×

Success!

Help @alexus spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.16,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @nearjob,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,
)...