Форматирование даты в PostgreSQL: полное руководство
Пересказ статьи DbVisualizer. Date Formatting in Postgres: A Comprehensive Guide
PostgreSQL поддерживает различные варианты форматирования даты. В этом руководстве мы сфокусируемся на таком способе форматировании дат, которое является одновременно читабельным и понятным для пользователей PostgreSQL.
Инструменты, используемые в этом руководстве
DbVisualizer, популярный инструмент управления базами данных и SQL-клиент.
Понимание типов данных даты и времени в PostgreSQL
PostgreSQL поддерживает различные типы данных даты и времени. Каждый тип данных имеет свой собственный набор опций, которые могут использоваться для форматирования даты и времени в формате заказчика. Эти типы данных включают DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE и INTERVAL.
- Тип данных DATE хранит информацию только о дате.
- Тип данных TIME представляет конкретное время дня.
- TIMESTAMP хранит информацию и о дате, и о времени.
- Тип данных TIMESTAMP WITH TIME ZONE подобен TIMESTAMP, но включает также часовой пояс.
- Тип данных INTERVAL представляет интервал времени.
Теперь, когда у нас есть представление о типах данных, давайте научимся форматироваться даты в PostgreSQL.
Функции форматирования даты в PostgreSQL
В этом разделе мы познакомимся с различными функциями форматирования даты в PostgreSQL. Давайте рассмотрим наиболее часто используемые функции, которые применяются для форматирования данных типа даты и времени в PostgreSQL:
- to_char(): преобразует значение даты или штампа времени в строковое значение в заданном формате.
- to_timestamp(): преобразует строковое значение в значение штампа времени.
- to_date(): преобразует строковое значение в значение даты.
Отлично. Мы затронули некоторые из стандартных функций форматирования даты в PostgreSQL; давайте теперь рассмотрим применение этих функций.
Использование функции to_char()
Функция to_char() используется для преобразования значение даты или штампа времени в строковое значение указанного формата. Для получения значения штампа времени в формате YYYY-MM-DD используйте следующий запрос:
Этот запрос вернет штамп текущего времени в формате YYYY-MM-DD HH24 :MI :SS, где YYYY представляет год четырьмя цифрами, MM - месяц двумя цифрами, DD - день месяца двумя цифрами, а HH24, MI и SS - соответственно, час двумя цифрами в 24-часовом формате, минуты часа двумя цифрами и секунды минуты двумя цифрами.
Использование функции to_timestamp
Функция to_timestamp преобразует строковое значение в значение штампа времени. Эта функция имеет два аргумента: входная строка и строка формата. Назначение строки формата - определить порядок и формат элементов даты и времени во входной строке.
Давайте построим запрос, который преобразует строковое значение в штамп времени, а затем извлечем из него год:
SELECT EXTRACT(year FROM to_timestamp('2023-07-06 11:05:00', 'YYYY-MM-DD HH24:MI:SS'));В этом случае дается штамп времени 2023–07–06 11:05:00. Функция EXTRACT() используется для извлечения компоненты года из него, а функция to_timestamp() используется для преобразования заданного строкового значения в формат штампа времени. Таким образом, этот запрос вернет 2023 год.
Использование функции to_date()
Функция to_date() используется для преобразовании строкового значения в значение типа date и также принимает аргументы входной строки и строки формата.
Давайте построим запрос, который преобразует строковое значение в значение даты:
SELECT to_date('2024-01-06', 'YYYY-MM-DD');Этот запрос преобразует входную строку ‘2024–01–06’ в значение date PostgreSQL.
Видим, что строка формата, используемая в to_timestamp() и to_date() подобна строке формата, используемой в to_char(). Строка формата отвечает за определение порядка и формата компонент даты и времени во входной строке.
Получив базовые знания о функциях форматирования даты и времени, которые поддерживает PostgreSQL, давайте теперь рассмотрим методы эффективного оформления и упорядочивания дат.
Персонализация форматирования даты и времени в PostgreSQL
PostgreSQL также предоставляет пользователям возможность создавать собственные форматы для даты и времени. Это делается с помощью функции to_char. Функция to_char принимает два аргумента: значение даты или штампа времени, которое требуется отформатировать, и строку, которая задает требуемый формат вывода.
Давайте рассмотрим пример создания пользовательского формата даты с использованием функции to_char.
Пусть мы хотим выводить текущую дату в формате ‘11/01/23’. Вы можете построить запрос, который поможет вам сделать это с использованием спецификации пользовательского шаблона, как показано ниже:
SELECT to_char(current_date, 'DD/MM/YY'); В результате будет получено что-то типа 11/01/23.
Аналогично, если вы захотели вывести текущую дату в формате DD/MM/YYYY, то можете изменить код для получения этого результата:
SELECT to_char(current_date, 'DD/MM/YYYY'); Результат: 11/01/2023.
Форматирование часовых поясов в PostgreSQL
PostgreSQL предлагает различные встроенные функции форматирования часовых поясов, включая AT TIME ZONE, timezone() и extract(), которые позволяют выполнять преобразование между различными часовыми поясами и отображать сдвиг часового пояса в читабельном формате.
Использование функции AT TIME ZONE
Вот, например, как можно использовать функцию AT TIME ZONE для преобразования значения штампа времени к другому часовому поясу:
SELECT current_timestamp AT TIME ZONE 'UTC';Этот запрос вернет текущий штамп времени, преобразованный ко времени часового пояса UTC.
Использование функции timezone()
Функция timezone() может использоваться для преобразования значения штампа времени к другому часовому поясу. Вот пример использования этой функции для преобразования значения штампа времени к центрально европейскому времени (часовой пояс CET):
SELECT timezone('CET', current_timestamp);Этот запрос вернет текущий штамп времени, преобразованный к часовому поясу CET.
Также, если вы хотите выводить значение текущего штампа времени, скажем, в CET, в заданном формате, то можете использовать функцию to_char() в сочетании с функцией timezone() для получения желаемого формата. Вот как это делается:
SELECT to_char(timezone('CET', current_timestamp), 'YYYY-MM-DD HH24:MI:SS');Этот запрос вернет текущий штамп времени, преобразованный к часовому поясу CET и отформатированный в виде YYYY-MM-DD HH24: MI: SS.
Арифметическое и интервальное форматирование даты
PostgreSQL позволяет выполнять арифметические операции с датами и интервалами, давая возможность прибавления и вычитания интервалов из дат и форматирования интервалов, с помощью типа данных interval и функции to_char().
Вот как вы можете добавить интервал к дате, используя оператор +:
SELECT CURRENT_DATE + INTERVAL '1 month';Этот запрос прибавит месяц к текущей дате.
Для форматирования интервалов дат мы можем использовать заполнители day, hour, minute и second для отображения числа дней часов, минут и секунд, соответственно. Рассмотрим пример:
SELECT TO_CHAR(INTERVAL '3 days 2 hours', 'DD "days" HH "hours"');Этот запрос вернет 03 days 02 hours.
Случаи использования форматирования даты
Если вы новичок в области разработок баз данных, вы, возможно, задаетесь вопросом, насколько важно форматирование дат в базах данных. Форматирование дат в PostgreSQL находит практическое применение во многих реальных ситуациях. Давайте рассмотрим некоторые случаи использования и то, как это делать эффективно:
(A) Обработка данных с использованием пользовательских форматов даты
Обработка данных - важная область, где форматирование дат играет важную роль. Скажем, вы должны делать ежемесячный отчет по продажам, который включает суммарное количество продаж за каждый день месяца. Для улучшения читабельности вы можете захотеть отформатировать даты в конкретном формате типа “YYYY-MM-DD”.
Давайте рассмотрим нижеприведенный пример:
SELECT date_trunc('day', order_date) AS sales_date, SUM(amount)AS total_sales
FROM sales
GROUP BY date_trunc('day', order_date)
ORDER BY sales_date;Здесь функция date_trunc() обрезает столбец order_date до дня и группирует данные sales по дням. Результат будет включать дату продаж, отформатированную в виде YYYY-MM-DD, и соответствующее общее количество продаж за каждый день.
(B) Форматирование дат для вывода в интерфейсе пользователя
При построении интерфейса пользователя важно представлять даты в легко понимаемом формате. PostgreSQL позволяет пользователям управлять отображением дат в соответствии с их предпочтениями.
Рассмотрим, например веб-приложение, которое выводит посты блога. Приложение получает даты публикации каждой статьи из базы данных и должно отформатировать их в подходящем для вывода виде.
SELECT title, TO_CHAR(publication_date, 'Mon DD, YYYY') AS formatted_date
FROM blog_posts;В этом запросе функция TO_CHAR() используется для требуемого форматирования столбца publication_date в виде "Mon DD, YYYY". Результат будет включать заголовок статьи в блоге и отформатированную дату публикации.
Заключение
В этом руководстве мы рассмотрели различные методы форматирования данных в PostgreSQL. При помощи таких функция, как TO_CHAR() и TO_DATE(), вы можете манипулировать значениями даты/времени в соответствии с вашими потребностями. Мы узнали, как применять эти функции форматирования на реальных примерах. Эти методы позволят вам гибко обрабатывать значения даты и времени в различных форматах и ситуациях, делая ваши данные более доступными и легкими в работе.
Ссылки по теме
1. Функции даты в SQL Server, Oracle и PostgreSQL
2. Дата и время в PostgreSQL. Часть 1
3. Дата и время в PostgreSQL. Часть 2
Trackbacks
No Trackbacks
The author does not allow comments to this entry
Comments
Display comments as Linear | Threaded