Improve company productivity with a Business Account.Sign Up

x
?
Solved

Mysql --> get date 90 days ago

Posted on 2011-03-21
9
Medium Priority
?
2,929 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 143

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

 

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 143

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 143

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 500 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 143

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

586 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