Select records from today, this week, this month php mysql

Question

I imagine this is pretty simple, but can't figure it out. I'm trying to make a few pages - one which will contain results selected from my mysql db's table for today, this week, and this month. The dates are entered when the record is created with date('Y-m-d H:i:s');. Here's what I have so far:

day where date>(date-(60*60*24))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24)) ORDER BY score DESC"

week where date>(date-(60*60*24*7))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24*7)) ORDER BY score DESC"

month (30 days) where date>(date-(60*60*24*30))

 "SELECT * FROM jokes WHERE date>(date-(60*60*24*30)) ORDER BY score DESC"

Any ideas would be much appreciated. Thanks!

Answer 1

Assuming your date column is an actual MySQL date column:

SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;        
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;

Answer 2

Try using date and time functions (MONTH(), YEAR(), DAY(), MySQL Manual)

This week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());

Last week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;

Answer 3

Current month:

SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date)=MONTH(NOW());

Current week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW());

Current day:

SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND DAY(date) = DAY(NOW());

This will select only current month, really week and really only today :-)

Answer 4

Nathan's answer will give you jokes from last 24, 168, and 744 hours, NOT the jokes from today, this week, this month. If that's what you want, great, but I think you might be looking for something different.

Using his code, at noon, you will get the jokes beginning yesterday at noon, and ending today at noon. If you really want today's jokes, try the following:

SELECT * FROM jokes WHERE date >= CURRENT_DATE() ORDER BY score DESC;  

You would have to do something a little different from current week, month, etc., but you get the idea.

Answer 5

Everybody seems to refer to date being a column in the table.
I dont think this is good practice. The word date might just be a keyword in some coding language (maybe Oracle) so please change the columnname date to maybe JDate.
So will the following work better:

SELECT * FROM jokes WHERE JDate >= CURRENT_DATE() ORDER BY JScore DESC;

So we have a table called Jokes with columns JScore and JDate.

Answer 6

A better solution for "today" is:

SELECT * FROM jokes WHERE DATE(date) = DATE(NOW())

Answer 7

Nathan's answer is very close however it will return a floating result set. As the time shifts, records will float off of and onto the result set. Using the DATE() function on NOW() will strip the time element from the date creating a static result set. Since the date() function is applied to now() instead of the actual date column performance should be higher since applying a function such as date() to a date column inhibits MySql's ability to use an index.

To keep the result set static use:

SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) 
ORDER BY score DESC;

SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) 
ORDER BY score DESC;

SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH) 
ORDER BY score DESC;

Answer 8

I think using NOW() function is incorrect for getting time difference. Because by NOW() function every time your are calculating the past 24 hours. You must use CURDATE() instead.

    function your_function($time, $your_date) {
    if ($time == 'today') {
        $timeSQL = ' Date($your_date)= CURDATE()';
    }
    if ($time == 'week') {
        $timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
    }
    if ($time == 'month') {
        $timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
    }

    $Sql = "SELECT * FROM jokes WHERE ".$timeSQL
    return $Result = $this->db->query($Sql)->result_array();
}

Answer 9

Well, this solution will help you select only current month, current week and only today

SELECT * FROM games WHERE games.published_gm = 1 AND YEAR(addedon_gm) = YEAR(NOW()) AND MONTH(addedon_gm) = MONTH(NOW()) AND DAY(addedon_gm) = DAY(NOW()) ORDER BY addedon_gm DESC;

For Weekly added posts:

WEEKOFYEAR(addedon_gm) = WEEKOFYEAR(NOW())

For Monthly added posts:

MONTH(addedon_gm) = MONTH(NOW())

For Yearly added posts:

YEAR(addedon_gm) = YEAR(NOW())

you'll get the accurate results where show only the games added today, otherwise you may display: "No New Games Found For Today". Using ShowIF recordset is empty transaction.

Answer 10

To get last week's data in MySQL. This works for me even across the year boundries.

select * from orders_order where YEARWEEK(my_date_field)= YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK));

To get current week's data

select * from orders_order where YEARWEEK(date_sold)= YEARWEEK(CURRENT_DATE());

Answer 11

You can do same thing using single query

SELECT sum(if(DATE(dDate)=DATE(CURRENT_TIMESTAMP),earning,null)) astodays,
       sum(if(YEARWEEK(dDate)=YEARWEEK(CURRENT_DATE),earning,null)) as weeks, 
       IF((MONTH(dDate) = MONTH(CURRENT_TIMESTAMP()) AND YEAR(dDate) = YEAR(CURRENT_TIMESTAMP())),sum(earning),0) AS months,
       IF(YEAR(dDate) = YEAR(CURRENT_TIMESTAMP()),sum(earning),0) AS years, 
       sum(fAdminFinalEarning) as total_earning FROM `earning`

Hope this works.