Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mysql --> get date 90 days ago

Posted on 2011-03-21
9
Medium Priority
?
2,381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
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…

718 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