Query to get results from the last hour where the target column is a timestamp.

Hello,

I'm trying to write a select where I can get part numbers sold in the last hour. The thing is, I'm having difficulties in getting the results right. Here's the info...

select c.partnumber,o.timeplaced,count(o.orders_id) AS count
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and o.timeplaced > current_timestamp - interval '1' hour
group by c.partnumber,o.timeplaced;

This all works fine. The issue is that since timeplaced is a timestamp, it goes down to the millisecond. Therefore, I'm not able to get the grouping I'm looking for. I really only want orders placed in the last hour. I really don't care about doing any ordering, although being able to order by the count would be nice.

How can I run my query but only get results in the last hour so the grouping will be better? For example, in my current query, the results look something like this...

PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 7:22:23.325000 AM      1
123456      10/16/2018 7:34:40.448000 AM      1
123456      10/16/2018 7:54:51.566000 AM      1
123457      ...

When I'd like it to be:
PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 07 AM                  3  
123457      ...

I did try using TO_CHAR(o.timeplaced,'yyyy-mm-dd hh24') in my select, but the results are basically the same. I'm wondering if this is because of the "and o.timeplaced > current_timestamp - interval '1' hour" while the "TO_CHAR(o.timeplaced,'yyyy-mm-dd hh24')" is just for display.:
PARTNUMBER  TIMEPLACED                    COUNT
123456      10/16/2018 07 AM                  1
123456      10/16/2018 07 AM                  1
123456      10/16/2018 07 AM                  1

Thanks,
Larry
Larry GrovesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try:
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'HH') - interval '1' hour

if there is an index on timeplaced, this will ignore it.  If you need the index used, we can tweak it.
Dorababu MSenior Software EngineerCommented:
If SQL you can try as follows
col > DATEADD(HOUR, -1, GETDATE())

Open in new window

slightwv (䄆 Netminder) Commented:
@Dorababu,

Oracle Topic Area and TO_CHAR seems to imply an Oracle solution?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
Note - you will have to include the trunc function in your select, where clause, and group by statements -
select c.partnumber,trunc(o.timeplaced,'HH') AS hour,count(o.orders_id) AS count
 from orders o
 inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'HH') - interval '1' hour
group by c.partnumber,trunc(o.timeplaced,'HH');
Larry GrovesAuthor Commented:
Hi slightwv... This is an Oracle question. Also, I tried your solution...

and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'HH') - interval '1' hour

I completely forgot about "trunc". That did give me results back. The one thing I needed to do was to add trunc(o.timeplaced,'HH') to my select and group by as well.

select c.partnumber,trunc(o.timeplaced,'HH'),count(o.orders_id)
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
where trunc(o.timeplaced,'HH') > trunc(current_timestamp,'HH') - interval '1' hour --o.timeplaced > current_timestamp - interval '1' hour
...
group by c.partnumber,trunc(o.timeplaced,'HH')

Thank you so much!! I really appreciate your help.

Thanks,
Larry
Larry GrovesAuthor Commented:
Hi slightwv...

Actually, now that I'm looking at the data, I'm seeing orders from "this" hour. Not from the last hour. For example, it's 9:52am but my results are all 9am. When I change the interval to '2', I'm seeing a mixture of both 8am and 9am.
awking00Information Technology SpecialistCommented:
where trunc(o.timeplaced,'HH') >= trunc(current_timestamp,'HH') - interval '2' hour
and trunc(o.timeplaced,'HH') < trunc(current_timestamp,'HH') - interval '1' hour

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry GrovesAuthor Commented:
That did it! Thanks awking00!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.