Select the last 2 hours on database (specific hours)

Question

0.00/5 (No votes)

See more:

Hi, I would like to ask your help on how could i get a 2 hour data from the database, on specific hour. Once the query run for example by 3pm, it will get the 1:00-3:00 pm data. Hopefully you could help me modify the below query.

What I have tried:

SELECT *
FROM
dbo.Products where
[LoadingDateTime] >= DATEADD(Hour, -2, GETDATE())
group by [LoadingDateTime]
order by [LoadingDateTime] asc

Posted 22-Mar-20 22:52pm

sophia kylie taylor

Updated 23-Mar-20 9:24am

Add a Solution

Comments

MadMyche 23-Mar-20 8:41am

So basically if you run it at 3:01 pm you want 1:00pm thru 3:00pm; kinda like you want to round the time down to the "solid" hour?

3 solutions

Solution 2

Sophia said:
the earlier minutes wasn't capture since it depend on the date time i run the script.

I think by this you mean that if the current time is 11:14 Solution 1 will literally return everything within the last 2 hours i.e. from 9:14 to 11:14 but what you actually want is everything from 09:00 to 11:00.

If so then @RamiroX is close. Try

SELECT [LoadingDateTime]
FROM
@Products where
DATEDIFF(hour, [LoadingDateTime], getdate()) <= 2
group by [LoadingDateTime]
order by [LoadingDateTime] asc

One other point:
- Don't use Select * if using Group By - list the columns explicitly. To be honest, this is true in general for good coding practice.

Posted 23-Mar-20 2:57am

CHill60

Comments

Richard Deeming 23-Mar-20 15:20pm

That query won't be SARGable. It would probably be better to calculate the start and end times instead. :)

CHill60 24-Mar-20 6:34am

Good point, about to 5 your solution!

Solution 1

You just have to specify the upper bound of the range (you only specified the lower bound):

SQL

-- Version 1
SELECT
 *
FROM
 dbo.Products
WHERE
 [LoadingDateTime] >= DATEADD(Hour, -2, GETDATE())
 AND [LoadingDateTime] <= GETDATE()
ORDER BY
 [LoadingDateTime] ASC

-- VERSION 2
SELECT
 *
FROM
 dbo.Products
WHERE
 [LoadingDateTime] BETWEEN DATEADD(Hour, -2, GETDATE()) AND GETDATE()
ORDER BY
 [LoadingDateTime] ASC

Posted 22-Mar-20 23:00pm

phil.o

Comments

sophia kylie taylor 23-Mar-20 5:16am

is there anyway i could get the data within the specific hour? When I tried to run the script, the earlier minutes wasn't capture since it depend on the date time i run the script. really appreciate your help.

phil.o 23-Mar-20 6:05am

Sorry, I don't understand 'the earlier minutes wasn't capture'. Can you give an example with concrete data?

RamiroX 23-Mar-20 7:01am

What about the use of DATEDIFF function like DATEDIFF(hour, LoadingDateTime, GETDATE()) = 2 ?

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)

Richard Deeming · Accepted Answer · 2020-03-23T09:25:00

A slight variation on solution 2 which will let SQL use an index on your date column (if there is one):

SQL

DECLARE @Now datetime = GETDATE();
DECLARE @Max datetime = DateAdd(hour, DateDiff(hour, 0, @Now), 0);
DECLARE @Min datetime = DateAdd(hour, -2, @Max);

SELECT [LoadingDateTime]
FROM dbo.Products 
WHERE [LoadingDateTime] >= @Min And [LoadingDateTime] < @Max
GROUP BY [LoadingDateTime]
ORDER BY [LoadingDateTime] ASC;

Non-SARGable Predicates - Brent Ozar Unlimited®[^]
sql server - T-SQL datetime rounded to nearest minute and nearest hours with using functions - Stack Overflow[^]

Select the last 2 hours on database (specific hours)

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us