Операции с датой и временем в PostgreSQL

Введение

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

Вы начнете с создания таблицы, предназначенной для хранения данных о событиях, используя тип данных TIMESTAMPTZ для обеспечения осведомленности о часовом поясе. Затем вы вставите данные для событий, происходящих в разных часовых поясах, и узнаете, как PostgreSQL нормализует эти данные. Наконец, вы попрактикуетесь в запросах и манипулировании этими временными данными, используя такие функции, как EXTRACT, предложение AT TIME ZONE и вычисление разницы во времени с помощью INTERVAL.

Создание таблицы для событий, связанных со временем

На этом шаге вы подключитесь к базе данных PostgreSQL и создадите таблицу для хранения информации о событиях. Эта таблица будет использовать тип данных TIMESTAMPTZ, который необходим для точного хранения временных меток из различных часовых поясов.

Понимание TIMESTAMPTZ

Тип данных TIMESTAMPTZ (timestamp with time zone) в PostgreSQL предназначен для работы с датами и временем со всего мира. Когда вы вставляете значение с определенным часовым поясом, PostgreSQL преобразует его в Всемирное координированное время (UTC) для хранения. Эта стандартизация упрощает сравнение и расчет времени независимо от их исходного часового пояса.

Шаг 1: Подключение к PostgreSQL

Сначала откройте терминал. Подключитесь к базе данных PostgreSQL с помощью интерактивной оболочки psql. Все последующие команды SQL вы будете выполнять в этой оболочке.

sudo -u postgres psql

Теперь вы увидите приглашение PostgreSQL, которое выглядит как postgres=#.

Шаг 2: Создание таблицы events

Теперь выполните следующую команду SQL для создания таблицы events. Эта таблица будет хранить идентификатор, название и время для каждого события.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

Эта команда создает таблицу с именем events и тремя столбцами:

• id: Автоинкрементный первичный ключ.
• event_name: Текстовое поле для названия события.
• event_time: Временная метка с поддержкой часового пояса.

Шаг 3: Проверка создания таблицы

Чтобы убедиться, что таблица была создана правильно, вы можете проверить ее структуру с помощью команды \d.

\d events

Вы должны увидеть схему таблицы events, подтверждающую ее столбцы и типы данных.

                                     Table "public.events"
   Column   |          Type          | Collation | Nullable |              Default
------------+------------------------+-----------+----------+-----------------------------------
 id         | integer                |           | not null | nextval('events_id_seq'::regclass)
 event_name | character varying(255) |           |          |
 event_time | timestamp with time zone |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)

Вы успешно настроили таблицу для хранения данных, основанных на времени.

Вставка данных с информацией о часовом поясе

На этом шаге вы вставите записи в таблицу events. Каждая запись будет иметь временную метку, связанную с определенным часовым поясом, демонстрируя, как PostgreSQL обрабатывает данные из разных географических местоположений.

Шаг 1: Вставка данных о событиях

Находясь в оболочке psql, выполните следующие операторы INSERT для добавления трех событий в вашу таблицу. Обратите внимание, как каждая event_time включает идентификатор часового пояса.

Вставьте событие конференц-связи из Лос-Анджелеса (UTC-8):

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');

Вставьте событие совещания по проекту из Лондона (UTC+0):

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');

Вставьте событие последующего совещания, также из Лос-Анджелеса:

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');

Шаг 2: Проверка вставки данных

Чтобы увидеть, как PostgreSQL сохранил эти данные, выполните запрос к таблице events.

SELECT * FROM events;

Вы должны увидеть следующий вывод.

 id |    event_name     |        event_time
----+-------------------+------------------------
  1 | Conference Call   | 2024-01-20 18:00:00+00
  2 | Project Meeting   | 2024-01-20 18:00:00+00
  3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)

Обратите внимание, что все значения event_time отображаются в UTC (обозначено смещением +00). Например, 10:00:00 в Лос-Анджелесе (UTC-8) было преобразовано в 18:00:00 UTC. Это внутреннее преобразование позволяет выполнять последовательные сравнения по времени.

Запрос и форматирование временных меток

Теперь, когда у вас есть данные, хранящиеся в UTC, вы можете запрашивать их и отображать в любом нужном вам часовом поясе. На этом шаге вы будете использовать функцию EXTRACT и предложение AT TIME ZONE для манипулирования и форматирования ваших временных меток.

Шаг 1: Извлечение частей временной метки с помощью EXTRACT

Функция EXTRACT позволяет извлекать определенные компоненты (например, год, месяц, час) из временной метки. Давайте получим год для каждого события.

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

Запрос извлекает год из сохраненной временной метки UTC.

    event_name     | event_year
-------------------+------------
 Conference Call   |       2024
 Project Meeting   |       2024
 Follow-up Meeting |       2024
(3 rows)

Шаг 2: Преобразование временных меток с помощью AT TIME ZONE

Чтобы просмотреть сохраненное время UTC в определенном местном часовом поясе, используйте предложение AT TIME ZONE. Давайте преобразуем все времена событий обратно в часовой пояс America/Los_Angeles.

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

Этот запрос берет event_time в UTC и отображает его эквивалентное местное время в Лос-Анджелесе.

    event_name     |   los_angeles_time
-------------------+---------------------
 Conference Call   | 2024-01-20 10:00:00
 Project Meeting   | 2024-01-20 10:00:00
 Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)

Как вы можете видеть, 'Project Meeting', которое произошло в 18:00:00 UTC, правильно отображается как 10:00:00 по времени Лос-Анджелеса.

Шаг 3: Объединение EXTRACT и AT TIME ZONE

Вы можете объединить эти функции для более мощных запросов. Например, чтобы найти час каждого события, как оно произошло в часовом поясе Europe/London:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;

Этот запрос сначала преобразует время в лондонский часовой пояс, а затем извлекает час.

    event_name     | event_hour_london
-------------------+-------------------
 Conference Call   |                18
 Project Meeting   |                18
 Follow-up Meeting |                20
(3 rows)

Расчет разницы во времени

Распространенное требование — рассчитать продолжительность между двумя событиями. В PostgreSQL вычитание одной временной метки из другой дает результат типа данных INTERVAL, который представляет собой продолжительность времени.

Шаг 1: Расчет разницы между двумя событиями

Давайте найдем время, прошедшее между 'Conference Call' и 'Follow-up Meeting'. Мы можем добиться этого, объединив таблицу events саму с собой.

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    e2.event_time - e1.event_time AS time_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Этот запрос работает следующим образом:

• Создаются два виртуальных экземпляра таблицы events, e1 и e2.
• e1 фильтруется только до строки 'Conference Call'.
• e2 фильтруется только до строки 'Follow-up Meeting'.
• event_time из e1 вычитается из e2.

Результатом является значение типа INTERVAL.

     event1      |      event2       | time_difference
-----------------+-------------------+-----------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

Разница во времени составляет 1 день и 2 часа.

Шаг 2: Извлечение компонентов из INTERVAL

Вы также можете использовать EXTRACT для типа INTERVAL, чтобы получить определенные части продолжительности, такие как количество дней или часов. Давайте извлечем только количество дней из предыдущего расчета.

SELECT
    EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Это даст вам числовое значение для дней.

 days_difference
-----------------
               1
(1 row)

Шаг 3: Выход из PostgreSQL

Вы завершили упражнения для этой лаборатории. Теперь вы можете выйти из оболочки psql и вернуться в основной терминал.

\q

Резюме

В этой лаборатории вы изучили основные операции с датой и временем в PostgreSQL. Вы узнали, как:

• Создавать таблицу с использованием типа данных TIMESTAMPTZ для корректного хранения информации с учетом часовых поясов.
• Вставлять временные метки с указанными часовыми поясами и наблюдать, как PostgreSQL преобразует их в UTC для хранения.
• Использовать функцию EXTRACT для извлечения конкретных компонентов, таких как год или час, из временной метки.
• Использовать предложение AT TIME ZONE для отображения сохраненных времен UTC в любом желаемом местном часовом поясе.
• Рассчитывать продолжительность между двумя событиями, которая дает результат типа INTERVAL, и извлекать компоненты из этого интервала.

Эти навыки являются основополагающими для создания надежных приложений, которые точно управляют временными данными в различных регионах.