CODEWITHSUNDEEP
phpmysql
nibb11
nibb11

Reputation: 25

MYSQL Query for Simple Data Range

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

Upvotes: 0

Views: 514

Answers (2)

Arash Mousavi
Arash Mousavi

Reputation: 2148

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 ...

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

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.

Upvotes: 2

Related Questions