Как SQL-запросом найти магазины, работающие в час ночи понедельника по времени Владивостока?

Отличный совет, но он отвечает на исходный вопрос чуть менее чем никак.

Григорий Добряков а при чём тут HL?

Alexander Orlovsky при том, что магазинов много и вычисляться должно очень быстро.

храните utc (данные), при запросах и отображениях результатов учитывайте часовой пояс.

Alexander Orlovsky пожалуйста, вместо умных советов напишите SQL-запрос.

По крайней мере начните писать. Может быть, увидите, что это нихрена не просто, как кажется.

Мне пока показали вот это. Как-то ни разу не тривиально. http://stackoverflow.com/.../perform-this-hours-of...

Григорий Добряков я понял проблему, но ИМХО тут вё-равно неправильное место для таких вопросов

есть sql.ru например или stackoverflow

Alexander Orlovsky ну что ж делать, если модератор потрёт мой пост, я не обижусь.

Григорий Добряков время работы магазина в разные дни разные? вообще без вводных данных на ваш ответ ответить можно только если случайно (на опыте или интуиции)

Alexander Orlovsky я побоялся, что большой пост будет лень читать. В разные дни - разные. Есть ещё обед. Есть ещё праздничные дни в каждой стране свои. Но для начала хотя бы с воскресеньем разобраться

Какое количество магазинов-то?

Роман Жуков не очень много, десятки тысяч, может быть сотни. Но явно не больше.

Alexander Orlovsky хранить в UTC – плохой совет. Храните в timestamptz и работайте с таймзонами в Постгресе, все инструменты для этого есть

Николай Самохвалов Если не хочется себя приколотить гвоздями к Pg, то можно без timestamptz обойтись (посмотрел – это UTC на стероидах). Но вообще спасибо, не знал про timestamptz, хотя и догадывался что в Pg есть всё

Да, это было у нас решение номер два - представить неделю как прямую ось времени на 8 дней. Почему на 8 - потому что хвост воскресенья это второй понедельник.

Григорий Добряков Время вам так и так потребуется приводить к часы UTC, иначе всё будет очень запутанно. Тогда и хвоста не будет. Если сделать хотя бы с градацией 15 минут, то получится 84 байта, что вполне терпимо.

Владислав Ярмак будет хвост, к сожалению... Ничто не мешает жителю Москвы ввести "воскресенье 23-18", и при приведении к UTC это будет "20-15", то есть мы всё равно заехали на понедельник.

Григорий Добряков А это вы уже должны закруглять интервал при составлении поисковой маски

Время в любом случае надо привести либо в UTC либо требуемую TZ, а потом попробовать представить День недели, Часы, Минуты как число DOWHHMM и сравнить с требуемым занчением

Владислав Ярмак то есть принудительно приводить хвост воскресенья к началу недели, к понедельнику? Например если юзер ввёл 11-02, то мы должны записать 11-24 воскресенья и 00-02 понедельника? А как потом восстановить обратно, чтобы в интерфейсе показать ожидаемое юзером?

Григорий Добряков Да. Если запрос был "работает ли магазин целиком в заданный интервал времени", то нужно сравнивать результат AND строки с самой маской и при равенстве ответ будет просто "да". Если запрос был "в какие часы часы работает магазин из указанного интервала", то нужно собрать все результаты AND, которые не равны строке с нулями и затем выводить интервалы от текущего момента времени, сопоставляя с датами на календаре. То есть если юзер делает запрос в в среду "в какие дни работает магазин с понедельника по пятницу", разумно выводить ближайшие даты на неделе, которые находятся не в прошлом.

Владислав Ярмак если я порежу интервал на два дня, как потом восстановить обратно то время которое ввёл администратор магазина? Чтобы в интерфейсе показать ожидаемое администратором "11-02"?

Григорий Добряков После получения вектора, когда магазин работает, нужно всегда сначала переводить каждый установленный бит в соответствующий день недели и час, понятный пользователю, а затем группировать в интервалы по дням. Если пользователю лучше понятно по календарным дням недели, а магазин работает с 10 утра до 2 часов ночи уже следующего дня, то так и писать: понедельник 10-24, вторник 00-02. Если все магазины точно закрываются на ночь, а пользователю понятнее вывод "понедельник: с 10 утра до двух ночи", то агрегировать в интервал так: каждый интервал длительностью меньше 24х часов получает тот день недели, в который начался.

Владислав Ярмак нет, попытаюсь объяснить ещё раз: вот сидит пользователь, он администратор магазина. Он поставил в интерфейсе в настройках "воскресенье 11-02". Вот такое бизнесовое требование - уметь ставить время в таком виде.

Григорий Добряков Я рассмотрел этот случай после второго "Если"

Владислав Ярмак окей, хорошее решение, спасибо!

вы меня, конечно, извините, но как воскресенье москвы превратилось во вторник камчатки? в этой стране в лучшие времена было 13часовых поясов, сейчас всего 10, не 25!ну а дату 1) можно кодом перевести 2) сделать хитрую рожу и понаставить серверов с разными локалями, там само переведётся при utc+tz

Окей, вот я сижу в таймзоне Москвы +03:00, ввожу время работы "воскресенье 23-18", оно приводится к UTC как "20-15", в базу кладётся gmt_start_hour:20 и gmt_end_hour:15.

Да, забавно.

Ок.

Тогда можно вообще забить на consist_night, если есть ночная работа -- писать 2 строки.

Григорий Соколик если я порежу интервал 11-02 на два дня (11-24 и 00-02), как потом восстановить обратно то время которое ввёл администратор магазина? Чтобы в интерфейсе показать ожидаемое администратором "11-02"?

Тогда надо уточнять задачу: можно только 2 дня аффектить интервалом?

Григорий Соколик ну мы для себя делаем, тут всё в наших руках. Скорее всего *только два*.

Надо селектить все интервалы с дня до реквестируемого по день после (их должно быть в худшем случае 6 штук), аппликейшеном находить "сшитые" и выбирать нужный.

Тоже вариант.

Александр Лурье (Alexander Lourier) вопрос скорее архитектурный - хочу советов как лучше сделать.

Всё эависит от стека технологий.

затем, что задача СУБД — не только хранить данные, но и делать операции с ними, хорошая СУБД делает это намного лучше других систем.

Николай Самохвалов (Nikolay Samokhvalov), это же история о трейдофах. С одной стороны - СУБД уже есть, можно не городить огород и всё решить на чистом SQL - в системе меньше компонентов, меньше точек отказа - это клёво. С другой - сложность решения на SQL может оказаться запретительно высокой. Кто-то же должен будет потом это поддерживать - баги там фиксить, тормоза лечить, новые фичи добавлять. Не дай бог эксперт, кто это изначально написал, уволится.

Андрей Овчаров (Andrey Ovcharov) прочитайте, пожалуйста, предыдущие комментарии (например тред с Григорием Соколиком) - там я объясняю, что проблема не в этом.

Grigoriy Dobryakov Правильно я понимаю, что проблема в том, что вы не можете придумать как хранить такие данные? Или под предложенный вариант хранения не можете придумать конвертер в слой представления? У меня вот такое ощущение сложилось.

Роман Жуков мы не можем выбрать нормальное решение в целом. И как хранить, и как вводить, и как выводить, и как искать. Каждый вариант несёт в себе те или иные костыли.

Vitaly Levchenko как сохранить "воскресенье 11-02"? Это какой день недели?

Григорий, неделю считаем с отсчётом от одного фиксированного дня. В таком случае день недели понятным образом интерпретируется, но в целом отсутствует в схеме.

Александр Акафьев конечно, если переписать бизнесовое условие под возможности технологий, то любая задача решится тривиально

Григорий Добряков, а разве у бизнесового требования есть условие на формат хранения ?

Григорий Добряков, хранить все интервалы работы в общем надо. Потому что иначе вылезет вопрос "а как тогда чтоб работало с вечера пятницы до утра понедельника" ?

Допустим, я сижу в таймзоне Москвы +03:00, ввожу время работы "воскресенье 23-18", оно приводится к UTC как "20-15", в базу кладётся opened_at:20 и closed_at:15.

В базу придется класть полную дату, а не только время. То есть мы берем 1970-00-00 00:00:00, прибавляем к нему 6 DAYS 23 HOURS 18 MINUTES и в таком виде сохраняем в базу с текущей таймзоной. Тогда все должно правильно высчитываться.

Pavel Chebotarev 23—18 это с 23 часов воскресенья до 18 часов понедельника. Как это хранить? Как по нему искать?

А, я не так понял. Тогда все не так просто. Надо хранить интервал времени в пределах самой первой недели отсчета (если не пересекает полночь воскресенья, то это один интервал, если пересекает - то два интервала - от времени открытия до воскресенья 23:59:59 и от понедельника 0:0:0 до времени закрытия), а дальше пользовательский запрос превращать в такую же дату от 1970-1-1 0:0:0 и сверять с помощью BETWEEN с конвертацией в таймзону.

Чуть проще: время открытия - интервал от начала недели rel_open, время закрытия - интервал от времени открытия rel_close_in

вот тут, например, на русском разжёвано https://habrahabr.ru/post/273177/

Николай Самохвалов Эх... Окей, вот я сижу в таймзоне Москвы +03:00, ввожу время работы ночного клуба "воскресенье с 23 до 18", оно приводится к UTC как "20-15", в базу кладётся starts_at:20 и ends_at:15 с указанием таймзоны +03.

Grigoriy Dobryakov прочитай статью ну или я сейчас освобожусь и покажу на твоём примере

Николай Самохвалов лучше покажи. Потому что по всему треду выше, как только я объяснял про суть перехода через полночь, каждый начинал предлагать свои костыли.

Блин. С такой ужасной постановкой даже я в правильном решении ошибку сделал. Отрезки сохранять на интервале, и не двигать их за него.