How to Get Hour Ending data from Oracle Database

We have readings that come into an Oracle database at 10 minutes past each hour.  That means that the first readings for the current day are hour ending (HE) 1:00 AM and the last readings for the current day are HE 24:00 AM.  A completed log would show HE 1 through HE 24 (a sample of the log is shown in message.docx).  An expert suggested I try what is shown below and "(TO_NUMBER(TO_CHAR(TIME,'HH24'))+1) " came the closest to what I need.  Test.txt contains the code I tried and the results are on page #2 of message.docx.  Page #1 in message.docx is what I need to duplicate with the BASH script.  


|          TRUNC(TIME,'HH24') | TO_CHAR(TIME,'HH24') | TO_NUMBER(TO_CHAR(TIME,'HH24')) | (TO_NUMBER(TO_CHAR(TIME,'HH24'))+1) | SUBSTR('0'||(TO_NUMBER(TO_CHAR(TIME,'HH24'))+1),-2) |
|-----------------------------|----------------------|---------------------------------|-------------------------------------|-----------------------------------------------------|
| September, 16 2015 00:00:00 |                   00 |                               0 |                                   1 |                                                  01 |
| September, 16 2015 01:00:00 |                   01 |                               1 |                                   2 |                                                  02 |
| September, 16 2015 02:00:00 |                   02 |                               2 |                                   3 |                                                  03 |
| September, 16 2015 03:00:00 |                   03 |                               3 |                                   4 |                                                  04 |
| September, 16 2015 05:00:00 |                   05 |                               5 |                                   6 |                                                  06 |
| September, 16 2015 06:00:00 |                   06 |                               6 |                                   7 |                                                  07 |
| September, 16 2015 07:00:00 |                   07 |                               7 |                                   8 |                                                  08 |
| September, 16 2015 08:00:00 |                   08 |                               8 |                                   9 |                                                  09 |
| September, 16 2015 10:00:00 |                   10 |                              10 |                                  11 |                                                  11 |
| September, 16 2015 09:00:00 |                   09 |                               9 |                                  10 |                                                  10 |
| September, 16 2015 11:00:00 |                   11 |                              11 |                                  12 |                                                  12 |
| September, 16 2015 12:00:00 |                   12 |                              12 |                                  13 |                                                  13 |
| September, 16 2015 14:00:00 |                   14 |                              14 |                                  15 |                                                  15 |
| September, 16 2015 15:00:00 |                   15 |                              15 |                                  16 |                                                  16 |
| September, 16 2015 16:00:00 |                   16 |                              16 |                                  17 |                                                  17 |
| September, 16 2015 17:00:00 |                   17 |                              17 |                                  18 |                                                  18 |
| September, 16 2015 19:00:00 |                   19 |                              19 |                                  20 |                                                  20 |
| September, 16 2015 20:00:00 |                   20 |                              20 |                                  21 |                                                  21 |
| September, 16 2015 21:00:00 |                   21 |                              21 |                                  22 |                                                  22 |
| September, 16 2015 22:00:00 |                   22 |                              22 |                                  23 |                                                  23 |
| September, 16 2015 23:00:00 |                   23 |                              23 |                                  24 |                                                  24 |

Open in new window

Test.txt
message.docx
dwortmanEngineering AssociateAsked:
Who is Participating?
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:
In the original question I thought this was solved with:
replace(to_char(time,'HH24'),'00','24') hour

http://www.experts-exchange.com/questions/28707662/Oracle-SQL-Plus-to-Select-an-Array-of-Values-from-a-Database.html#a40948045
0
Mark GeerlingsDatabase AdministratorCommented:
If you remove the "+1" from this line:
  to_number(to_char(TIME,'HH24'))+1 HE,
that should get the values to line up with the correct hour.

Ideally you would not need the long list of "hard-coded" conversions in your query.  Ideally these conversions could be done instead (and more dynamically) by a join to a setup or configuration table that would contain these code and name values in two separate columns.  Or, is this a static list of columns that you want to see returned in all cases (whether there is matching data for all of them, or not)?  If that is true, then this may be the best way to do that directly in a SQL query.  (The other option would be to use a reporting tool that can produce this kind of output.)

Also, this line in your query:
  WHERE to_char(TIME, 'DD') = to_char(Sysdate, 'DD')
is the kind of condition you normally want to avoid for performance reasons in Oracle queries, since this part; "to_char(TIME, 'DD')" will normally prevent the use of an index on that column (unless your database has a function-based index that exactly matches this syntax).

A more-efficient way to do that is more like this:
  WHERE TIME between trunc(Sysdate) and trunc(Sysdate) +1

Or, maybe you need that to be:
  WHERE TIME between trunc(Sysdate) -1 and trunc(Sysdate)

Then, if records created exactly on midnight end up getting counted on two different days, you will need to subtract 1 second from the end time, like this:

  WHERE TIME between trunc(Sysdate) and trunc(Sysdate) +1 - 86399/86400
0
PortletPaulfreelancerCommented:
Recommend you do NOT use between for date ranges, instead use a combination of >= with <

 WHERE TIME >= trunc(Sysdate) and TIME < trunc(Sysdate) +1

this way it is always accurate and is not affected by rounding of time units or double counting any records sitting on midnight

see: "Beware of Between"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
You have previously requested assistance in this need for "hours ending" (1 to 24)

The logic:
TO_CHAR(TIME,'HH24') will give the hour in the series '0' to '23'
        i.e any time value >= 00:00:00 +00000 and < 01:00:00 +00000  is '0'

TO_NUMBER( to_char(time,'hh24) ) simply converts the result to a number
        i.e any time value >= 00:00:00 +00000 and < 01:00:00 +00000  is 0 (integer zero)

+1 does exactly that
        i.e any time value >= 00:00:00 +00000 and < 01:00:00 +00000  becomes 1
        i.e any time value >= 23:00:00 +00000 and < 00:00:00 +00000  (of next day) becomes 24

If this logic is incorrect for your situation I simply do not know what else to suggest.

Perhaps try changing the WHERE and GROUP BY clauses

         WHERE TIME >= trunc(Sysdate) and TIME < trunc(Sysdate) +1
         GROUP BY
            to_char(TIME, 'MM/DD/YYYY')
          , to_number(to_char(TIME, 'HH24')) + 1


SELECT
       TIME
     , HE
     , Round((Andrew + Bennet + Dar230 + DarCty + Heming + Kingst + Lugoff + MarNor + MarSou + StGeor + FaberP 
         + Lyles + Willia + Summ1 + Summ2 + Summ3 + Summ4 + LibHal + Woodla + Purrys + Yemass + NHECti + Mateeb 
         + McInt1 + McInt2 + Ander1 + Ander2 + Hodge1 + Hodge2 + NHECDu + Thurmo + Russel + WebbFo + DieLSR + HydLSR + SEPASc) 
     - (AndrewDel + BennetDel + Dar230Del + DarCtyDel + HemingDel + KingstDel + LugoffDel + MarNorDel + MarSouDel 
         + StgeorDel + FaberPDel + LylesDel + WilliaDel + Summ1Del + Summ2Del + Summ3Del + Summ4Del + LibHalDel 
         + WoodlaDel + PurrysDel + YemassDel + MateebDel + McInt1Del + McInt2Del + Ander1Del + Ander2Del + Hodge1Del 
         + Hodge2Del + NHECDuDel + ThurmoDel + RusselDel) + (JeffHydr + Spillway + StStHydr + JeffOil1 + JeffOil2 
         + JeffStm3 + JeffStm4 + Winyah_1 + Winyah_2 + Winyah_3 + Winyah_4 + Cross_1 + Cross_2 + Cross_3 + Cross_4 
         + Hilton_1 + Hilton_2 + Hilton_3 + Myrtle_1 + Myrtle_2 + Myrtle_3 + Myrtle_4 + Myrtle_5 + Rainey_1 + Rainey_2 + Rainey_3 
         + Rainey_4 + Rainey_5 + Rainey_6 + HG + Lee + Rich + Geotown + Berk + Domtar + VSG + Cater + BioEng + BioDor + BioAll + IPCO + CEC), 0) TerrLoad
FROM (
         SELECT
            to_char(TIME, 'MM/DD/YYYY') TIME
          , to_number(to_char(TIME, 'HH24')) + 1 HE
          , max(nvl(CASE WHEN OSI_KEY = '05148002' THEN VALUE END, 0)) Andrew
          , max(nvl(CASE WHEN OSI_KEY = '05149002' THEN VALUE END, 0)) Bennet
          , max(nvl(CASE WHEN OSI_KEY = '05151001' THEN VALUE END, 0)) Dar230
          , max(nvl(CASE WHEN OSI_KEY = '05150010' THEN VALUE END, 0)) DarCty
          , max(nvl(CASE WHEN OSI_KEY = '05054002' THEN VALUE END, 0)) Heming
          , max(nvl(CASE WHEN OSI_KEY = '05152001' THEN VALUE END, 0)) Kingst
          , max(nvl(CASE WHEN OSI_KEY = '05084002' THEN VALUE END, 0)) Lugoff
          , max(nvl(CASE WHEN OSI_KEY = '05153002' THEN VALUE END, 0)) MarNor
          , max(nvl(CASE WHEN OSI_KEY = '05153004' THEN VALUE END, 0)) MarSou
          , max(nvl(CASE WHEN OSI_KEY = '05206002' THEN VALUE END, 0)) StGeor
          , max(nvl(CASE WHEN OSI_KEY = '05184004' THEN VALUE END, 0)) FaberP
          , max(nvl(CASE WHEN OSI_KEY = '05185002' THEN VALUE END, 0)) Lyles
          , max(nvl(CASE WHEN OSI_KEY = '05187002' THEN VALUE END, 0)) Willia
          , max(nvl(CASE WHEN OSI_KEY = '05186002' THEN VALUE END, 0)) Summ1
          , max(nvl(CASE WHEN OSI_KEY = '05236004' THEN VALUE END, 0)) Summ2
          , max(nvl(CASE WHEN OSI_KEY = '05186004' THEN VALUE END, 0)) Summ3
          , max(nvl(CASE WHEN OSI_KEY = '05236002' THEN VALUE END, 0)) Summ4
          , max(nvl(CASE WHEN OSI_KEY = '05075001' THEN VALUE END, 0)) LibHal
          , max(nvl(CASE WHEN OSI_KEY = '05224001' THEN VALUE END, 0)) Woodla
          , max(nvl(CASE WHEN OSI_KEY = '05160002' THEN VALUE END, 0)) Purrys
          , max(nvl(CASE WHEN OSI_KEY = '05225001' THEN VALUE END, 0)) Yemass
          , max(nvl(CASE WHEN OSI_KEY = '05180004' THEN VALUE END, 0)) NHECti
          , max(nvl(CASE WHEN OSI_KEY = '05088010' THEN VALUE END, 0)) Mateeb
          , max(nvl(CASE WHEN OSI_KEY = '05194026' THEN VALUE END, 0)) McInt1
          , max(nvl(CASE WHEN OSI_KEY = '05194054' THEN VALUE END, 0)) McInt2
          , max(nvl(CASE WHEN OSI_KEY = '05039001' THEN VALUE END, 0)) Ander1
          , max(nvl(CASE WHEN OSI_KEY = '05039005' THEN VALUE END, 0)) Ander2
          , max(nvl(CASE WHEN OSI_KEY = '05040001' THEN VALUE END, 0)) Hodge1
          , max(nvl(CASE WHEN OSI_KEY = '05040003' THEN VALUE END, 0)) Hodge2
          , max(nvl(CASE WHEN OSI_KEY = '05179005' THEN VALUE END, 0)) NHECDu
          , max(nvl(CASE WHEN OSI_KEY = '05191001' THEN VALUE END, 0)) Thurmo
          , max(nvl(CASE WHEN OSI_KEY = '05190001' THEN VALUE END, 0)) Russel
          , max(nvl(CASE WHEN OSI_KEY = '05182020' THEN VALUE END, 0)) WebbFo
          , max(nvl(CASE WHEN OSI_KEY = '05181023' THEN VALUE END, 0)) DieLSR
          , max(nvl(CASE WHEN OSI_KEY = '05182018' THEN VALUE END, 0)) HydLSR
          , max(nvl(CASE WHEN OSI_KEY = '05180020' THEN VALUE END, 0)) SEPASc
          , max(nvl(CASE WHEN OSI_KEY = '05148001' THEN VALUE END, 0)) AndrewDel
          , max(nvl(CASE WHEN OSI_KEY = '05149001' THEN VALUE END, 0)) BennetDel
          , max(nvl(CASE WHEN OSI_KEY = '05151002' THEN VALUE END, 0)) Dar230Del
          , max(nvl(CASE WHEN OSI_KEY = '05150009' THEN VALUE END, 0)) DarCtyDel
          , max(nvl(CASE WHEN OSI_KEY = '05054001' THEN VALUE END, 0)) HemingDel
          , max(nvl(CASE WHEN OSI_KEY = '05152002' THEN VALUE END, 0)) KingstDel
          , max(nvl(CASE WHEN OSI_KEY = '05084001' THEN VALUE END, 0)) LugoffDel
          , max(nvl(CASE WHEN OSI_KEY = '05153001' THEN VALUE END, 0)) MarNorDel
          , max(nvl(CASE WHEN OSI_KEY = '05153003' THEN VALUE END, 0)) MarSouDel
          , max(nvl(CASE WHEN OSI_KEY = '05206001' THEN VALUE END, 0)) StGeorDel
          , max(nvl(CASE WHEN OSI_KEY = '05184002' THEN VALUE END, 0)) FaberPDel
          , max(nvl(CASE WHEN OSI_KEY = '05185001' THEN VALUE END, 0)) LylesDel
          , max(nvl(CASE WHEN OSI_KEY = '05187001' THEN VALUE END, 0)) WilliaDel
          , max(nvl(CASE WHEN OSI_KEY = '05186001' THEN VALUE END, 0)) Summ1Del
          , max(nvl(CASE WHEN OSI_KEY = '05236003' THEN VALUE END, 0)) Summ2Del
          , max(nvl(CASE WHEN OSI_KEY = '05186003' THEN VALUE END, 0)) Summ3Del
          , max(nvl(CASE WHEN OSI_KEY = '05236001' THEN VALUE END, 0)) Summ4Del
          , max(nvl(CASE WHEN OSI_KEY = '05075002' THEN VALUE END, 0)) LibHalDel
          , max(nvl(CASE WHEN OSI_KEY = '05224002' THEN VALUE END, 0)) WoodlaDel
          , max(nvl(CASE WHEN OSI_KEY = '05160001' THEN VALUE END, 0)) PurrysDel
          , max(nvl(CASE WHEN OSI_KEY = '05225002' THEN VALUE END, 0)) YemassDel
          , max(nvl(CASE WHEN OSI_KEY = '05088009' THEN VALUE END, 0)) MateebDel
          , max(nvl(CASE WHEN OSI_KEY = '05194025' THEN VALUE END, 0)) McInt1Del
          , max(nvl(CASE WHEN OSI_KEY = '05194053' THEN VALUE END, 0)) McInt2Del
          , max(nvl(CASE WHEN OSI_KEY = '05039002' THEN VALUE END, 0)) Ander1Del
          , max(nvl(CASE WHEN OSI_KEY = '05039006' THEN VALUE END, 0)) Ander2Del
          , max(nvl(CASE WHEN OSI_KEY = '05040002' THEN VALUE END, 0)) Hodge1Del
          , max(nvl(CASE WHEN OSI_KEY = '05040004' THEN VALUE END, 0)) Hodge2Del
          , max(nvl(CASE WHEN OSI_KEY = '05180008' THEN VALUE END, 0)) NHECDuDel
          , max(nvl(CASE WHEN OSI_KEY = '05191002' THEN VALUE END, 0)) ThurmoDel
          , max(nvl(CASE WHEN OSI_KEY = '05190002' THEN VALUE END, 0)) RusselDel
          , nvl(max(CASE WHEN OSI_KEY = '03065053' AND DATA_TYPE = 1 THEN VALUE END), 0) JeffHydr
          , nvl(max(CASE WHEN OSI_KEY = '04205004' AND DATA_TYPE = 1 THEN VALUE END), 0) Spillway
          , nvl(max(CASE WHEN OSI_KEY = '04200127' AND DATA_TYPE = 1 THEN VALUE END), 0) StStHydr
          , nvl(max(CASE WHEN OSI_KEY = '04196050' AND DATA_TYPE = 1 THEN VALUE END), 0) JeffOil1
          , nvl(max(CASE WHEN OSI_KEY = '04196051' AND DATA_TYPE = 1 THEN VALUE END), 0) JeffOil2
          , nvl(max(CASE WHEN OSI_KEY = '04196052' AND DATA_TYPE = 1 THEN VALUE END), 0) JeffStm3
          , nvl(max(CASE WHEN OSI_KEY = '04196053' AND DATA_TYPE = 1 THEN VALUE END), 0) JeffStm4
          , nvl(max(CASE WHEN OSI_KEY = '04196040' AND DATA_TYPE = 1 THEN VALUE END), 0) Winyah_1
          , nvl(max(CASE WHEN OSI_KEY = '04196041' AND DATA_TYPE = 1 THEN VALUE END), 0) Winyah_2
          , nvl(max(CASE WHEN OSI_KEY = '04196042' AND DATA_TYPE = 1 THEN VALUE END), 0) Winyah_3
          , nvl(max(CASE WHEN OSI_KEY = '04196043' AND DATA_TYPE = 1 THEN VALUE END), 0) Winyah_4
          , nvl(max(CASE WHEN OSI_KEY = '04196036' AND DATA_TYPE = 1 THEN VALUE END), 0) Cross_1
          , nvl(max(CASE WHEN OSI_KEY = '04196037' AND DATA_TYPE = 1 THEN VALUE END), 0) Cross_2
          , nvl(max(CASE WHEN OSI_KEY = '04196038' AND DATA_TYPE = 1 THEN VALUE END), 0) Cross_3
          , nvl(max(CASE WHEN OSI_KEY = '04196039' AND DATA_TYPE = 1 THEN VALUE END), 0) Cross_4
          , nvl(max(CASE WHEN OSI_KEY = '04201006' AND DATA_TYPE = 1 THEN VALUE END), 0) Hilton_1
          , nvl(max(CASE WHEN OSI_KEY = '04201007' AND DATA_TYPE = 1 THEN VALUE END), 0) Hilton_2
          , nvl(max(CASE WHEN OSI_KEY = '04201008' AND DATA_TYPE = 1 THEN VALUE END), 0) Hilton_3
          , nvl(max(CASE WHEN OSI_KEY = '04201001' AND DATA_TYPE = 1 THEN VALUE END), 0) Myrtle_1
          , nvl(max(CASE WHEN OSI_KEY = '04201002' AND DATA_TYPE = 1 THEN VALUE END), 0) Myrtle_2
          , nvl(max(CASE WHEN OSI_KEY = '04201003' AND DATA_TYPE = 1 THEN VALUE END), 0) Myrtle_3
          , nvl(max(CASE WHEN OSI_KEY = '04201004' AND DATA_TYPE = 1 THEN VALUE END), 0) Myrtle_4
          , nvl(max(CASE WHEN OSI_KEY = '04201005' AND DATA_TYPE = 1 THEN VALUE END), 0) Myrtle_5
          , nvl(max(CASE WHEN OSI_KEY = '04196044' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_1
          , nvl(max(CASE WHEN OSI_KEY = '04196045' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_2
          , nvl(max(CASE WHEN OSI_KEY = '04196046' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_3
          , nvl(max(CASE WHEN OSI_KEY = '04196047' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_4
          , nvl(max(CASE WHEN OSI_KEY = '04196048' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_5
          , nvl(max(CASE WHEN OSI_KEY = '04196049' AND DATA_TYPE = 1 THEN VALUE END), 0) Rainey_6
          , nvl(max(CASE WHEN OSI_KEY = '05058002' AND DATA_TYPE = 1 THEN VALUE END), 0) HG
          , nvl(max(CASE WHEN OSI_KEY = '05074001' AND DATA_TYPE = 1 THEN VALUE END), 0) Lee
          , nvl(max(CASE WHEN OSI_KEY = '05177001' AND DATA_TYPE = 1 THEN VALUE END), 0) Rich
          , nvl(max(CASE WHEN OSI_KEY = '05047001' AND DATA_TYPE = 1 THEN VALUE END), 0) Geotown
          , nvl(max(CASE WHEN OSI_KEY = '05014001' AND DATA_TYPE = 1 THEN VALUE END), 0) Berk
          , nvl(max(CASE WHEN OSI_KEY = '05041001' AND DATA_TYPE = 1 THEN VALUE END), 0) Domtar
          , nvl(max(CASE WHEN OSI_KEY = '04201027' AND DATA_TYPE = 1 THEN VALUE END), 0) VSG
          , nvl(max(CASE WHEN OSI_KEY = '05022001' AND DATA_TYPE = 1 THEN VALUE END), 0) Cater
          , nvl(max(CASE WHEN OSI_KEY = '05233001' AND DATA_TYPE = 1 THEN VALUE END), 0) BioEng
          , nvl(max(CASE WHEN OSI_KEY = '05053002' AND DATA_TYPE = 1 THEN VALUE END), 0) BioDor
          , nvl(max(CASE WHEN OSI_KEY = '05229002' AND DATA_TYPE = 1 THEN VALUE END), 0) BioAll
          , nvl(max(CASE WHEN OSI_KEY = '05232002' AND DATA_TYPE = 1 THEN VALUE END), 0) IPCO
          , nvl(max(CASE WHEN OSI_KEY = '05239001' AND DATA_TYPE = 1 THEN VALUE END), 0) CEC
         FROM $DATA_VALUES
         WHERE TIME >= trunc(Sysdate) and TIME < trunc(Sysdate) +1
         GROUP BY
            to_char(TIME, 'MM/DD/YYYY')
          , to_number(to_char(TIME, 'HH24')) + 1
         )
ORDER BY TIME, HE;

Open in new window

0
PortletPaulfreelancerCommented:
If you remove the "+1" from this line:
  to_number(to_char(TIME,'HH24'))+1 HE,
that should get the values to line up with the correct hour.
Please refer to this comment. The concept of reporting the hours in a series 0 to 23 has previously been discussed at length but dwortman has been adamant about requiring the series as 1 to 24.

As slightwv points out I had, even earlier, suggested replace(to_char(time,'HH24'),'00','24')  but this is a real kludge that frankly I'm sorry I even mentioned (as it make the first hour of a day the last hour of a day).
0
dwortmanEngineering AssociateAuthor Commented:
When I use this:

from
(
select
      to_char(TIME,'MM/DD/YYYY') TIME,
      replace(to_char(time,'HH24'),'00','24') HE,
      max(nvl(case when OSI_KEY = '05148002' then VALUE end,0)) Andrew,
      max(nvl(case when OSI_KEY = '05149002' then VALUE end,0)) Bennet,
      
I get this for an output
      
09/22/2015 24         2778
09/22/2015 01         2614
09/22/2015 02         2468
09/22/2015 03         2425
09/22/2015 04         2341
09/22/2015 05         2393
09/22/2015 06         2454
09/22/2015 07         2662

HE 24 shown here is from the previous day at HE 24.

Thanks
0
Geert GOracle dbaCommented:
why make it so difficult ?
just report the real time ...

everything else will just lead to confusion
0
Geert GOracle dbaCommented:
another example of home to make things more complicated

why not use the n'th day of the year instead of the date notation ?
to_char(sysdate, 'DDD') 

Open in new window


that way you can produce a report, who nobody will have a clue what the numbers mean :
DDD HH    RANDOM_NUMBER
265 24         2778
265 01         2614
265 02         2468
265 03         2425
265 04         2341
265 05         2393
265 06         2454
265 07         2662

Open in new window


it looks meaningless in the end ...
0
PortletPaulfreelancerCommented:
a day is divided into 24 hours, they are numbered 0 to 23
      (0 is the 1st hour, 23 is the 24th hour)

===================================================
to make meaningful progress
===================================================
a. provide a sample of the raw data,
              with at least one record in each hour, spanning at least 26 hours, over 3 days
then
b. also provide the manually calculated "expected result" from that sample data
0
dwortmanEngineering AssociateAuthor Commented:
Attached are screen copies of the log that requires HE 1-24 (this is an industry standard, not mine).  The first column is the date, the second is the HE value and the third is the sum (expected result) of the row of numbers for that HE hour.  The log is an Excel spreadsheet using VBA to do the calculations.  From what I can tell uses "DateAdd" to get the HE 1-24 values.

Thanks
message.docx
0
Geert GOracle dbaCommented:
i've never seen an industry standard with 2500 as a "valid hour"

ah ... i get it, it's for the new industry standard on mars
from wiki, you can see mars rotates in nearly 25 hours
https://en.wikipedia.org/wiki/Rotation_period

so that's the industry your report is for ?

or is it for another planet ?
0
dwortmanEngineering AssociateAuthor Commented:
I added this line (AND   to_char(TIME, 'HH24') != '00') to the code and the HE values line up.

TerrLoad
from
(
select
      to_char(TIME, 'MM/DD/YYYY') TIME,
    to_number(to_char(TIME, 'HH24')) HE,
      max(nvl(case when OSI_KEY = '05148002' then VALUE end,0)) Andrew,
      max(nvl(case when OSI_KEY = '05149002' then VALUE end,0)) Bennet,  
      .
      .
      .
      nvl(max(case when OSI_KEY = '05232002' and DATA_TYPE = 1 then VALUE end) ,0) IPCO    ,
        nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end) ,0) CEC  
FROM $DATA_VALUES
WHERE to_char(TIME, 'DD') >= to_char(Sysdate, 'DD')
AND   to_char(TIME, 'HH24') != '00'
GROUP BY
            to_char(TIME, 'MM/DD/YYYY'),
            to_char(TIME, 'HH24')    
)
ORDER BY TIME
;                        
exit;

Correct Expected Result:

09/22/2015          1       2614
09/22/2015          2       2468
09/22/2015          3       2425
09/22/2015          4       2341
09/22/2015          5       2393
09/22/2015          6       2454
09/22/2015          7       2662
09/22/2015          8       2676
09/22/2015          9       2772
09/22/2015         10       2804
09/22/2015         11       2890
09/22/2015         12       3023
09/22/2015         13       3150
 

This should work through HE 23 but will skip the HE midnight hour.  Is there a way to run the same code but with the HE 24 row calculation handled by itself and appended to the same output file?

Thanks
0
sdstuberCommented:
try something like this...

select
       to_char(TIME-1/86400, 'MM/DD/YYYY') TIME,
       to_number(replace(to_char(TIME, 'HH24'),'00','24')) HE,
       max(nvl(case when OSI_KEY = '05148002' then VALUE end,0)) Andrew,
       max(nvl(case when OSI_KEY = '05149002' then VALUE end,0)) Bennet,  
       .
       .
       .
       nvl(max(case when OSI_KEY = '05232002' and DATA_TYPE = 1 then VALUE end) ,0) IPCO    ,
         nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end) ,0) CEC  
 FROM $DATA_VALUES
 WHERE time > trunc(sysdate) and time<= trunc(sysdate) +1
 GROUP BY to_char(TIME-1/86400, 'MM/DD/YYYY') ,
          to_number(replace(to_char(TIME, 'HH24'),'00','24'))


the  reason for

TIME-1/86400

is to skew the date-to-text conversions into a single calendar day.


for performance, remember to NOT execute functions on your TIME column in the where clause
0

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
Mark GeerlingsDatabase AdministratorCommented:
Or, to get the records for HE 24 into the same day as the records for 0-23. maybe you need to subtract one hour from your time value like this:

select
        to_char(TIME-1/24, 'MM/DD/YYYY') TIME,

Note: doing a "to_char" conversion like this is OK (from a performance perspective) in the "select" part of your query.  But, this kind of conversion should be avoided on database column names in your "where" clause.
0
sdstuberCommented:
same idea as subtracting   1/86400 in the previous post

I intentionally use one second so I don't accidentally skew a value like  00:10:00  into the previous day.
Since the data is for hours, then it's probably ok to use a one hour offset, but I use a second so it's "portable" regardless of my interval-ending durations.
0
dwortmanEngineering AssociateAuthor Commented:
I have a script running at 10 minutes after each hour using the suggestion below:

select
       to_char(TIME-1/86400, 'MM/DD/YYYY') TIME,
       to_number(replace(to_char(TIME, 'HH24'),'00','24')) HE,
       max(nvl(case when OSI_KEY = '05148002' then VALUE end,0)) Andrew,
       max(nvl(case when OSI_KEY = '05149002' then VALUE end,0)) Bennet,  
       .
       .
       .
       nvl(max(case when OSI_KEY = '05232002' and DATA_TYPE = 1 then VALUE end) ,0) IPCO    ,
         nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end) ,0) CEC  
 FROM $DATA_VALUES
 WHERE time > trunc(sysdate) and time<= trunc(sysdate) +1
 GROUP BY to_char(TIME-1/86400, 'MM/DD/YYYY') ,
          to_number(replace(to_char(TIME, 'HH24'),'00','24'))

However, the last hour written to the output file was 'HE 22'.  The code is still running and everything looks good so far.  Tomorrow morning we will see if the output file stops at HE 22 again.  

I am going to have an additional script running using the suggestion below:

select
        to_char(TIME-1/24, 'MM/DD/YYYY') TIME,


Thanks for the help.
0
sdstuberCommented:
the problem is probably sysdate


if you run this query every hour at 10 minutes past the hour, then at 23:10 you'll get data for hour 22 and below, and maybe hour 23 if it's been loaded.  It's a race condition.  If you wait longer into the 24th hour to make sure all of hour 23 has been loaded then the query should be able to return that data too.

But, when you go to hour 24,  and run it 10 minutes past,  there is no 24:10,  intead it's 00:10 of the next day
which means

 time > trunc(sysdate) and time<= trunc(sysdate) +1

will be for that next day and you won't get any data at all because then it will be looking for data of hour 1 of that next day and at 00:10, hour 1 hasn't finished yet, so you won't get anything


So,  instead of sysdate,  pass in the date you want.
0
dwortmanEngineering AssociateAuthor Commented:
I can use the following to get the previous days 24 hours of data:
.
.
.
    nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end),0) CEC
  FROM $DATA_VALUES
  WHERE TIME >= '09/28/2015 01:00:00' and TIME <= '09/29/2015 00:00:00'
  GROUP BY to_char(TIME - (1/24), 'MM/DD/YYYY') ,
           to_number(replace(to_char(TIME, 'HH24'),'00','24'))
)
ORDER BY TIME


I am using a BASH script and am trying to pass the date portion (09/28/2015 and 09/29/2015) without passing the 01:00:00 and 00:00:00.  Is there a way to do this?  I keep getting ORA-00907 or 00904 when I try.  Thanks for the explanations, they help a lot.
0
sdstuberCommented:
nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end),0) CEC
   FROM $DATA_VALUES
   WHERE TIME >= to_date('09/28/2015,'mm/dd/yyyy') + 1/24 and TIME <= to_date('09/29/2015','mm/dd/yyyy')
   GROUP BY to_char(TIME - (1/24), 'MM/DD/YYYY') ,
            to_number(replace(to_char(TIME, 'HH24'),'00','24'))
 )

Open in new window


or, if you don't want to have calculate the next day yourself, let Oracle do it for you, here I'm using 9/28/2015 for both ends of the date range and letting oracle add one

nvl(max(case when OSI_KEY = '05239001' and DATA_TYPE = 1 then VALUE end),0) CEC
   FROM $DATA_VALUES
   WHERE TIME >= to_date('09/28/2015,'mm/dd/yyyy') + 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1
   GROUP BY to_char(TIME - (1/24), 'MM/DD/YYYY') ,
            to_number(replace(to_char(TIME, 'HH24'),'00','24'))
 )

Open in new window





Since your code isn't complete I can't determine what your missing parentheses (ora-907) or invalid identifiers (ora-904) are
0
Mark GeerlingsDatabase AdministratorCommented:
I always find it simpler to solve Oracle syntax and/or data problems in Oracle by using *.SQL files, stored procedures, and/or views.  Then, if I have to use a shell script to run something, I have the shell script launch SQL*Plus, which then runs my *.SQL file that contains either a SQL query or a PL\SQL block, then exits SQL*Plus.

(Maybe that's because I have 25 years of experience as a SQL and/or PL\SQL developer, but I am not a master of shell scripting.)

It may be possible to do all of what you need in a single shell script, but I can't write that.
0
dwortmanEngineering AssociateAuthor Commented:
I tried the latest suggestion but it brought back all 24 hours for the 28th.  What I am trying in order to automate this process is build a date variable in BASH:

DD=$(date +%d)
MM=$(date +%m)
YYYY=$(date +%Y)
SQLDate=$MM/$DD/$YYYY

and pass it to:

WHERE TIME >= to_date($SQLDate,'mm/dd/yyyy') + 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1

but I get this return:

WHERE TIME >= to_date(09/30/2015,'mm/dd/yyyy') + 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1
                                           *
ERROR at line 132:
ORA-01858: a non-numeric character was found where a numeric was expected

I did not try the variable here:

+ 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy')

because it was just a test to see if it would work at all.  The result of SQLDate is today's date of 09/30/2015.  Also, I understand the error but do not know how to fix it, if possible.

Thanks
0
slightwv (䄆 Netminder) Commented:
>>ORA-01858: a non-numeric character was found where a numeric was expected

The string needs to be in quotes:
WHERE TIME >= to_date('$SQLDate','mm/dd/yyyy') + 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1
0
dwortmanEngineering AssociateAuthor Commented:
I did try that before and it returned nothing.  But after looking at the WHERE statement I saw why it could never work:

WHERE TIME >= to_date('$SQLDate','mm/dd/yyyy') + 1/24 and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1

$SQLDate=09/30/2015 vs "and TIME <= to_date('09/28/2015','mm/dd/yyyy') + 1".  

I changed it to:

 WHERE TIME >= to_date('$SQLDate','mm/dd/yyyy') + 1/24 and TIME <= to_date('$SQLDate','mm/dd/yyyy') + 1

and it brought back HE 1 to the current hour which is what I need.  If I understand what has been suggested here HE 24 should be brought back as well.  I will let it run overnight and see, unless someone thinks otherwise.

thanks
0
sdstuberCommented:
yes, you must be consistent in the date used

that's what I was trying to demonstrate in the 2nd example of  http:#a41016718   above
0
dwortmanEngineering AssociateAuthor Commented:
I wish there were more points to give out but here goes:
sdstuber 200
maxwell 100
markgeer 100
slightwv 100

EE is still telling me: "You must assign all of your available points to answers."  But I have given out 500 points and rated sdstuber as best solution.  Please let me know if I have done something wrong in assigning points.

Thanks for the help
0
sdstuberCommented:
if all 4 contributed to the answer you use, then splitting is fine.
if you give points but don't use the post then that's against the spirit of the point system (i.e. participation doesn't merit award, only utilization)
how you weight them within the split is up to you.


note- utilization can be in actually using the syntax from one, but learning something new from another.
0
dwortmanEngineering AssociateAuthor Commented:
Thanks for the comment.  Knowing this I feel OK about the points awarded because I used some form of almost all of the Experts comments.  Your note says exactly what I am talking about.  Also, most of us out here want to learn from the Experts not just get the answers.  This is why the explanations are so important.  Please keep up the good work as I know I will be back with more questions...
0
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.