Calculating hours worked in the last 28 days-CodePudding

I have a Firebird database which contains a table Duty:

Date	Start	End
DATE	TIME	TIME

This records the start and end times of my job. My goal is to count how many hours I've worked in the last 28 days (672 hours) from when the query was run. My starting point is:

SELECT
    SUM(DATEDIFF(HOUR, "Start", "End"))
FROM
    "Duty"
WHERE
    "Date" > DATEADD(-672, HOUR, TIMESTAMP 'NOW')

This gives me a Token unknown - *, error on line 6. Not sure what's wrong with the comma usage.

I also realise that my table has separate date and time columns, instead of a timestamp, which may cause some complications with the code above. Due to other data in the table, I can't use timestamps in it.

Where do I go from here?

CodePudding user response：

Your parameters to DATEADD are in the wrong order. It is either DATEADD(<amount> <unit> TO <datetime>) or DATEADD(<unit>, <amount>, <datetime>). Given you use the comma-form, you need to put HOUR first.

As an aside, you should not use TIMESTAMP 'NOW', use CURRENT_TIMESTAMP (or CURRENT_DATE if you just need a DATE), or if you really must, CAST('NOW' AS TIMESTAMP). TIMESTAMP 'NOW' no longer works in Firebird 4.0 or higher.

In other words, use:

DATEADD(HOUR, -672, CURRENT_TIMESTAMP)

However, if you want 28 days ago, why not just use

DATEADD(DAY, -28,  CURRENT_TIMESTAMP)