Solved

Mysql --> get date 90 days ago

Posted on 2011-03-21
9
2,101 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now