Trino: How to convert timestamp to date in Presto?

Бывает так: смотришь на timestamp в Trino, а там непонятные цифры. Типа 1506929478589. Хочется нормальную дату, чтобы глаза не ломать. Или вообще разделить на дату и время для отчётов.

Тыкаешь в запрос, гуглишь — а ответ проще, чем кажется.

Базовое преобразование

Всё что нужно — это cast. Берёшь колонку с timestamp и превращаешь её в date. Всё.

SELECT
    my_timestamp,
    CAST(my_timestamp AS DATE) as my_date
FROM my_table

Второй вариант — использовать встроенную функцию date(). Работает так же.

SELECT
    my_timestamp,
    DATE(my_timestamp) as my_date
FROM my_table

Оба способа дают одинаковый результат. Разницы нет. Выбирай тот, что больше нравится.

А если timestamp в миллисекундах?

Твои данные 1506929478589 — это как раз UNIX-время в миллисекундах. Trino не поймёт это сразу как timestamp.

Сначала преврати это число в нормальный timestamp. Используй from_unixtime. Но она работает с секундами, поэтому миллисекунды нужно поделить на 1000.

SELECT
    from_unixtime(1506929478589 / 1000) as proper_timestamp

Получишь 2017-10-02 11:31:18.589. А потом уже к этому результату применяй cast или date().

Весь запрос целиком будет выглядеть так:

SELECT
    CAST(from_unixtime(1506929478589 / 1000) AS DATE) as final_date

Или так, что то же самое:

SELECT
    DATE(from_unixtime(1506929478589 / 1000)) as final_date

На выходе получишь чистую дату: 2017-10-02.

Про таймзоны

В вопросе упомянуто, что время в UTC. Функция from_unixtime возвращает timestamp в часовом поясе сессии.

Если нужно явно указать UTC, используй at_timezone.

SELECT
    DATE(from_unixtime(1506929478589 / 1000) AT TIME ZONE 'UTC')

Но если твоя сессия и так работает с UTC, то всё и так будет правильно. Это просто для строгости.

Что под капотом

Функция date() — это по сути синтаксический сахар для cast(x as date). В плане выполнения разницы нет.

Выбор между ними — дело стиля. В сложных запросах cast может быть нагляднее, особенно когда преобразований много. date() — чуть короче.

Главное помни: если исходные данные — это bigint с миллисекундами, без from_unixtime никуда. Прямое преобразование cast(1506929478589 as date) не сработает.

Итог

Алгоритм простой. У тебя есть число? Сначала делай from_unixtime(millis / 1000). Получил нормальный timestamp? Теперь оборачивай в date() или делай cast(... as date).

Всё это есть в документации по datetime-функциям. Если что-то работает — не трогай. Если не работает — проверяй тип исходных данных и делишь ли ты на 1000.