Mysql search records from now upto 30 days or 1 month or 2 month

Question

0.00/5 (No votes)

See more:

hi i want to select from table notes all records from now upto 30 days or 1 month or 2 monthes

i enter time in mysql using php time() function when i created the table i entered row column name time and i choose integer and 10

i tried

What I have tried:

$sql="SELECT * FROM notes WHERE time BETWEEN NOW() - INTERVAL 30 DAY AND NOW()";
//$sql="SELECT * FROM notes   where time >= CURRENT_TIMESTAMP -30";
//$sql="select * FROM notesWHERE   time  BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)AND NOW()";

 
 //$sql="  SELECT  * FROM   notes WHERE  time BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()";

//$sql=" select * from notes where  time >now() - interval 1 month";
  // $sql="     SELECT * FROM   notes WHERE  user_id ='$user_id' and time > (NOW() - INTERVAL 1 MONTH)";
      //$sql=  "SELECT * FROM notes WHERE time BETWEEN now()    , DATE_SUB(NOW()    , INTERVAL 1 MONTH) ";

Posted 2-Sep-21 2:24am

Member 15344769

Updated 2-Sep-21 17:33pm

v2

Add a Solution

3 solutions

Solution 1

Try this:

SQL

SELECT *
FROM notes
WHERE time BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()

or:

SQL

SELECT CURRENT_DATE - INTERVAL 30 DAY

See: MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions[^]

Posted 2-Sep-21 2:33am

Maciej Los

Updated 2-Sep-21 2:36am

v3

Comments

Maciej Los 2-Sep-21 9:58am

You have to provide more details about a structure of table... Please, explain, why do you want to store data and time separately.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 15344769 · Accepted Answer · 2021-09-02T05:20:00

Solution 2

$sql="SELECT * FROM notes WHERE from_unixtime(`time`) > date_sub(now(), interval 30 day) " >

Posted 2-Sep-21 5:20am

Member 15344769

Updated 2-Sep-21 5:22am

Wendelius · Accepted Answer · 2021-09-02T17:33:00

Based on your question and the conversation in solution 1 I would recommend storing the date (and time) either in datetime or timestamp column, depending on the resolution you want to have.

In other words I would convert the unix timestamp to a MySql native date type when storing the data in the first place. This helps you when you want to compare dates, calculate using them etc. If you leave it in unix format, you always have to remember to convert it when using and with greater amount of data this also causes some CPU overhead.

To select proper date type, have a look at MySQL :: MySQL 8.0 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[^]