Как собрать и агрегировать данные из разных часовых поясов?
Доброго дня.
Я занимаюсь размещением рекламы в интернете, и несколько месяцев назад решил написать систему аналитики моих расходов-доходов, все казалось бы просто и легко, спарсили данные из рекламной сети А Б и Ц в таблицу spend, далее собрали все что заработали в различных местах в таблицу revenue. Свели дебет с кредитом и вуоля, профит.
Но в реальности оказалось совсем все не так легко и очевидно. Попробую сделать полную постановку задачи, и возможно кто-то из Вас сможет мне помочь, за что я буду бесконечно благодарен :)
Рекламные сети в которых мы платим за размещение нашей рекламы находятся в разных странах и считают всю статистику в своем часовом поясе (TZ), без возможности изменения этого самого часового пояса. Таким образом, на входе имеем следующие параметры:
Сеть А - tz -04:00 (New York)
Сеть B - tz +00:00 (Barcelona)
Сеть C - tz +03:00 (Moscow)
Сеть D - tz +07:00 (Thailand)
Абсолютно идентичная ситуация с сервисами, в которых мы получаем деньги как affiliate. Для ясности пусть это будут Партнерские Программы (ПП):
ПП1 -02:00
ПП2 +00:00
ПП3 +03:00
ПП4 +07:00
Задача состоит в том, чтобы каждые 5 минут собирать статистику расходов и доходов, вычислять на ее основе профит и другие различные параметры и коэффициенты (конверсия из покупки в продажу, конверсия лендингпейджей, коэффициенты для АБ тестирования, географические срезы и т.д.) и отображать в одном дашборде красивыми графиками и диаграммами :)
Проблема: Так как во всех сетях статистика в своем часовом поясе, при сборе за "вчера" мы получим цифры, которые в действительности не являются цифрами за "вчера", потому что мы находимся в МСК часовом поясе, и смотрим на все из него. В тайских сетках "сегодня" закончится, когда в Москве 20:00, и все последующие цифры будут писаться в "завтра", но в МСК еще "вчера". Когда в МСК наступит новый день, то "завтра", которое было для Тайланда, станет "сегодня" МСК и Тая, и теперь во "вчера" записывать все доходы и расходы для тех, кто в Барсе и Нью йорке. И так по-кругу.
Уточнения: У нас нет возможности в интерфейсе сети или ПП получить доходрасход по часам для каждой рекламной единицы. Зачастую вообще нет возможности делить статистику никак кроме как по дате и странам.
Ранее я придумал примерно такую схему: так как мы собираем статы для каждого промо раз в 5 минут, то если в 23:55 у промо id=12345 будет расход 10$ и доход 12$, мы можем зафиксировать этот факт на уровне БД, имея например 2 колонки с датами, одна (localDateTime) это DATETIME в зоне сетиПП, вторая колонка по МСК (mskDateTime). Соответственно когда в Москве 00:05, расход по промо=12345 будет 11$, доход все еще 12$, так как по МСК начались новые сутки, мы будем передавать сумму поправки 10$ (на которых закончилось "вчера по мск") каждый сбор, до тех пор пока в целевой сетиПП не сменятся сутки. Но в итоге эта схема не подходит, если вдруг мы не собирали данные на момент перехода суток в разных TZ, и образуется большая погрешность. Равно как и если мы сейчас хотим собрать и обработать задним числом уже имеющуюся статистику.
Для примера: Если в 23:55 по мск мы имели расход 10$ то в сетке (-04:00 New York) - в 06:55 утра МСК у нас будет например 30$, а в 7 утра по МСК уже 0$. Выбирая данные из таблицы за "сегодня", чем то типа SELECT * FROM spend WHERE `date` BETWEEN '2021-08-15 00:00:00' AND '2021-08-15 23:59:59' мы получим фигню... :( потому что до 7 утра там будет высокий расход, который не соответствует реальности текущего дня и маленьких доход, а после 7 утра, расход пропадет целиком, а доходы останутся из тех ПП, которые находятся в других TZ. Если поправка 10$ о которой я говорил выше будет, то 30$ на которых закончилось "вчера" минус 10$ поправки = 20$ + 0$ (уже сегодня) получится как раз "сегодня" по МСК, тоесть сегодня по МСК мы израсходовали 20$ (зная что у нас расхождение на 5 минут между 23:55 и 23:59:59)
Возможно задачу я сформулировал ужасно сумбурно и ничего непонятно. Если это так, поясните пожалуйста как надо ее составить :)
ЗЫ: Жду ваших умных комментариев, спасибо, за Ваше уделенное время.
Дополнительно:
Лентюй, Не умеет, ничего общего с ГА к сожалению данная задача не имеет
Ответы:
Переводите все местное время в какой-то единый стандарт. Лучше всего подходит UTC. Везде в запросах оперируйте UTC, только при выводе на экран конвертируйте обратно в свое локальное время.
UTC использовать проще всего, чем скажем MSK, т.к. конвертация из локального времени в UTC уже встроена в язык.
- Вы не поняли суть проблемы. В отображении или хранении проблемы нету, проблема в том, что там где мы получаем данные они соответствуют только тому часовом поясу в котором работает сервис.
Пример: за 24 часа по UTC+0 израсходовано 378$, но за 24 часа UTC+03 312$. Таким образом 65$ потрачены в то время когда в Москве было с 00:00 до 03:00.
Задача соответственно придумать алгоритм вычисления этой самой разницы в 65$ которую мы не можем получить просто запросом
- cpacash, Похоже вы не поняли суть ответа.
Вам надо изменить запрос. В запросе конвертируйте локальное время из базы данных в UTC и сравнивайте с заданным UTC временем. - res2001, от того что я изменю запрос 378 не разделится магическим образом на 312 и 65, потому что когда я делаю запрос в сеть, я могу передать только дату и в качестве ответа буду получать всегда 378$ за например 10.08.2021. Если я спрошу через неделю и сделаю сдвиг utc+0500 это ничего не изменит, мне вернут 378$.
А задача состоит в том, чтоб алгоритмически разделить и вычислить с минимальной погрешностью сколько реально было израсходовано/заработано по дефолтному часовому поясу (в моем случае мск+0300)Скорее всего я неверно указал тэг у вопроса и попал в раздел SQL, хотя по сути мне нужен алгоритм, а не методология хранения/отображения
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для сбора и агрегирования данных из разных часовых поясов вам потребуется использовать язык программирования, который позволяет работать с датами и временем. Например, вам может пригодиться PHP, который имеет встроенные функции для работы с датами и временем.
Для начала вам нужно определить, откуда и какие данные вы будете собирать. Это может быть база данных, API или файлы с данными. После этого вы можете использовать функции PHP для преобразования времени из разных часовых поясов в один общий часовой пояс.
Пример кода на PHP для работы с разными часовыми поясами:
// Устанавливаем часовой пояс по умолчанию date_default_timezone_set('Europe/Moscow'); // Получаем текущую дату и время $now = new DateTime(); // Преобразуем время из другого часового пояса $now->setTimezone(new DateTimeZone('America/New_York')); echo 'Current time in New York: ' . $now->format('Y-m-d H:i:s'); // Преобразуем время из еще одного часового пояса $now->setTimezone(new DateTimeZone('Asia/Tokyo')); echo 'Current time in Tokyo: ' . $now->format('Y-m-d H:i:s'); // Другие операции с временем и датой
Этот пример демонстрирует, как можно получить текущее время в разных часовых поясах и вывести его. Вы можете использовать аналогичные методы для сбора данных из разных источников и агрегирования их в удобном для вас формате.
Не забывайте также обработать возможные ошибки и исключения при работе с датами и временем, чтобы ваш код был надежным и стабильным.