asked on

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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"

PortletPaul

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

PortletPaul

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).

dwortman

ASKER

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

Geert G

why make it so difficult ?
just report the real time ...

everything else will just lead to confusion

Geert G

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 ...

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwortman

ASKER

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

Geert G

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 ?

dwortman

ASKER

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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.

Sean Stuber

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.

dwortman

ASKER

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.

Sean Stuber

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.

dwortman

ASKER

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.

Sean Stuber

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

Mark Geerlings

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.

dwortman

ASKER

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

slightwv (䄆 Netminder)

>>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

dwortman

ASKER

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

Sean Stuber

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

dwortman

ASKER

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

Sean Stuber

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.

dwortman

ASKER

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...