Subtracting two dates and comparing if result >365 days
I have this sql SELECT instrument_id, maturity_dt, issue_dt, (maturity_dt - issue_dt) as days FROM instrument_desc_current where maturity_dt is not null and issue_dt is not null and (maturity_dt - issue_dt) > 365 limit 100 but get this error... SYNTAX_ERROR: line 5:30: '>' cannot be applied to interval day to second, integer commenting out "and (maturity_dt - issue_dt) > 365 my result set looks like... instrument_id maturity_dt issue_dt days 1000244483 3/22/2011 3/21/2011 1 00:00:00.000 1000244596 7/1/2020 4/4/2000 7393 00:00:00.000 1000244665 4/1/2019 12/8/2011 2671 00:00:00.000 1000245009 6/1/2021 6/1/2011 3653 00:00:00.000 1000245547 5/23/2011 4/6/2011 47 00:00:00.000 1000245766 11/8/2012 11/7/2012 1 00:00:00.000
How do I select only those records which are greater than 365 days?
I think based on the tags you're using Athena, so date_diff may be the answer. The official AWS documentation links to Presto.
Other database systems also support DATEDIFF: MySQL, MS SQL Server and Oracle. With POSTGRES you could use DATE_PART, see http://www.sqlines.com/postgresql/how-to/datediff.
Relevant questions
PearsonVue glitch: Exam is blocked by AWS for next 14 days
asked 5 months agoExam Revoked After PASSING - Bad Experience with PSI Online (AWS Solution Architect Associate Exam)
asked 5 months agoStack stuck at CREATE_IN_PROGRESS for 12 days now
asked 9 months agoEC2 instance shutting down for more than 2 days
Accepted Answerasked 3 years agoMy AWS account has been hacked over a week and it generated a bill of $2845 in last two days of Feb and $3431 in March. In just 15 days, this amount got generated.
asked 4 months agoWorkdocs user invite getting expired within 2 days
asked a month agoInstance reachability check failed - two days in a row
asked 3 years agoSubtracting two dates and comparing if result >365 days
asked a month agoAWS SSO Issue with Office 365
asked 3 years agoRetrieve or store AWS Step function Execution history older than 90 days.
asked 2 years ago