Get last 7 days worth of data in select output

Question

I'm trying to make a script that will send out a Firebird SQL query as a report via email.

I have my query created:

SELECT 
  MASTER.MST_FIRST_NAME || ' ' || MASTER.MST_LAST_NAME AS PERSON,
  TAG.TAG_CODE AS CODE,
  TRANSACK.TR_DATE AS DATE_ACCESED,
  TRANSACK.TR_TIME AS TIME_ACCESSED,
  READER.DR_NAME AS LOCATION,
  MASTER.MST_EMAIL,
  EVENT_TYPE.ET_DESC
FROM
  TAG
  INNER JOIN MASTER ON (TAG.MST_SQ = MASTER.MST_SQ)
  INNER JOIN TRANSACK ON (TAG.TAG_CODE = TRANSACK.TR_TAG_CODE)
  INNER JOIN TAG_LEVEL ON (TAG.TL_VALUE = TAG_LEVEL.TL_VALUE)
  INNER JOIN MASTER_TYPE ON (MASTER.MT_NO = MASTER_TYPE.MT_NO),
  READER,
  EVENT_TYPE
WHERE
  TRANSACK.TR_DATE > dateadd(year, -3, current_timestamp)

And the output is what it should be:

PERSON	CODE	DATE_ACCESED	TIME_ACCESSED	LOCATION	MST_EMAIL	ET_DESC
First Last	412412421421	20190121	143453	storage	[email protected]	Alarm 4
First Last	412412421421	20190121	180459	storage	[email protected]	Alarm 4
First Last	412412421421	20190121	180637	storage	[email protected]	Alarm 4
First Last	412412421421	20190121	113516	storage	[email protected]

But no matter how much documentation I read I cannot find out how to automate it to a way that it will select the last 7 days worth of entries fitting that query.

I tried things like adding WHERE

DATEADD(year, 1, current_timestamp) >= DATEADD(year, -7, current_timestamp)

Or

SELECT * FROM rdb$database WHERE dateadd(7 day to current_date);

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Unexpected end of command - line 1, column 63

current_timestamp >= DATEADD(year, -7, current_timestamp)

And probably 20 variants with EXACT and NOW.

For more context, this is generated by a ACS system, which uses Firebird and it seems to be able to generate pretty output like human readable dates and time, but I cannot really find how it executes the queries when I extracted and ran the jar files and ran them over with a viewer.

Can anyone please help me out?

This is the closest that I got to getting something:

SELECT TR_DATE FROM TRANSACK WHERE TR_DATE= substring(100+extract(day from current_date) from 2 for 2) || '.' || substring(100+extract(month from current_date) from 2 for 2) || '.' || extract(year from current_date);

Statement failed, SQLSTATE = 22018
conversion error from string "01.03.2021"

I got this query example from this question: Firebird Select Field From Table where Field = current_date

After @Mark Rotteveel's reply I finally see a good error:

SQL> select * from TRANSACK WHERE TR_DATE > dateadd(day, -7, current_timestamp);

        S_ID       TR_SEQ      TR_DATE      TR_TIME TR_TERM_SLA                 TR_DPT_NO TR_EVENT TR_DIRECTION TR_TAG_CODE                                      TR_TT_TYPENO     TR_MSTSQ TR_REASON_CODE TR_PROCESSED
============ ============ ============ ============ ======================== ============ ======== ============ ================================================ ============ ============ ============== ============
Statement failed, SQLSTATE = 22018
conversion error from string "20200324"

Can anyone give me an example as to what I should do later? Should this be altered with cast? Please, if possible paste in an example in your answers, I do not really know the sql syntax and I will likely not see a simple error in the exact query I place in :/

Answer 1

Instead of ... WHERE TRANSACK.TR_DATE > dateadd(year, -3, current_timestamp) which shows the results of the last 3 years, use ... WHERE TRANSACK.TR_DATE > dateadd(day, -7, current_timestamp) to show the results for the last seven days.