Query records within last hour

tdignan87

Well-known member
Joined
Jul 8, 2019
Messages
95
Programming Experience
Beginner
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? :)

C#:
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

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
What database? MSSQL's timestamp doesn't act like other database's timestamp.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
I'm a NoSQL guy, but if push came to shove, I would do something like:
SQL:
AV_TRANSACTIONS.ANSWERTIMESTAMP > DATEADD(-1 HOUR TO CURRENT_TIMESTAMP)
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,378
Location
UK
Programming Experience
10+
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

Well-known member
Joined
Jul 8, 2019
Messages
95
Programming Experience
Beginner
Brilliant Sheepings i did what you said above and the query has worked a treat.
Always appreciate all of your help guys
Cheers
 
Top Bottom