Как работать с датой записанной в поле формата числа с плавающей точкой?

Question

[Искатель]

Каждый раз, когда пытаюсь вручную писать SQL-запросы, спотыкаюсь об работу с датой/временем. Что у нас есть: есть отдельные типы полей, специально созданные для даты/времени: DATETIME И TIMESTAMP, есть ещё отдельно DATE и TIME... что-то уже много...

С базой PostgreSQL я имел меньше опыта чем скажем с MySQL, а тут мне попалась база PostgreSQL где для даты/времени используется поле типа с плавающей точкой double precision. Сутки уже мозгую как с этим работать.
Если вывести содержимое поля как есть, то выведется число 45672.38114849537
Спросил у нейросети, говорит нужно использовать функцию to_timestamp, делаю запрос:

SELECT to_timestamp(start_time) FROM TReportAppUsage;

выводит 1970-01-01 12:41:13.617443+00 хотя на самом деле должен быть 2025 год
При этом в описании структуры базы сказано, что отсчёт даты ведётся не с 01.01.1970 (начало эпохи Unux), а с 30.12.1899.
Помогите продраться сквозь SQL-премудрости и понять как вывести на консоль дату/время из этого поля. И наверное на этом вопрос будет не исчерпан, а сразу жет перейдёт в следующий вопрос, что в опции WHERE писать, чтобы выполнить фильтрацию по подобной дате.

Comments

[Everything_is_bad]

это какой-то свой формат, а не timestamp

Ио при этом в описании структуры базы сказано, что отсчёт даты ведётся не с 01.01.1970 (начало эпохи Unux), а с 30.12.1899

показывай один в один текст приложенного описания или пинай разработчика и спрашивай чё это

[maksam07]

Если вывести содержимое поля как есть, то выведется число 45672.38114849537

А туда данные корректно записываются? Какое-то странное число..

[Искатель]

Админы хабра, зачем редактируйте мой вопрос, вставляя в него ошибки?

Answer 1

[Rsa97]

Этот способ хранения даты тянется ещё с Lotus 1-2-3. Когда Microsoft добавляла дату/время в Excel, то для совместимости использовали тот же формат.
Отсчёт ведётся от 30.12.1899. Целая часть - количество дней, прошедших с этой даты, дробная часть - время. Если дробную часть умножить на 24*60*60, то получим количество секунд с начала дня.
Встроенных функция для преобразования в PostgreSQL нет, так что, видимо, это прямой перенос из чего-то типа MSAccess.
45672.38114849537 соответствует 2025-01-15 09:08:51.230
Получить можно, например, так:

SELECT
  date_lotus,
  '1899-12-30'::date + MAKE_INTERVAL(days => date_lotus::int, secs => ((date_lotus - date_lotus::int) * 24*60*60)::int) AS datetime
  FROM test;

| date_lotus        | datetime            |
| ----------------- | ------------------- |
| 45672.38114849537 | 2025-01-15 09:08:51 |