asked on 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,
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
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
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,
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
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
Oracle DatabaseSQL
Last Comment
Larry Groves
If SQL you can try as follows
col > DATEADD(HOUR, -1, GETDATE())
@Dorababu,
Oracle Topic Area and TO_CHAR seems to imply an Oracle solution?
Oracle Topic Area and TO_CHAR seems to imply an Oracle solution?
Note - you will have to include the trunc function in your select, where clause, and group by statements -
select c.partnumber,trunc(o.timep
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
group by c.partnumber,trunc(o.timep
select c.partnumber,trunc(o.timep
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
...
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
group by c.partnumber,trunc(o.timep
ASKER
Hi slightwv... This is an Oracle question. Also, I tried your solution...
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
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.timep
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
where trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
...
group by c.partnumber,trunc(o.timep
Thank you so much!! I really appreciate your help.
Thanks,
Larry
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
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.timep
from orders o
inner join orderitems oi on (o.orders_id = oi.orders_id)
where trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
...
group by c.partnumber,trunc(o.timep
Thank you so much!! I really appreciate your help.
Thanks,
Larry
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That did it! Thanks awking00!
and trunc(o.timeplaced,'HH') > trunc(current_timestamp,'H
if there is an index on timeplaced, this will ignore it. If you need the index used, we can tweak it.