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

Question

I think it is quite simple, but can not understand. I am trying to make several pages - one that will contain the results selected from my mysql db table for today, this week, and this month. Dates are entered when a record is created using date('Ymd H:i:s'); . Here is what I still have:

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 highly appreciated. Thank!

Answer 1

Assuming your date column is the 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 Guide )

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()); 

This week:

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

This day:

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

It will select only the current month, really a week and really only today :-)

Answer 4

Nathan’s answer will give you jokes for the past 24, 168 and 744 hours, not jokes from today, this week, this month. If this is what you want, great, but I think you can look for something else.

Using your code, at noon you will receive jokes that start at noon yesterday and end at noon today. If you really want to joke today, try the following:

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

You will need to do something a little different from the current week, month, etc., but you will get this idea.

Answer 5

Everyone seems to refer to a date, which is a column in the table.
I do not think this is a good practice. The word date might just be a keyword in some coding language (maybe Oracle), so please change the date of the column name, possibly to JDate.
So the following will 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

The best solution for today:

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

Answer 7

Nathan's answer is very close, but he will return a floating result set. As time shifts, the records will sail to the result set. Using the DATE() function on NOW() will erase the time element from the date the static result set was created. Since the DATE() function is applied to NOW() instead of the actual value of the date column, it should be higher since applying a function such as DATE() to the date column prohibits the use of the MySql index.

To maintain a static use of the result:

 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

Well, this solution will help you choose only the current month, the 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; 

Messages added per week:

 WEEKOFYEAR(addedon_gm) = WEEKOFYEAR(NOW()) 

Messages added per month:

 MONTH(addedon_gm) = MONTH(NOW()) 

Messages for the year added:

 YEAR(addedon_gm) = YEAR(NOW()) 

You’ll get accurate results that show only those games that are added today, otherwise you can display: “No new games found to date.” Using the ShowIF record set is an empty transaction.

Answer 9

I think that using the NOW() function is wrong to get the time difference. Because the NOW() function works every time you calculate the last 24 hours. You should use CURDATE() .

  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 10

To get data for last week in MySQL. It works for me even across the borders of the year.

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

To get the current week

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

Answer 11

You can do the same using a single request

 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.