Строки, текст, дата и время, таймзоны

[Egor_DBA]

Урок 14. Строки, текст, дата и время, таймзоны

В прошлых уроках мы разобрали числа и базовые типы. Теперь беремся за два семейства, которые приносят больше всего боли на практике: строки и моменты времени. Разберем, чем отличаются char, varchar и text, как кодировка и COLLATE влияют на сортировку и поиск, какие функции реально нужны для работы со строками. Потом перейдем к датам и времени: date, time, timestamp, timestamptz, интервалы и главный источник граблей в любом проекте на postgresql - таймзоны. По ходу будем гонять запросы на демобазе Авиаперевозки от Postgres Professional, где как раз полно полей со временем вылета.



Как это работает

Начнем со строк. В PostgreSQL есть три текстовых типа, но внутри они почти близнецы. char(n) - строка фиксированной длины, которая дополняется пробелами справа до n символов. varchar(n) - строка с ограничением сверху на длину. text - строка без ограничения длины. Важная мысль: varchar(n) и text хранятся одинаково и работают с одинаковой скоростью, ограничение длины - это просто проверка при вставке, а не способ сэкономить место. Поэтому в современных схемах char почти не используют (он только мешает лишними пробелами), а выбор обычно стоит между varchar(n) ради смысловой валидации и text для всего остального.

Длина строки в PostgreSQL считается в символах, а не байтах, и это второй ключевой момент. Кодировку задает база при создании, и стандарт на 2026 год - UTF-8. В UTF-8 латинская буква занимает 1 байт, а кириллическая - 2 байта. Поэтому char_length дает число символов, а octet_length - число байт, и для русского текста они различаются вдвое. Если перепутать их в проверке лимита поля, можно неожиданно обрезать данные.

Сортировка и сравнение строк зависят от правила сопоставления - COLLATE. Это набор правил конкретного языка: где какая буква стоит по алфавиту, учитывается ли регистр. Сравнение abc < ABC может дать разный результат в зависимости от collation. По умолчанию берется правило базы данных, но COLLATE можно навесить точечно в ORDER BY или прямо на столбец. С версии 15 PostgreSQL умеет ICU-коллации как основные для базы, и это более стабильное и предсказуемое поведение, чем у старых системных libc-локалей.

Теперь время. Тут важно держать в голове одно разделение. timestamp (он же timestamp without time zone) хранит дату и время как есть, без привязки к поясу - просто числа на стене часов. timestamptz (timestamp with time zone) хранит момент абсолютного времени: внутри это всегда UTC, а пояс используется только при вводе и выводе. То есть timestamptz не хранит вашу таймзону - он приводит входное значение к UTC и потом показывает его в поясе текущей сессии. Это и есть главная развилка всего урока.

SQL и примеры

Сначала строки. Посмотрим разницу длины в символах и байтах:

Код: Выделить всё

SELECT char_length('Привет') AS chars,   -- 6 символов
       octet_length('Привет') AS bytes;  -- 12 байт в UTF-8

Базовый набор строковых функций, который стоит знать наизусть. Здесь же показано, что char(n) добавляет хвост из пробелов:

Код: Выделить всё

SELECT upper('aero') AS up,                  -- AERO
       lower('SVO') AS low,                  -- svo
       left('Москва', 3) AS l,               -- Мос
       substring('SVO-LED' FROM 5) AS sub,   -- LED
       trim('  hi  ') AS trimmed,            -- hi
       'SVO' || '-' || 'LED' AS concat,      -- SVO-LED
       length('abc'::char(8)) AS char_len;   -- 3, хвостовые пробелы не в счет

Поиск по образцу: LIKE учитывает регистр, ILIKE - нет. Найдем аэропорты, в названии города которых встречается Москва (поле city в airports - это тип jsonb с переводами, но для примера возьмем текстовое представление):

Код: Выделить всё

SELECT airport_code, city
FROM   airports
WHERE  city->>'ru' ILIKE '%москв%';

Сортировка с явной коллацией. Сравним порядок по русскому правилу:

Код: Выделить всё

SELECT airport_name->>'ru' AS name
FROM   airports
ORDER BY airport_name->>'ru' COLLATE "ru-RU-x-icu";

Переходим к времени. Демобаза хранит время вылета в двух полях: scheduled_departure и actual_departure, оба типа timestamptz. Посмотрим, на сколько реально задержали рейсы, через интервал-арифметику:

Код: Выделить всё

SELECT flight_id,
       actual_departure - scheduled_departure AS delay
FROM   flights
WHERE  actual_departure IS NOT NULL
ORDER BY delay DESC NULLS LAST
LIMIT 5;

Вычитание двух timestamptz дает interval - тип для длительностей. Его можно прибавлять и сравнивать. Текущий момент и усечение до часа:

Код: Выделить всё

SELECT now()                              AS moment,      -- момент с поясом
       date_trunc('hour', now())         AS hour_start,  -- начало часа
       extract(dow  FROM now())          AS day_of_week, -- 0=воскресенье
       extract(epoch FROM interval '90 minutes') AS secs; -- 5400

Сгруппируем вылеты по дням - частый аналитический запрос. date_trunc срезает время до начала суток:

Код: Выделить всё

SELECT date_trunc('day', scheduled_departure) AS day,
       count(*) AS flights
FROM   flights
GROUP  BY day
ORDER  BY day;

И главный фокус с таймзонами. Одно и то же абсолютное время показывается по-разному в зависимости от пояса сессии:

Код: Выделить всё

SET timezone = 'UTC';
SELECT scheduled_departure FROM flights WHERE flight_id = 1;
SET timezone = 'Europe/Moscow';
SELECT scheduled_departure FROM flights WHERE flight_id = 1;
-- значение в базе одно, на экране сдвиг на 3 часа

Явно перевести момент в нужный пояс можно оператором AT TIME ZONE:

Код: Выделить всё

SELECT scheduled_departure AT TIME ZONE 'Asia/Vladivostok' AS local_vvo
FROM   flights WHERE flight_id = 1;

Частые грабли

• Колонка timestamp вместо timestamptz для событий. Без пояса вы потеряете информацию о моменте: данные с разных серверов смешаются, и при смене пояса сервиса все поедет. Для абсолютных событий почти всегда нужен timestamptz.
• Иллюзия, что timestamptz хранит таймзону. Он хранит UTC. Если вам нужно запомнить именно исходный пояс пользователя - заводите отдельную колонку с его именем.
• char(n) и невидимые пробелы. Сравнения и конкатенация с дополненной пробелами строкой дают сюрпризы. Не используйте char без явной причины.
• Путаница char_length и octet_length для кириллицы. Лимит поля в байтах при UTF-8 урежет вдвое меньше русских символов, чем кажется.
• now() внутри транзакции возвращает время старта транзакции, а не реальный текущий момент. Для настоящего стенного времени берите clock_timestamp().
• Сравнение timestamptz и timestamp в одном выражении: PostgreSQL неявно докрутит timestamp к поясу сессии, и результат зависит от текущего timezone. Приводите типы явно.
• Сортировка строк ломается при смене locale базы. Не полагайтесь на порядок по умолчанию для бизнес-логики - задавайте COLLATE явно там, где порядок важен.

Мини-лаба

• Создайте таблицу: CREATE TABLE tz_demo (id int GENERATED ALWAYS AS IDENTITY, label text, ts timestamp, tstz timestamptz);
• Вставьте одну строку со значением 2026-06-14 12:00:00+03 в оба поля ts и tstz.
• Выполните SET timezone = 'UTC'; и сделайте SELECT - запишите, что показали ts и tstz.
• Выполните SET timezone = 'Europe/Moscow'; повторите SELECT и сравните: какое поле изменилось, а какое нет, и объясните почему.
• Посчитайте char_length и octet_length для строки из вашего имени на русском.
• Через date_trunc и extract достаньте из tstz начало суток и номер дня недели.
• Удалите таблицу: DROP TABLE tz_demo;

Контрольные вопросы

• Чем varchar(n) отличается от text по хранению и по поведению, и когда оправдан char(n)?
• Почему для русского текста char_length и octet_length дают разные числа?
• Что физически хранит timestamptz и в какой момент применяется таймзона?
• Чем now() отличается от clock_timestamp() внутри транзакции?
• Зачем нужен COLLATE и как он влияет на ORDER BY и сравнение строк?
• Что делает оператор AT TIME ZONE с типом timestamptz?

[Yxcvbn]

А если у меня колонка уже timestamp без зоны и данных уже миллион строк - как безопасно перевести в timestamptz, чтобы старые значения не сдвинулись на 3 часа?

[alex1967]

Поймал на проекте для 1с: лимит поля задал в символах, а драйвер считал в байтах, кириллица обрезалась ровно посередине. Теперь везде text без длины и проверка на стороне приложения.