Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2598
  • Last Modified:

Mysql --> get date 90 days ago


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
dwax
Asked:
dwax
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Beverley PortlockCommented:
"...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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
OP_ZaharinCommented:
- 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dwaxAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I do presume that the field reg_date is indeed of data type date ?!
0
 
Beverley PortlockCommented:
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
 
dwaxAuthor Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now