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

Larry Groves
Larry Groves used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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 Engineer
Distinguished Expert 2018

Commented:
If SQL you can try as follows
col > DATEADD(HOUR, -1, GETDATE())

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
@Dorababu,

Oracle Topic Area and TO_CHAR seems to imply an Oracle solution?
Should you be charging more for 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 using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

Commented:
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');

Author

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

Author

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.
Information Technology Specialist
Commented:
where trunc(o.timeplaced,'HH') >= trunc(current_timestamp,'HH') - interval '2' hour
and trunc(o.timeplaced,'HH') < trunc(current_timestamp,'HH') - interval '1' hour

Author

Commented:
That did it! Thanks awking00!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial