ansaurus

Question

How to fetch data from oracle database in hourly basis

Answer 1

+7 A:

Use the TO_CHAR function to format the DATETIME column, so you can GROUP BY it for aggregate functions:

  SELECT TO_CHAR(t.time, 'YYYY-MM-DD HH24') AS hourly,
         COUNT(*) AS numPerHour
    FROM YOUR_TABLE t
GROUP BY TO_CHAR(t.time, 'YYYY-MM-DD HH24')

OMG Ponies 2010-10-07 15:45:53

i like it, so +1. But can we do it without using join.Because my data is huge.

MAS1 2010-10-07 15:50:28

@MAS1: Sorry, but I've re-read your question and I don't see anything implying the need for a JOIN.

OMG Ponies 2010-10-07 15:52:49

@OMG Ponies, i think u use self join in your answer.(t.time)

MAS1 2010-10-07 15:54:08

@MAS1: No, there's no self join--you'd see the "JOIN"/etc keyword if that were the case.

OMG Ponies 2010-10-07 15:57:10

@OMG Ponies,ok..Thanks. Will try it now.

MAS1 2010-10-07 16:00:54

@MAS1 that's not a join, it's using the table alias. If you're only looking for data for the current day you can add `WHERE t.time BETWEEN TRUNC(sysdate) AND TRUNC(sysdate + 1)` between the `FROM` and `GROUP BY` clauses; or you can parameterise that to use a date you pass in.

Alex Poole 2010-10-07 16:11:15

@Alex I think you meant (sysdate + 1) there (not + 10)

Tony Andrews 2010-10-07 16:13:14

@Tony thanks, mis-corrected a typo and made it worse. Fixed it.

Alex Poole 2010-10-07 16:15:06

@Alex and @Tony, Will try this also.

MAS1 2010-10-07 16:18:49

@MAS1: At the risk of stating the obvious, if you don't already have an index on the Time field on your table, you should add one if you can. If the query still takes too long, consider a separate partition on the table for the current day's data.

Mark Bannister 2010-10-07 17:01:13

@Mark Bannister: The use of TO_CHAR would render the index on the `time` column useless, unfortunately.

OMG Ponies 2010-10-07 17:09:02

@ Mark Bannister:Thanks for your suggestion of separation of current day's data.

MAS1 2010-10-07 17:39:17

@OMG: the `WHERE` clause would still use the index to restrict to a single day, and avoid the need to fetch the data blocks for the records that match as well (although I think I read somewhere that `count(*)` could undo some of that and `count(1)` might be more performant - must test that one day).

Alex Poole 2010-10-07 19:35:11

Answer 2

+2 A:

sh_kamalh 2010-10-07 16:15:30

@sh_kamalh..+1,Thanks for your suggestion..but i can't create another table.

MAS1 2010-10-07 16:17:58

ansaurus

tags:

views:

answers:

How to fetch data from oracle database in hourly basis

related questions