Каждый раз, когда я пытаюсь вручную писать SQL-запросы, у меня возникают трудности с работой с датами и временем. Я знаю, что в базе данных существуют разные типы полей для этого: DATETIME и TIMESTAMP, а также DATE и TIME - их достаточно много. <br/><br/> У меня меньше опыта с PostgreSQL, чем с MySQL, и недавно я столкнулся с базой данных PostgreSQL, где для хранения даты и времени используется поле типа с плавающей точкой double precision. Я уже несколько дней пытаюсь понять, как с этим работать. Например, если я вывожу содержимое поля, то получаю число 45672.38114849537. <br/> Я обратился к нейросети за помощью, и она посоветовала использовать функцию to_timestamp, в результате чего я выполнил запрос: <br/> SELECT to_timestamp(start_time) FROM TReportAppUsage; <br/> Однако вывод оказался 1970-01-01 12:41:13.617443+00, хотя на самом деле это должно быть в 2025 году. При этом в описании структуры базы данных указано, что отсчет даты начинается не с 01.01.1970 (эпоха Unix), а с 30.12.1899. <br/> Могли бы вы помочь разобраться в этих SQL-вопросах и объяснить, как корректно вывести дату и время из этого поля? Кроме того, что нужно писать в условии WHERE для фильтрации по такой дате?
Ваша ситуация действительно требует немного специфического подхода к работе с датами. Основная проблема здесь заключается в том, что значение, которое вы видите в вашем поле (число с плавающей точкой), вероятно, представляет собой количество дней с определенной начальной даты.
Судя по вашему описанию, начальная дата в данном случае — это 30 декабря 1899 года. В PostgreSQL вы можете преобразовать это значение в корректный формат даты и времени с помощью операций с числовыми значениями.
### 1. Преобразование числа с плавающей точкой в дату
Для преобразования числа с плавающей точкой (days) в дату, вы можете использовать функцию `to_timestamp`, но сначала надо привести число дней к точному значению, добавив его к начальной дате с помощью типа `interval`.
Вот пример SQL-запроса, который делает это:
```sql
SELECT
'1899-12-30'::date + (start_time * interval '1 day') AS converted_date
FROM
TReportAppUsage;
```
В этом запросе мы добавляем количество дней (умноженное на интервал в 1 день) к дате `1899-12-30`.
### 2. Фильтрация по дате в условии WHERE
Если вы хотите фильтровать записи по дате, вы также выполните преобразование в условии. Например, если вы хотите получить все записи с датой после 1 января 2025 года, вы можете написать следующий запрос:
```sql
SELECT *
FROM
TReportAppUsage
WHERE
'1899-12-30'::date + (start_time * interval '1 day') > '2025-01-01';
```
Таким образом, вы гарантируете, что SQL-сервер правильно интерпретирует и сравнивает даты.
### Итоги
- Чтобы преобразовать дни с плавающей точкой в дату, добавьте их к дате 30 декабря 1899 года, используя интервал в 1 день.
- Для фильтрации по дате повторите преобразование в условии `WHERE`.
Этот подход должен помочь вам правильно обрабатывать даты в PostgreSQL с использованием числа с плавающей точкой.
Этот способ хранения даты тянется ещё с 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, '1889-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 | 2015-01-16 09:08:51 |