Фильтровать записи по дате, чтобы получить последние 7 дней
Я новичок в SQL и пытаюсь создать запрос, который будет учитывать запись, если она соответствует нескольким параметрам. Одним из них является то, что он должен быть введен в течение последних 7 дней, остальные параметры запроса работают нормально, но в тот момент, когда я добавляю И для диапазона дат, он ломается. кто-нибудь может помочь? Вот мой запрос, поле даты — pvs.last_reading, а формат — 2019-06-24 16:20:00.
SELECT count(*) as criticalCases
FROM patient_vitals_state pvs
INNER JOIN user u on u.uuid = pvs.patient_uuid
WHERE EXISTS (
SELECT pc.clinic_uuid FROM patient_clinics pc
WHERE pc.clinic_uuid IN (?) AND pc.patient_uuid = u.uuid
)
AND pvs.avg_sbp >= 181
AND pvs.last_reading BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
Прямо сейчас, если я удалю последнее И, запрос будет работать нормально и вернет несколько результатов. Если я добавлю его, он вернет 0, когда должен вернуть 1.
Вот все значения ячеек, на которые он должен смотреть. 2019-06-24 16:20:00, 2019-06-01 19:00:00, 2019-06-24 14:00:00, 2019-06-20 18:25:00 это другое поле pvs.avg_sbp который оценивается, и только значение freading в первой строке соответствует критериям.
SQL выглядит хорошо; честно говоря, я предполагаю, что у вас есть граничное условие; вы можете проверить это, изменив «7» на, скажем, «14», «90» или «365» и посмотрев, сколько результатов вы получите; это покажет вам, что SQL в порядке, тогда вы можете сосредоточиться на данных; поскольку данные представляют собой даты, вам необходимо учитывать такие вещи, как время сервера (не обязательно такое же, как время вашего рабочего стола), часовой пояс и т. д.
«критерию соответствует только считывание в первой строке» ... что вы имеете в виду под этим? freading не является критерием в вашем SQL
Вот что-то действительно странное, если я изменяю Интервал с 7 на 30, он тянет и считает запись на 01.06.2019, но не запись на сегодня 24.06.2019??
Извините, landru27, что надо было "читать". в основном у меня 1 запись в системе. он должен быть не старше 7 дней и иметь рейтинг avg_sbp 181 или выше.
ваш комментарий о том, что вы не возвращаете запись на сегодня, сильно напоминает мне о временах, когда я сталкивался с проблемами границы даты; вы можете начать устранение неполадок, увидев результаты SELECT NOW();; может быть, время на сервере SQL отличается от того, что вы думаете? и не удивляйтесь, если завтра вы начнете видеть строку за 6/24; Я на 99,44% уверен, что вы столкнулись с проблемой границы даты и времени.
Итак, идем вперед, я решаю проблему с границей, которую только что запустил. SELECT DATE_SUB(NOW(), INTERVAL 7 DAY) as One_Week_Ago. и вывод был «{One_Week_Ago: 2019-06-17T12:50:20.000Z}». Это правильная дата, так почему же она все еще сбивается?
обратите внимание на время вашего результата недельной давности: 12:50; строка данных, которую вы ожидаете увидеть, имеет время 16:20; таким образом, на сервере, на котором работает MySQL, NOW() по-прежнему до — дата и время строки, которую вы ожидаете увидеть; кроме того, Z во временной части результата недельной давности говорит мне, что это несоответствие во многом связано с предположениями о часовом поясе.
Спасибо всем, что я понял это, вместо простого NOW() мне пришлось запустить фильтр как BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_ADD(NOW(), INTERVAL 1 DAY) для учета разницы в TimeZone.
Я превратил наш разговор в этих комментариях в ответ; именно то, как с этим справиться, зависит от нескольких факторов - слишком много, чтобы предлагать конкретные шаги, - но по этой теме есть достаточно материалов для чтения (часовые пояса в столбцах базы данных), и лучший подход будет зависеть от вашей конкретной ситуации
Ответы 2
Это проблема границы даты и времени. SQL в порядке. Согласно нашему разговору в комментариях, строка данных, которую вы ожидаете увидеть, имеет дату и время:
2019-06-24 16:20:00
но на основе вашего запроса недельной давности дата и время на сервере (на момент написания этого) ближе к:
2019-06-24 12:50:20
Таким образом, NOW() является до значением patient_vitals_state.last_reading, и SQL на самом деле делает именно то, что и ожидалось: он исключает результат, чей last_reading находится за пределами BETWEEN параметров.
Z в опубликованном вами результате One_Week_Ago: 2019-06-17T12:50:20.000Z в первую очередь говорит мне о том, что сервер MySQL хранит дату и время в формате UTC, но вы основываете свои ожидаемые результаты на своем собственном локальном времени. Возможно, вы также вводите время, используя свое собственное местное время, но по незнанию они хранятся в формате UTC, таким образом, отображая момент времени, отличный от того, что вы действительно намеревались.
Проблемы с часовым поясом — очень распространенный класс проблем при работе с датами и временем; часовой пояс, используемый для столбца базы данных, является одним из аспектов этой общей проблемы.
Чтобы решить эту проблему, вам необходимо синхронизировать то, как данные даты и времени вводятся, сохраняются и извлекаются из базы данных. На эту тему можно многое прочитать - слишком много, чтобы опубликовать ответ SO. Но, надеюсь, это объясняет источник проблемы и направляет вас к чтению, которое вам нужно сделать, чтобы понять фундаментальный вопрос.
Одно из возможных решений:
SELECT count(*) as criticalCases
FROM patient_vitals_state pvs
JOIN user u
on u.uuid = pvs.patient_uuid
WHERE EXISTS (
SELECT pc.clinic_uuid
FROM patient_clinics pc
WHERE pc.clinic_uuid IN (?)
AND pc.patient_uuid = u.uuid
)
AND pvs.avg_sbp >= 181
AND pvs.last_reading BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY)
AND DATE_ADD(NOW(), INTERVAL 1 DAY);
гм, ... я вижу, как добавление дня к вашему диапазону дат дает вам нужные данные, но это решение, вероятно, подведет вас в какой-то момент; MySQL предлагает средства для работы с несоответствием между часовым поясом сервера и часовым поясом клиента; Я призываю вас прочитать эту тему, потому что доступны более надежные решения; например, у вас фактически есть будущие даты и время в ваших данных - вы действительно хотите сохранить в качестве значения last_reading момент времени, который еще не произошел?
Я хочу сделать свою просьбу еще сильнее: подумайте о том, что это означает, что вам нужно исказить значение того, что означает «сейчас», чтобы получить нужные данные; и из названий таблиц похоже, что речь идет о данных здравоохранения; вам действительно нужно изменить свои данные или иметь дело непосредственно с несоответствием часовых поясов, а не заглядывать в будущее, чтобы получить данные, которые должны быть в прошлом; пожалуйста, пожалуйста, пожалуйста, поймите, как это может пойти ужасно неправильно для того, кто думает, что результаты на самом деле означают "на данный момент"
Почему написанный запрос не дает желаемого результата? Что на самом деле означает «как только я добавляю проверку даты, она ломается»?