Query records within last hour

[tdignan87]

Hi i am having issues trying to look at a TIMESTAMP data type to only see if records within last hour, Example of code below: Any ideas?

DateTime hourDate =  DateTime.Now.AddHours(-1);
                    FbDataAdapter sqlFB = new FbDataAdapter(" Select AV_TRANSACTIONS.QUESTION,AV_TRANSACTIONS.QUESTIONTIMESTAMP,AV_TRANSACTIONS.ANSWERTIMESTAMP,AV_TRANSACTIONS.ANSWER,USERS.NAME As OPERATOR,TERMINALS.NAME, " +
                        "COMMODITIES.CODE As PRODUCT From (TERMINALS TERMINALS Inner Join (USERS USERS Inner Join AV_TRANSACTIONS AV_TRANSACTIONS On USERS.USERID = AV_TRANSACTIONS.USER_ID) On TERMINALS.TERMINALID =" +
                        "AV_TRANSACTIONS.TERMINAL_ID) Inner Join (COMMODITIES COMMODITIES Inner Join PRODUCEDH PRODUCEDH On COMMODITIES.COMMODITYID = PRODUCEDH.COMMODITYID) On AV_TRANSACTIONS.BATCH_ID = PRODUCEDH.BATCHID " +
                        "Where AV_TRANSACTIONS.QUESTION = 'CCP1: Metal detection test pass/fail?' And AV_TRANSACTIONS.ANSWER = 'Fail' And AV_TRANSACTIONS.ANSWERTIMESTAMP > " + hourDate + "", db);

So AV_TRANSACTIONS.ANSWERTIMESTAMP is the timestamp table format in the DB.

Need to convert TIMESTAMP i guess


Thanks
Tom

 

[Skydiver]

What database? MSSQL's timestamp doesn't act like other database's timestamp.

 

[tdignan87]

Firebird mate

 

[Skydiver]

I'm a NoSQL guy, but if push came to shove, I would do something like:

AV_TRANSACTIONS.ANSWERTIMESTAMP > DATEADD(-1 HOUR TO CURRENT_TIMESTAMP)

 

[NoUserHere]

You don't want to get the users time because that likely won't correspond well when you query against the database minus one hour from the the users timezone. Doing so would return sporadic results for different users. So instead I suggest getting the servers time and then minus one hour in your parameter value when executing your query. See : How to get the Firebird server date and time?

You also shouldn't be cutting into your queries with concatenation.

 

[tdignan87]

Brilliant Sheepings i did what you said above and the query has worked a treat.
Always appreciate all of your help guys
Cheers