Solved

Mysql --> get date 90 days ago

Posted on 2011-03-21
9
2,138 Views
Last Modified: 2012-05-11

I am trying to get a date 90 days ago from a db table.
I've used this query before:

SELECT * FROM dbtable WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) <= reg_date

But i would like to remove the < part. What's the best way to do this?
0
Comment
Question by:dwax
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35179514
"...i would like to remove the < part. What's the best way to do this?"

Err.... with the DEL key on the keyboard?

Somehow I don't think that is the answer you are looking for? Can you restate the question more clearly? What is wrong with the SQL as it is a present?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35179518
can you please clarify?

it's not sure what you mean by "remove the <" part.
do you want all the records exactly 90 days ago ?

SELECT *
  FROM dbtable
  WHERE reg_date >= DATE_SUB(CURDATE(),INTERVAL 90 DAY)
     and reg_date < DATE_SUB(CURDATE(),INTERVAL 89 DAY)
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35179585
- try this:

1- if you want to query data between today up to 90 days:
SELECT * FROM dbtable
 WHERE reg_date BETWEEN CURDATE() and DATE_SUB(CURDATE(),INTERVAL 90 DAY)

Open in new window


2- if you want to query data on the 90th day:
SELECT * FROM dbtable
 WHERE reg_date = DATE_SUB(CURDATE(),INTERVAL 90 DAY)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dwax
ID: 35180240
@bportlock
haha, that would be funny indeed!

I would like to retrieve the data on the ..th day:

For example: there is a user that signed up on the 16th:
reg_date = 2011-03-16 07:18:46

Today i would like to check which user(s) have signed up exactly 5 days ago.
I've tried both queries you suggest, but both returned nothing.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35180369
well, did you try mine?

for N = 90:
SELECT *
  FROM dbtable
  WHERE reg_date >= DATE_SUB(CURDATE(),INTERVAL 90 DAY)
     and reg_date < DATE_SUB(CURDATE(),INTERVAL 89 DAY)  

Open in new window


for N = 5:
SELECT *
  FROM dbtable
  WHERE reg_date >= DATE_SUB(CURDATE(),INTERVAL 5 DAY)
     and reg_date < DATE_SUB(CURDATE(),INTERVAL 4 DAY)  

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35180373
I do presume that the field reg_date is indeed of data type date ?!
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 125 total points
ID: 35180405
The problem is that if you are using a DATETIME field for reg_date then it will never match a 'pure' date because of the extra hours and minutes. Try changing the above query like so

SELECT * FROM dbtable
 WHERE CAST( reg_date AS DATE )  = DATE_SUB(CURDATE(),INTERVAL 90 DAY)

and adjust the 90 DAY to whatever you require
0
 

Author Comment

by:dwax
ID: 35181201

Thanks a lot guys!
The field was indeed a DATETIME field. That caused the problems, this query worked like a charm:

SELECT * FROM dbtable
 WHERE CAST( reg_date AS DATE )  = DATE_SUB(CURDATE(),INTERVAL 90 DAY)

Thanks again for your help.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35181234
well, though the query shall work, it will make sure that an index on req_date could not be used with the CAST method in the accepted solution.
please stick with the >=  + <  method I suggest. surely a bit more of code, but if you have to run the query often, make sure to use that one with a index on the field reg_date (and mesure the difference ... )
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question