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

Введение

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

Затем лабораторная работа проведет вас через использование таких функций, как EXTRACT и INTERVAL, для манипулирования и запроса данных о дате и времени. Вы узнаете, как запрашивать данные по часовым поясам и вычислять разницу во времени между событиями, что даст вам практические навыки для управления временными данными в PostgreSQL.

Skills Graph

Подключение к PostgreSQL и создание таблицы events

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

Сначала откройте свой терминал. Чтобы подключиться к базе данных PostgreSQL от имени пользователя postgres, выполните следующую команду:

sudo -u postgres psql

Эта команда открывает интерактивный терминал PostgreSQL, также известный как psql shell. Вы будете использовать этот shell для выполнения SQL-команд.

Теперь давайте создадим таблицу events. Выполните следующую SQL-команду:

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

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

• id: Последовательный первичный ключ (serial primary key), который автоматически увеличивается для каждого нового события.
• event_name: Строка, содержащая название события.
• event_time: Временная метка с информацией о часовом поясе (TIMESTAMPTZ). Этот тип данных имеет решающее значение для обработки событий, происходящих в разных часовых поясах.

Чтобы проверить создание таблицы, вы можете описать структуру таблицы с помощью следующей команды:

\d events

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

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

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

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

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

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

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

В этих операторах INSERT:

• '2024-01-20 10:00:00-08 America/Los_Angeles' указывает временную метку 20 января 2024 года в 10:00 утра в часовом поясе America/Los_Angeles (UTC-8).
• '2024-01-20 18:00:00+00 Europe/London' указывает временную метку 20 января 2024 года в 18:00 в часовом поясе Europe/London (UTC+0).

Чтобы проверить вставку данных, запросите таблицу 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)

Обратите внимание, что PostgreSQL хранит временные метки в UTC (Coordinated Universal Time - Всемирное координированное время). Когда вы вставляете временную метку с часовым поясом, PostgreSQL преобразует ее в UTC и сохраняет ее внутри.

Использование EXTRACT и AT TIME ZONE

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

Сначала давайте извлечем год из столбца event_time. Выполните следующий запрос:

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

Этот запрос использует функцию EXTRACT для получения года из столбца event_time и присваивает ему псевдоним event_year. Результат будет следующим:

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

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

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

Этот запрос преобразует event_time в часовой пояс America/Los_Angeles и отображает его как los_angeles_time. Результат будет следующим:

   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 04:00:00
(3 rows)

Вы можете объединить EXTRACT и AT TIME ZONE, чтобы извлечь определенные компоненты временной метки в определенном часовом поясе. Например, чтобы узнать час событий в часовом поясе America/Los_Angeles, используйте следующий запрос:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'America/Los_Angeles') AS event_hour_la FROM events;

Это выведет час каждого события в часовом поясе America/Los_Angeles.

Вычисление разницы во времени с помощью INTERVAL

На этом шаге вы узнаете, как вычислить разницу во времени между двумя событиями, используя тип данных INTERVAL (интервал).

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

Давайте вычислим разницу во времени между 'Follow-up Meeting' (последующая встреча) и 'Conference Call' (конференц-звонок). Для этого вы можете использовать подзапрос или соединить таблицу саму с собой (join the table to itself). Вот пример с использованием соединения (join):

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. Затем он выбирает event_name из обеих таблиц и вычисляет разницу во времени между event_time 'Follow-up Meeting' и 'Conference Call'. Результат будет следующим:

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

Столбец time_difference показывает интервал между двумя событиями.

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

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    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';

Этот запрос извлекает количество дней из time_difference и отображает его как days_difference.

Наконец, выйдите из оболочки PostgreSQL:

\q

Это вернет вас в терминал.

Итог

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

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

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