Преобразование bigint UTC в метку времени
У меня есть поле «event_timestamp», которое предположительно соответствует времени UTC, но в настоящее время является полем bigint, а не отметкой времени. Документация по этому полю:
Время (в микросекундах, UTC), когда событие было зарегистрировано на клиенте.
Вот пример значения из этого поля: 1681149457712377.
Я знаю из соответствующего поля event_date, что метка времени должна соответствовать дате 2023-04-10
Мне нужно рассчитать дельту времени между событиями, поэтому мне нужно преобразовать поле bigint event_timestamp в фактическую метку времени. Я прочитал этот ТАК пост и попробовал следующее:
select to_date(1681149457712377::text, 'YYYYMMDD')
ERROR: value for "DD" in source string is out of range
Detail: Value must be in the range -2147483648 to 2147483647.
Затем попробовал:
select trunc(TIMESTAMP 'epoch' + 1681149457712377 / 1000 * INTERVAL '1 second')
55243-07-04 # nonsensical date
Но я действительно хочу рассчитать разницу во времени в секундах между двумя из этих меток времени bigint, например. какова разница во времени в секундах между 1681149457712377 и 1681101230813726, два примера значений из моей таблицы событий. Как я могу это сделать?
Ответы 2
Во-первых, вам нужно вычесть один bigint из другого. Это даст вам их разницу в микросекундах.
Одна микросекунда составляет 1e-6 секунд. Итак, вам нужно умножить эту разницу на 1e-6, чтобы преобразовать ее в секунды.
Обратите внимание, что эта метка времени указана в микросекундах (не миллисекундах), поэтому вам нужно делить на 1 000 000, а не на 1 000.
Я не знаю, какой SQL вы используете, но во многих из них есть функция to_timestamp, которая может сделать это преобразование за вас.
Например, в snowflakeSQL вы можете использовать
select to_timestamp(1681149457712377, 6)
где 6 означает "точность" - здесь это микросекунды (10^-6)
Некоторые sql могут не иметь встроенной этой функции или могут разрешать только секунды. Затем вам нужно вручную разделить метку времени.
Микросекунда в 1000 раз меньше миллисекунды, поэтому разделите ее на 1e6. Разница, конечно, намного проще: это разница в микросекундах (вычесть), снова делимая на 1e6, без преобразования в дату.