MYSQL Query for Simple Data Range

Question

Im new to mysql coding so help would be appreciated.

I have a simple database with a table called history that stores data in dates.

The table is called history and there have a column name for "user", "data" and "creation" which is the date. Data stamps in "creation" are like this "2013-01-13 07:45:49"

I tried some very basic stuff with PHP and I get data, for example: $result = mysql_query("SELECT data FROM history WHERE user='705'");

Will display all the data from the user "705"

But how would I select the same data for user 705 only in a specific data range? For example only show data from the last week? Or any date range...

"data" by the way are just numbers like "88993" so with my current query I get a long and long list of numbers. I just want to narrow my selection by dates.

Help is appreciated. Thanks

bonCodigo · Accepted Answer · 2013-01-13 08:12:47Z

2

Try dates between..

select * from yourtable
where userid =785
and date between yourdate1 and yourdate2
;

You may use interval to specify the dates gap that you need:

select * from yourtable
    where userid =785
    and date between '2013-01-13 07:45:49' 
    and '2013-01-13 07:45:49' interval -10 day
    ;

Try this based on your last comment:

select * from yourtable
        where userid =785
        and creation between Now() 
        and Now() interval -7 day
order by creation
limit 7
;

Reference: you may check all date time functions here.

edited Jan 13, 2013 at 8:12

answered Jan 13, 2013 at 7:54

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bonCodigo Over a year ago

@nibb11 try this one out and comment please. :)

nibb11 Over a year ago

Thanks for the reply but this does not work: $result = mysql_query("select data from history where user ='705' and date between '2013-01-13 08:02:5'2 and '2013-01-13 08:00:51'");

nibb11 Over a year ago

Does MYSQL does not have just a number query? Like * shows all, would it not be easier to pull for example only the last 7 records, this would be a week, like pull 7 instead of everything. Of course dates would be better but I don´t seem to get it to work.

bonCodigo Over a year ago

@nibb11 so you mean you only have one record per day for this user? So from today till 7 days back if we get records for this user will that be alright or if you have multiple records per day (with different times) do you want to limit it by the latest 7 records?

Arash Mousavi · Accepted Answer · 2013-01-13 08:21:51Z

0

For last week try this:

select data from history
where (user='705') AND (date between date_sub(now(),INTERVAL 1 WEEK) and now());

you can also substitute "1 WEEK" with "1 MONTH", "2 MONTH" ,"1 YEAR", and ...

edited Jan 13, 2013 at 8:21

answered Jan 13, 2013 at 8:07

Arash Mousavi

2,1465 gold badges28 silver badges48 bronze badges

2 Comments

nibb11 Over a year ago

Sorry but this codes return nothing, I also don´t understand why you are closing the query in this example with ; if its not even in bracked like here: w3schools.com/php/php_mysql_select.asp The codes on this tutorial, all work for me fine which means they are valid in PHP.

Arash Mousavi Over a year ago

I edited my answer, try this. as said in this Doc dev.mysql.com/doc/refman/5.0/en/entering-queries.html you can ending your query with semi-colon...

Collectives™ on Stack Overflow

MYSQL Query for Simple Data Range

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related