Select the last 2 hour data on database

Question

0.00/5 (No votes)

See more:

Hello,

I'm still new with regards on the scripting, and I am cracking this query for quite sometime now. I would want to get the last 2 hour data from the big query. we managed to come up on getting the 5 minute records but I'll appreciate if you could help me getting the 2 hour data. Morelikely we would want to get a data from specific hours. For example 8am-10pm data will be shown during 10:30 am. Appreciate if you could help me on this.

Below is our query getting the 5 minute data

What I have tried:

Select  A.ID, A.Timestamp,  B.Package_quantity,
safe_add(A.Package_quantity, B.Package_quantity) as defect,
from
`tbl_ER020` A
INNER JOIN `tbl_ER220` B
ON A.ID = B.ID
where datetime_add('1900-01-01 00:00:00', interval 27900 + safe_cast(60 * 5 * floor(datetime_diff(current_datetime(),'1900-01-01 00:00:00',minute)/5) as int64) - 450 second) <= datetime(A.Timestamp)
            And DateTime(A.Timestamp) < datetime_add('1900-01-01 00:00:00', interval 27900 + safe_cast(60 * 5 * floor(datetime_diff(current_datetime(),'1900-01-01 00:00:00',minute)/5) as int64) - 150 second)
            ORDER BY A.Timestamp ASC

Posted 27-Feb-20 17:35pm

sophia kylie taylor

Updated 28-Feb-20 5:11am

Add a Solution

Comments

Maciej Los 28-Feb-20 2:01am

What database provider? Looks like MySql or Postgres.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-02-27T18:52:00

Even if I don't practice SQL, I have enough experience to see a very bad design, something to avoid at all cost.
- Because you use current_datetime() 2 times, you can be unfortunate and have them on a different minute, hour or day which will silently make the query on a different lapse than expected.
- If the SQL compiler is unable to understand that you want the 2 boundaries as constant (which is not said) in the query, it will issue 2 current_datetime() per record because current_datetime() is volatile. Not good either.
- If the SQL compiler is unable to remove the DateTime() from DateTime(A.Timestamp), the server will be unable to leverage an index.

The correct way to do this is:

SQL

-- declare the 2 variables with same datatype as A.Timestamp
declare @start datetime;
declare @End datetime;
declare @DT datetime = current_datetime(); -- read current_datetime() once
-- set start and End values
set @Start = @DT - ... ; -- cancel seconds, minutes and hours as needed
set @End = @Start + ... ; -- add offset as needed

Select  A.ID, A.Timestamp,  B.Package_quantity,
safe_add(A.Package_quantity, B.Package_quantity) as defect,
from
`tbl_ER020` A
INNER JOIN `tbl_ER220` B
ON A.ID = B.ID
where @Start <= A.Timestamp And A.Timestamp < @End
ORDER BY A.Timestamp ASC;

Why doing the query this way?
Your query runtime depend on the number of records of datatable.
My query runtime depend on the number of records of select result.
On a huge datatable, it makes a big difference.
[Update]
Read there: PostgreSQL: Documentation: 9.1: Function Volatility Categories[^]

Quote:
A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed.

MarcusCole6833 · Answer 2 · 2020-02-28T05:12:00

My solution is a slight refinement to the above

declare @d1 datetime = dateadd(hh,-2, getdate()) 
declare @d2 datetime = getdate()
Select  A.ID, A.Timestamp,  B.Package_quantity,
safe_add(A.Package_quantity, B.Package_quantity) as defect,
from
`tbl_ER020` A
INNER JOIN `tbl_ER220` B
ON A.ID = B.ID
where a. timestamp between @d1 and @d2
            
            ORDER BY A.Timestamp ASC

Date Add is covered her

DATEADD (Transact-SQL) - SQL Server | Microsoft Docs[^]

Between is here

BETWEEN (Transact-SQL) - SQL Server | Microsoft Docs[^]

Select the last 2 hour data on database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0