Преобразование Unix эпохи в дату в PostgreSQL: только дата

Быстрый ответ

Для преобразования Unix-времени в дату в PostgreSQL используйте функцию TO_TIMESTAMP. Её применение предельно просто:

SELECT TO_TIMESTAMP(epoch_value);

Замените epoch_value на соответствующее значение Unix-времени, и вы обратите его в доступный для восприятия формат TIMESTAMP. Пример подобного преобразования:

SELECT TO_TIMESTAMP(1617187800); -- Результат: 2021-03-31 02:30:00+00

Если требуется получить исключительно дату, запрос будет выглядеть следующим образом:

SELECT TO_TIMESTAMP(epoch_value)::date;

Этот приём позволяет точно и просто извлечь дату из Unix-времени.

Различные форматы эпохи

Unix-время в секундах

Обычно Unix-время измеряется в секундах. Для преобразования таких значений используйте функцию TO_TIMESTAMP:

SELECT TO_TIMESTAMP(1617187800); -- Получаем TIMESTAMP '2021-03-31 02:30:00+00'

Подробнее об этом расскажет наш спикер на видео

Unix-время в миллисекундах

Чтобы перевести миллисекунды в секунды, поделите их на 1000:

SELECT TO_TIMESTAMP(epoch_value / 1000);

Вот пример запроса для Unix-времени выраженного в миллисекундах:

SELECT TO_TIMESTAMP(1617187800000 / 1000); -- Перевод в секунды

Учёт часовых поясов

При работе с часовыми поясами для корректного учета местного времени используйте следующую конструкцию:

SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + epoch_value * INTERVAL '1 second') AT TIME ZONE 'UTC'

Продвинутые методики преобразований

Манипуляции с датой

Вы можете использовать интервалы для модификации полученной даты, например, для сдвига на один день вперёд:

SELECT (TO_TIMESTAMP(epoch_value) + INTERVAL '1 day')::date;

Работа с интервалами

Для операций с интервалами можно использовать функцию extract(epoch from ...):

SELECT 'epoch' + (extract(epoch from now()) * INTERVAL '1 second') as exact_time_now;

Потенциальные проблемы и их решения

Переполнение

Будьте осторожны с переполнением, особенно при работе с Unix-временем, выраженным в миллисекундах. Используйте следующий способ приведения типов:

SELECT TO_TIMESTAMP(CAST(epoch_value as bigint) / 1000);

Совместимость версий

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

Тестирование запросов

Для обеспечения точности и надёжности преобразований протестируйте запросы на тестовой базе данных.

Преобразование к четкой дате

Если нужно извлечь только дату из Unix-времени, воспользуйтесь следующим запросом:

SELECT TO_TIMESTAMP(epoch_value)::date as just_the_date;

Визуализация

Мы можем визуализировать Unix-время как шкалу 📏, где каждая секунда отсчитывается с 1 января 1970 года:

Unix Epoch (🎇): 0 --------------------> Теперь (🕒)

Преобразование времени напоминает измерение общей длины ленты и определение соответствующего года:

SELECT TO_TIMESTAMP(1672531200); -- Результат: 🗓️ '2023-01-01'

Так мы переходим от секунд на ленте к календарной дате.

Оптимизация преобразований для производительности и читабельности

Стремление к краткости

При стремлении к краткости сделайте ваши выражения компактными – это улучшает читабельность SQL-запросов:

SELECT TO_TIMESTAMP(epoch_value)::date as conversion_result;

Описательный результат помогает уменьшить когнитивную нагрузку при чтении кода.

Знание типов данных

Непременно разберитесь со всеми типами данных с которыми вы работаете, это гарантирует точность ваших результатов.

Адаптивность

С каждым выходом новой версии PostgreSQL появляются инновации. Отслеживайте обновления и обеспечьте гибкость кода.

Полезные материалы

1. PostgreSQL: Documentation: 9.9. Date/Time Functions and Operators — официальная документация PostgreSQL по функциям даты и времени.
2. How to convert epoch to datetime redshift? – Stack Overflow — обсуждение на Stack Overflow о преобразовании Unix-времени в datetime в SQL.
3. PostgreSQL : Documentation: 9.5: 9.8. Data Type Formatting Functions : Postgres Professional — руководство по функциям форматирования типов данных в Postgres.
4. Unix Time Stamp – Epoch Converter — онлайн-инструмент для преобразования Unix-меток времени.
5. Working with Dates and Times in PostgreSQL – PostgreSQL wiki — рекомендации по работе с датами и временем в PostgreSQL.
6. PostgreSQL exercises — перечень упражнений для практики использования функций даты/времени в PostgreSQL.