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 gt  date- 60 60 24   ORDER BY score DESC    week  where date  date- 60 60 24 7      SELECT   FROM jokes WHERE date gt  date- 60 60 24 7   ORDER BY score DESC    month  30 days    where date  date- 60 60 24 30      SELECT   FROM jokes WHERE date gt  date- 60 60 24 30   ORDER BY score DESC    Any ideas would be much appreciated  Thanks

User · Answer

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.

User · Answer

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  gt  DATE SUB DATE NOW     INTERVAL 1 DAY   ORDER BY score DESC   SELECT   FROM jokes WHERE date  gt  DATE SUB DATE NOW     INTERVAL 1 WEEK   ORDER BY score DESC   SELECT   FROM jokes WHERE date  gt  DATE SUB DATE NOW     INTERVAL 1 MONTH   ORDER BY score DESC

User · Answer

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

User · Answer

A better solution for  today  is   SELECT   FROM jokes WHERE DATE date    DATE NOW

User · Answer

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

User · Answer

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

User · Answer

Assuming your date column is an actual MySQL date column   SELECT   FROM jokes WHERE date  gt  DATE SUB NOW    INTERVAL 1 DAY  ORDER BY score DESC          SELECT   FROM jokes WHERE date  gt  DATE SUB NOW    INTERVAL 1 WEEK  ORDER BY score DESC  SELECT   FROM jokes WHERE date  gt  DATE SUB NOW    INTERVAL 1 MONTH  ORDER BY score DESC

User · Answer

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  gt   CURRENT DATE   ORDER BY JScore DESC    So we have a table called Jokes with columns JScore and JDate

User · Answer

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- gt db- gt query  Sql - gt result array

User · Answer

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

User · Answer

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  -

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

Examples related to php

Examples related to mysql

Examples related to date

Examples related to select