Как конвертировать double precision в дату в PostgreSQL

При работе с PostgreSQL вы можете столкнуться с полями, где дата и время хранятся в виде числа с плавающей точкой double precision. Это нестандартный формат, который часто вызывает трудности у разработчиков, привыкших к типу timestamp или date. В этой статье мы подробно разберём, как преобразовать такое число в читаемую дату и выполнить фильтрацию записей.

Почему double precision используется для хранения дат

В некоторых старых или специализированных базах данных (например, из ERP-систем или финансовых приложений) даты хранятся как количество дней от определённой точки отсчёта. В вашем случае отсчёт ведётся с 30 декабря 1899 года, а не с 1 января 1970 (Unix-эпоха). Число 45672.38114849537 означает количество дней (включая дробную часть для времени), прошедших с этой базовой даты.

Как конвертировать double precision в timestamp

Стандартная функция to_timestamp() в PostgreSQL ожидает количество секунд от Unix-эпохи (1970-01-01). Чтобы получить корректную дату, нужно пересчитать дни в секунды и учесть смещение начала отсчёта. Используйте следующий запрос:

SELECT to_timestamp((start_time - 25569) * 86400) FROM TReportAppUsage;

Здесь 25569 - это количество дней между 30.12.1899 и 01.01.1970, а 86400 - число секунд в сутках. После выполнения запроса вы получите корректную дату, например 2025-01-15 09:08:51+03.

Проверка результата и работа с часовыми поясами

По умолчанию to_timestamp() возвращает значение с часовым поясом вашей сессии. Если нужно получить время без зоны, используйте приведение к типу timestamp without time zone:

SELECT (to_timestamp((start_time - 25569) * 86400))::timestamp FROM TReportAppUsage;

Для точной настройки под конкретный регион применяйте AT TIME ZONE.

Фильтрация записей по дате в формате double precision

Чтобы отфильтровать строки по дате, не нужно конвертировать каждую запись - это замедлит запрос. Вместо этого преобразуйте условие фильтрации в число. Например, если нужно найти все записи за 1 марта 2025 года:

SELECT * FROM TReportAppUsage WHERE start_time >= (EXTRACT(epoch FROM '2025-03-01'::date) / 86400 + 25569) AND start_time < (EXTRACT(epoch FROM '2025-03-02'::date) / 86400 + 25569);

Этот подход использует индекс по полю start_time и работает значительно быстрее.

Создание представления для удобства

Если вам часто нужно работать с этим полем как с датой, создайте представление (view) с вычисляемым столбцом:

CREATE VIEW report_dates AS SELECT id, (to_timestamp((start_time - 25569) * 86400))::timestamp AS event_date FROM TReportAppUsage;

Теперь вы можете писать простые запросы: SELECT * FROM report_dates WHERE event_date = '2025-03-01'.

Распространённые ошибки и их решения

Ошибка: Результат to_timestamp показывает 1970 год. Причина - вы передаёте количество дней, а функция ожидает секунды. Всегда умножайте на 86400.

Ошибка: Дата плавает на несколько часов. Проверьте, не содержит ли исходное число временную зону. Иногда double precision хранит время по UTC, и требуется корректировка.

Ошибка: Медленная фильтрация. Не используйте WHERE to_timestamp(...) = '2025-01-01' - это вызовет полное сканирование таблицы. Всегда преобразуйте дату в число.