How to extract data of last week from Firebird database?-CodePudding

I am having trouble of getting the query working, the database is a Firebird database, and the tool I use for querying the data is DBeaver. - work requirement, no other choice. The goal is run the query and only pull the data of last week, here is one of the queries I have tried:

select *
from table 1
where 1.payment_rundate > dateadd(DAY, -7,CURRENT_TIMESTAMP())

I find that dateadd syntax is not recognized in DBeaver.

CodePudding user response：

The problem isn't DATEADD, the problem is that the Firebird syntax doesn't allow CURRENT_TIMESTAMP(), it only allows CURRENT_TIMESTAMP (no parentheses) or CURRENT_TIMESTAMP(precision) where precision is 0 - 3.

In other words, use the condition:

payment_rundate > dateadd(DAY, -7, CURRENT_TIMESTAMP)

Part of the problem is that DBeaver's autocompletion generates CURRENT_TIMESTAMP() and positions the cursor inside the parentheses, expecting you to enter the desired precision. If you don't want to specify precision, it is up to you to delete those parentheses.

CodePudding user response：

I would try with interval

select *
from table 1
where 1.payment_rundate between (current_date - INTERVAL 7 DAY) and current_date