Преобразование bigint в формат даты в PostgreSQL: решение

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

Чтобы преобразовать значение поля bigint в формат даты в PostgreSQL, используйте функции to_timestamp и to_char.

SELECT to_timestamp(your_bigint) AS formatted_date FROM your_table;

Преобразование выполняется относительно времени Unix epoch. Чтобы получить дату в более понятном или заданном пользователем формате, используйте функцию to_char:

SELECT to_char(to_timestamp(your_bigint), 'YYYY-MM-DD') AS formatted_date FROM your_table;

Конвертация миллисекунд в секунды

Если значение bigint представляет время в формате Unix epoch, выраженное в миллисекундах, приведите его к секундам, разделив на 1000:

-- Волшебное число – 1000!
SELECT to_char(to_timestamp(your_bigint / 1000), 'DD/MM/YYYY HH24:MI:SS') AS formatted_date FROM your_table;

Это преобразование позволит сохранить точное время.

Приведение типов

Время от времени может возникнуть необходимость сначала привести тип bigint к тексту, а затем к дате:

-- Искренняя экспрессия чувств:
SELECT to_timestamp(your_bigint::text, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM your_table;

Исключая путаницу с timestamptz (меткой времени с часовым поясом), используйте ::timestamp для приведения к метке времени без учета часового пояса:

-- Обошли сложности отношений с часовыми поясами
SELECT to_timestamp(your_bigint)::timestamp AS formatted_date FROM your_table;

Коррекция смещения относительно Unix epoch

Если ваше значение bigint содержит смещение относительно Unix epoch, прежде проведите коррекцию:

-- Проверьте перед использованием
SELECT to_char(to_timestamp((your_bigint – offset) / 1000), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM your_table;

Такой шаг поможет сохранить точность дат с учетом временных смещений эпохи.

Предохранение от ошибок

Всегда проверьте результат после преобразования bigint в дату:

-- Null – это плохо
SELECT formatted_date FROM your_table
WHERE to_timestamp(your_bigint) IS NOT NULL;

Всегда лучше предотвратить появление нулевых значений и ошибок форматирования.

Обновление типа столбца в таблице

Если необходимо обновить представление таблицы, измените тип столбца с помощью ALTER TABLE:

-- Кардинальные изменения для таблиц
ALTER TABLE your_table ALTER COLUMN your_column TYPE timestamp USING to_timestamp(your_bigint);

Это позволит сохранить ваши данные при их преобразовании.

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

Таинственное значение bigint:

bigint: 1622476800000 // Напоминает символы из "Матрицы", неправда ли?

Превращается в легко читаемую дату благодаря возможностям PostgreSQL:

SELECT to_char(to_timestamp(1622476800000 / 1000), 'DD/MM/YYYY') AS formatted_date;

Загадка bigint разгадана:

Дата: 📅 01/06/2021 // Как видеть истинную матрицу впервые!

PostgreSQL превращает бессонные ночи, проведенные в компании с bigint, в мирные сновидения о датах.

Полезные нюансы

Часовые пояса не только для джетлага

Вкратце: если вам важна точность с учетом часового пояса, воспользуйтесь AT TIME ZONE:

-- Защищаем наши даты от изменений временных зон
SELECT to_char(to_timestamp(your_bigint) AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM your_table;

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

Управление транзакциями

При планировании значительных изменений контролируйте процесс с помощью BEGIN и COMMIT:

-- Если не уверены – не торопитесь с COMMIT
BEGIN;
-- Ваши изменения под ALTER TABLE
COMMIT;

Проверка диапазона значений bigint

Исключите возможные проблемы с преобразованием значений, выходящих за пределы, проверяя диапазон:

-- Правильное время и правильное число
SELECT to_char(to_timestamp(your_bigint), 'YYYY-MM-DD') AS formatted_date
FROM your_table
WHERE your_bigint BETWEEN '-2147483648' AND '2147483647';

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

1. PostgreSQL: Документация: 4.2. Выражения значений — Сведения о приведении типов в PostgreSQL.
2. PostgreSQL: Документация: 9.9. Функции и операторы для работы с датой и временем — Обзор возможностей PostgreSQL для преобразования bigint в дату.
3. Типы данных PostgreSQL: Дата, Время и Часовые пояса — Подробная информация о работе с датой и временем в PostgreSQL.
4. PostgreSQL: Документация: 9.8. Функции форматирования типов данных — Детальное руководство по функциям форматирования для работы с датами в PostgreSQL.