Как собрать и агрегировать данные из разных часовых поясов?
Доброго дня.
Я занимаюсь размещением рекламы в интернете, и несколько месяцев назад решил написать систему аналитики моих расходов-доходов, все казалось бы просто и легко, спарсили данные из рекламной сети А Б и Ц в таблицу 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, хотя по сути мне нужен алгоритм, а не методология хранения/отображения
Простой
