-1

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 first.last@example.com Alarm 4
First Last 412412421421 20190121 180459 storage first.last@example.com Alarm 4
First Last 412412421421 20190121 180637 storage first.last@example.com Alarm 4
First Last 412412421421 20190121 113516 storage first.last@example.com

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 :/

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Did you try `DATEADD(day` instead of `DATEADD(year`? If you want seven days instead of seven years. – Connor Low Mar 01 '21 at 16:51
  • The condition `DATEADD(year, 1, current_timestamp) >= DATEADD(year, -7, current_timestamp)` makes no sense at all. It checks if 1 year from **now** is larger than or equal to 7 years ago, which is always true. The same goes for `current_timestamp >= DATEADD(year, -7, current_timestamp)`, which compares if **now** is larger than or equal to 7 years ago, which is also always true. – Mark Rotteveel Mar 01 '21 at 17:48

2 Answers2

1

If you want last 7 days you must subtract 7 days, not years.

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-functions-scalarfuncs-dateadd

user13964273
  • 1,012
  • 1
  • 4
  • 7
  • @PeonWorker That is because your statement is incomplete. `dateadd (7 day to current_date)` produce a value, but you also need to compare that value against something, for example `TRANSACK.TR_DATE > dateadd(day, -7, current_timestamp)` – Mark Rotteveel Mar 01 '21 at 17:46
  • @PeonWorker Notice the `-7` (minus seven), because you want 7 days in the past, not 7 days in the future. – Mark Rotteveel Mar 01 '21 at 17:53
0

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thank you for the reply, that started displaying a good error:```SQL> select * from TRANSACK WHERE TRANSACK.TR_DATE > dateadd(day, -7, current_timestamp);``` And it gives back this ```Statement failed, SQLSTATE = 22018 conversion error from string "20200324"``` For some reason it shows the very firs entry in the column. Is there anything you can help me out with this? –  Mar 02 '21 at 10:13
  • @PeonWorker That problem seems to derive from the fact that you're using a `CHAR` or `VARCHAR` column for dates, instead of using an actual `DATE`. `20200324` is highly ambiguous for the conversion, either switch to type `DATE`, or - if you continue to want to use strings - use a less ambiguous value like `2020-03-24`. – Mark Rotteveel Mar 02 '21 at 12:33
  • The TR_DATE column is an integer in the TRANSACK table. I was unable to alter it to be DATE. –  Mar 02 '21 at 15:24