Блог

Главная - Вопросы по программированию - выбор данных до и после полуночи

выбор данных до и после полуночи

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть панель мониторинга, которая сообщает о производстве за каждый час смены. На панели инструментов есть фиксированные интервалы для каждого часа смены.

  • Когда смена с 7 утра до 7 вечера, все в порядке.
  • Когда смена длится с 7 вечера до 7 утра, у меня возникает проблема.
  • с 7 вечера до 8 вечера все в порядке
  • с 8 вечера до 9 вечера все в порядке
  • с 9 вечера до 10 вечера все в порядке
  • с 10 вечера до 11 вечера все в порядке
  • с 11 вечера до 12 вечера все в порядке
  • проблема с 12 вечера до 1 часа ночи — проблема в том, что когда вы входите в панель мониторинга в 7 вечера, я забираю производство для сегодняшнего производства с полуночи до 1 часа ночи. Как я могу закодировать свой sql, чтобы игнорировать данные с полуночи до 1 часа ночи до полуночи и выбирать их после полуночи?

Вот SQL, который я использую

 select date_time_created,
       ith.quantity
From   ifsapp.inventory_transaction_hist2 ith
where  TRANSACTION_CODE = 'OOREC'
and    upper(ifsapp.Inventory_Part_API.Get_Unit_Meas('WW001',PART_NO) ) =upper( 'CS' )
and    date_time_created -1 - 60*60*7/86400 >= to_date(to_char(sysdate -1,'DD/MM/YYYY') || ' 19:00:00','DD/MM/YYYY HH24:MI:SS')- 60*60*7/86400
and    date_time_created - 60*60*7/86400 <= to_date(to_char(sysdate,'DD/MM/YYYY') || ' 19:59:59','DD/MM/YYYY HH24:MI:SS')- 60*60*7/86400
and    userid='3520'

Спасибо

Комментарии:

1. Я не понимаю ваш запрос. Вы занимаетесь математикой много времени, но когда вы добавляете или вычитаете одно и то же влево и вправо, все это становится недействительным. В итоге получается, ГДЕ date_time_created = сегодня в 19:xx:xx . Но где время смены, о котором вы говорите? Я думал, что ваша таблица будет содержать что-то вроде start_shift и end_shift. Где это «для каждого часа» в вашем запросе? Пожалуйста, покажите образцы данных и ожидаемые результаты.

2. Я не понимаю, что вы имеете в виду How can I code my sql to ignore data from midnight to 1am before midnight and select it after midnight? . Можете ли вы предоставить некоторые образцы входных данных вместе с выводом, который вы ожидаете увидеть?

3. Когда вы превращаете дату в символьную строку, манипулируете строкой, а затем превращаете ее обратно в дату, вы почти наверняка делаете что-то неправильно. Манипулируйте датами, используя только функции даты и алгебру … например, Trunc(sysdate) 7/24

Ответ №1:

с 7 вечера до 7 утра: 

 date_time_created between to_date( to_char(sysdate-1, 'ddmmyyyy') || '19:00:00', 'ddmmyyhh24:mi:ss')
                    AND   to_date( to_char(sysdate, 'ddmmyyyy')   || '07:00:00', 'ddmmyyhh24:mi:ss')

с 12 вечера до 1 часа ночи: 

 date_time_created between to_date( to_char(sysdate-1, 'ddmmyyyy') || '12:00:00', 'ddmmyyhh24:mi:ss')
                    AND   to_date( to_char(sysdate, 'ddmmyyyy')   || '01:00:00', 'ddmmyyhh24:mi:ss')

Ответ №2:

Я не совсем уверен, что вам нужно, но если вы хотите увидеть результаты текущей смены, надеюсь, этот пример поможет вам понять, что вам нужно сделать:

 WITH dates AS (SELECT TRUNC(SYSDATE)   5.1/24   LEVEL/24 dt
               FROM   dual
               CONNECT BY LEVEL <= 36)
-- end of generating some data for the following query to run against
SELECT dt,
       CASE WHEN to_number(to_char(dt - 7/24, 'hh24')) < 12 THEN '7am-7pm'
            ELSE '7pm-7am'
       END shift
FROM   dates
WHERE  dt >= CASE WHEN to_number(to_char(SYSDATE - 7/24, 'hh24')) < 12 THEN TRUNC(SYSDATE -7/24)   7/24
                  ELSE TRUNC(SYSDATE -7/24)   19/24
             END
AND    dt < CASE WHEN to_number(to_char(SYSDATE - 7/24, 'hh24')) < 12 THEN TRUNC(SYSDATE -7/24)   19/24
                 ELSE TRUNC(SYSDATE -7/24)   31/24
            END;

DT                  SHIFT
------------------- -------
06/10/2016 07:06:00 7am-7pm
06/10/2016 08:06:00 7am-7pm
06/10/2016 09:06:00 7am-7pm
06/10/2016 10:06:00 7am-7pm
06/10/2016 11:06:00 7am-7pm
06/10/2016 12:06:00 7am-7pm
06/10/2016 13:06:00 7am-7pm
06/10/2016 14:06:00 7am-7pm
06/10/2016 15:06:00 7am-7pm
06/10/2016 16:06:00 7am-7pm
06/10/2016 17:06:00 7am-7pm
06/10/2016 18:06:00 7am-7pm

И просто чтобы доказать, что для разных системных адресов отчет будет сгенерирован, я настроил запрос на использование разных «системных адресов» для имитации запуска в разное время дня:

 WITH dates AS (SELECT TRUNC(SYSDATE)   5.1/24   LEVEL/24 dt
               FROM   dual
               CONNECT BY LEVEL <= 36),
-- end of generating some data for the main query to run against
 sys_dates AS (SELECT to_date('06/10/2016 20:10:23', 'dd/mm/yyyy hh24:mi:ss') sysdt FROM dual UNION ALL
               SELECT to_date('06/10/2016 13:10:23', 'dd/mm/yyyy hh24:mi:ss') sysdt FROM dual UNION ALL
               SELECT to_date('07/10/2016 01:10:23', 'dd/mm/yyyy hh24:mi:ss') sysdt FROM dual)
-- end of mimicking different sysdates that the report could be run against
-- - for testing purposes only
SELECT sd.sysdt run_time_of_report,
       d.dt,
       CASE WHEN to_number(to_char(d.dt - 7/24, 'hh24')) < 12 THEN '7am-7pm'
            ELSE '7pm-7am'
       END shift
FROM   dates d
       CROSS JOIN sys_dates sd
WHERE  d.dt >= CASE WHEN to_number(to_char(sd.sysdt - 7/24, 'hh24')) < 12 THEN TRUNC(sd.sysdt -7/24)   7/24
                    ELSE TRUNC(sd.sysdt -7/24)   19/24
               END
AND    d.dt < CASE WHEN to_number(to_char(sd.sysdt - 7/24, 'hh24')) < 12 THEN TRUNC(sd.sysdt -7/24)   19/24
                   ELSE TRUNC(sd.sysdt -7/24)   31/24
              END
ORDER BY sd.sysdt, d.dt;

RUN_TIME_OF_REPORT  DT                  SHIFT
------------------- ------------------- -------
06/10/2016 13:10:23 06/10/2016 07:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 08:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 09:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 10:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 11:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 12:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 13:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 14:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 15:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 16:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 17:06:00 7am-7pm
06/10/2016 13:10:23 06/10/2016 18:06:00 7am-7pm
--
06/10/2016 20:10:23 06/10/2016 19:06:00 7pm-7am
06/10/2016 20:10:23 06/10/2016 20:06:00 7pm-7am
06/10/2016 20:10:23 06/10/2016 21:06:00 7pm-7am
06/10/2016 20:10:23 06/10/2016 22:06:00 7pm-7am
06/10/2016 20:10:23 06/10/2016 23:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 00:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 01:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 02:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 03:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 04:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 05:06:00 7pm-7am
06/10/2016 20:10:23 07/10/2016 06:06:00 7pm-7am
-- 
07/10/2016 01:10:23 06/10/2016 19:06:00 7pm-7am
07/10/2016 01:10:23 06/10/2016 20:06:00 7pm-7am
07/10/2016 01:10:23 06/10/2016 21:06:00 7pm-7am
07/10/2016 01:10:23 06/10/2016 22:06:00 7pm-7am
07/10/2016 01:10:23 06/10/2016 23:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 00:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 01:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 02:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 03:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 04:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 05:06:00 7pm-7am
07/10/2016 01:10:23 07/10/2016 06:06:00 7pm-7am

Вы можете видеть, что отчет по состоянию на 06/10/2016 20: 10:23 и 07/10/2016 01: 10:23 возвращает те же данные для смены с 7 вечера до 7 утра.