title: Oracle SQL: Using intervals to specify ranges of hours
body: <p>I have power meter data stored in table MeterData:</p>
  <pre><code>    create table MeterData (
      MeterID VARCHAR2(10), DownloadCycle VARCHAR2(6), DateHour Date,
      KWH Number(22,6), KW Number(22,6), KVA Number(22,6), KVAR Number(22,6),
      CONSTRAINT UniqueDownload UNIQUE(MeterID, DownloadCycle, DateHour))
  </code></pre>
  <p>The data looks like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>MeterID</th>
  <th>DownloadCycle</th>
  <th>DateHour</th>
  <th>KWH</th>
  <th>KW</th>
  <th>KVA</th>
  <th>KVAR</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2319927</td>
  <td>202206</td>
  <td>13/06/2022 00:00</td>
  <td>0.138</td>
  <td>0.552</td>
  <td>0.552</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2500350</td>
  <td>202206</td>
  <td>13/06/2022 00:15</td>
  <td>0.612</td>
  <td>2.448</td>
  <td>2.916</td>
  <td>1.584</td>
  </tr>
  <tr>
  <td>2500351</td>
  <td>202206</td>
  <td>13/06/2022 01:30</td>
  <td>0.8</td>
  <td>3.2</td>
  <td>3.2358</td>
  <td>0.48</td>
  </tr>
  <tr>
  <td>2500352</td>
  <td>202206</td>
  <td>13/06/2022 04:00</td>
  <td>0.288</td>
  <td>1.152</td>
  <td>1.44</td>
  <td>0.864</td>
  </tr>
  <tr>
  <td>2500353</td>
  <td>202206</td>
  <td>13/06/2022 05:30</td>
  <td>0.90808</td>
  <td>3.63232</td>
  <td>4.32456</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2500396</td>
  <td>202206</td>
  <td>13/06/2022 12:00</td>
  <td>68.09</td>
  <td>272.36</td>
  <td>277.101157</td>
  <td>51.04</td>
  </tr>
  <tr>
  <td>2500446</td>
  <td>202206</td>
  <td>13/06/2022 18:15</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2500453</td>
  <td>202206</td>
  <td>13/06/2022 21:00</td>
  <td>2.772</td>
  <td>11.088</td>
  <td>11.088</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2500472</td>
  <td>202206</td>
  <td>13/06/2022 23:30</td>
  <td>64.8</td>
  <td>259.2</td>
  <td>305.788256</td>
  <td>162.24</td>
  </tr>
  <tr>
  <td>2500490</td>
  <td>202206</td>
  <td>14/06/2022 00:30</td>
  <td>2.4</td>
  <td>9.6</td>
  <td>9.6</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2501352</td>
  <td>202206</td>
  <td>14/06/2022 01:45</td>
  <td>11.64</td>
  <td>46.56</td>
  <td>46.56</td>
  <td>0</td>
  </tr>
  <tr>
  <td>5187222</td>
  <td>202206</td>
  <td>14/06/2022 06:30</td>
  <td>1.452</td>
  <td>5.808</td>
  <td>7.392</td>
  <td>0</td>
  </tr>
  <tr>
  <td>5284288</td>
  <td>202206</td>
  <td>14/06/2022 11:00</td>
  <td>66.792</td>
  <td>267.168</td>
  <td>267.447334</td>
  <td>149.336</td>
  </tr>
  <tr>
  <td>5516997</td>
  <td>202206</td>
  <td>14/06/2022 18:30</td>
  <td>0.384</td>
  <td>1.536</td>
  <td>8.112</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to assign every record in table MeterData to a range of hours stored in table HourlyBlocks, in which I'm using intervals as the starting and ending hours:</p>
  <pre><code>    create table HourlyBlocks (
      HourlyBlock VARCHAR2(6) UNIQUE, BlockStart INTERVAL DAY(1) TO SECOND(0),
      BlockEnd INTERVAL DAY(1) TO SECOND(0));
      insert into HourlyBlocks values (
      'Rest', interval '0 05:00:00' day to second, interval '0 18:00:00' day to second);
      insert into HourlyBlocks values (
      'Peak', interval '0 18:00:00' day to second, interval '0 23:00:00' day to second);
      insert into HourlyBlocks values (
      'Valley', interval '0 23:00:00' day to second, interval '1 05:00:00' day to second);
  </code></pre>
  <p>(HourlyBlock 'Valley' begins at 23:00:00 and ends at 05:00:00 of the following day).</p>
  <p>To test to which HourlyBlock every record in MeterData belongs, I extract the record's hour, minute and second information as an interval with the following, adding 1 day to the interval if it is less than 05:00:00 and thus belongs to HourlyBlock 'Valley':</p>
  <pre><code>    select distinct m.MeterID, m.DateHour, m.kwh,
      NUMTODSINTERVAL(m.DateHour - trunc(m.DateHour), 'DAY') + case when 
        NUMTODSINTERVAL(m.DateHour - trunc(m.DateHour), 'DAY') &lt;= interval '00 05:00:00'
        day to second then interval '1' day else interval '0' day end as intval,
      h.HourlyBlock
      from MeterData m, HourlyBlocks h
      where (NUMTODSINTERVAL(m.DateHour - trunc(m.DateHour ), 'DAY') &gt; h.BlockStart
      and NUMTODSINTERVAL(m.DateHour - trunc(m.DateHour ), 'DAY') + case when
        NUMTODSINTERVAL(m.DateHour - trunc(m.DateHour ), 'DAY') &lt;= interval '00 05:00:00'
        day to second then interval '1' day else interval '0' day end &lt;= h.BlockEnd)
  </code></pre>
  <p>The HourlyBlock are correctly assigned, except for records where DateHour is between 00:00:00 and 05:00:00!</p>
  <p>What am I doing wrong?</p>
  <p>The expected output for the sample data provided would be:</p>
  <pre><code>|MeterID|DateHour        |KWH   |intval             |HourlyBlock|
  |-------|----------------|------|-------------------|-----------|
  |2319927|13/06/2022 00:00|0.138 |+00 00:00:00.000000|Valley     |
  |2500350|13/06/2022 00:15|0.612 |+01 00:15:00.000000|Valley     |
  |2500351|13/06/2022 01:30|0.8   |+01 01:30:00.000000|Valley     |
  |2500352|13/06/2022 04:00|0.288 |+01 04:00:00.000000|Valley     |
  |2500353|13/06/2022 05:30|0.908 |+00 05:30:00.000000|Rest       |
  |2500396|13/06/2022 12:00|68.09 |+00 12:00:00.000000|Rest       |
  |2500446|13/06/2022 18:15|0     |+00 18:15:00.000000|Peak       |
  |2500453|13/06/2022 21:00|2.772 |+00 21:00:00.000000|Peak       |
  |2500472|13/06/2022 23:30|64.8  |+00 23:30:00.000000|Valley     |
  |2500490|14/06/2022 00:30|2.4   |+01 00:30:00.000000|Valley     |
  |2501352|14/06/2022 01:45|11.64 |+01 01:45:00.000000|Valley     |
  |5187222|14/06/2022 06:30|1.452 |+00 06:30:00.000000|Rest       |
  |5284288|14/06/2022 11:00|66.792|+00 11:00:00.000000|Rest       |
  |5516997|14/06/2022 18:30|0.384 |+00 18:30:00.000000|Peak       |
  </code></pre>
  <p>(I'm sorry I had to format the output as code. It was the only way around that pesky &quot;Your post appears to contain code that is not properly formatted as code&quot; error.)</p>