Извлечение дня недели из даты в PostgreSQL, понедельник = 0

Быстрый ответ

Для определения дня недели в PostgreSQL, где понедельник считается первым (т.е. соответствует числу 1), а воскресенье последним (т.е. соответствует числу 7), следует использовать функцию EXTRACT(DOW) в сочетании с функцией mod:

SELECT MOD(EXTRACT(DOW FROM date_column)::int + 6, 7) + 1 AS day_of_week 
FROM your_table;

Такой подход позволяет сместить нумерацию дней, заменяя стандартное представление воскресенья числом 0.

Адаптация дней недели под конкретные нужды

Для корректировки дней недели под систему, где понедельник обозначается числом 0, можно использовать следующий запрос:

SELECT EXTRACT(ISODOW FROM date_column) – 1 AS day_of_week 
FROM your_table;

Здесь используется параметр ISODOW, благодаря которому интервал от 1 (понедельник) до 7 (воскресенье) преобразуется в требуемый формат 0-6.

Текстовое представление дней недели

Для текстового представления дней недели используйте функцию TO_CHAR():

SELECT TO_CHAR(date_column, 'Day') AS day_text 
FROM your_table;

Форматирование текстового представления даты можно применять с помощью различных токенов, например, 'Day' для полного наименования дня недели, 'Dy' для его сокращения или 'D' для однобуквенного обозначения.

Индивидуальная нумерация дней недели

Выражения CASE могут пригодиться для реализации особой нумерации дней:

SELECT CASE 
  WHEN EXTRACT(DOW FROM date_column)::int = 0 THEN 6
  ELSE EXTRACT(DOW FROM date_column)::int – 1 
END AS custom_day_of_week
FROM your_table;

Этот код позволит определить воскресенье как 6-й день, в то время как понедельник займет место первого рабочего дня недели под индексом 0.

Визуализация

Таблица ниже демонстрирует размещение дней недели в зависимости от выбранных определений:

Номер дня недели в PostgreSQL можно определить следующим запросом:

SELECT EXTRACT(ISODOW FROM your_date) AS day_of_week 
FROM your_table;

Маленькие хитрости: находим нестандартные решения

Таблица дней недели для диапазона дат

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

-- Возможно, вам будет интересно узнать, каким днем недели будет ваш день рождения в 2023 году

WITH date_series AS (
  SELECT generate_series(
    '2023-01-01'::date, 
    '2023-01-07', 
    '1 day'::interval
  )::date as series_date
)
SELECT series_date, EXTRACT(ISODOW FROM series_date) – 1 AS weekday_number
FROM date_series;

Подробнее об этом расскажет наш спикер на видео

Фильтрация дней недели для бизнес-анализа

При составлении рабочих отчетов часто требуется исключить выходные дни. Это можно выполнить следующим образом:

-- Извините, выходные! Но вы не приглашены на эту вечеринку.

SELECT *
FROM your_table
WHERE EXTRACT(ISODOW FROM date_column) BETWEEN 1 AND 5;

Часовые пояса и их влияние

При извлечении данных важно учитывать часовой пояс:

-- UTC? Или не UTC? Вот в чем вопрос.

SELECT EXTRACT(ISODOW FROM date_column AT TIME ZONE 'UTC') – 1 AS day_of_week 
FROM your_table;

Не забывайте, что часовой пояс может существенно исказить локальное время.

Полезные материалы

1. Документация PostgreSQL: 9.9. Функции даты и времени и операторы — основная ссылка по функциям работы с временем и датой.
2. Работа с датами и временем в PostgreSQL – PostgreSQL wiki — подробное руководство по управлению датами и временем в PostgreSQL.
3. Пять способов пагинации в Postgres — улучшение навыков работы с SQL с помощью эффективных техник.
4. Занудные штучки — примеры использования функций даты в PostgreSQL.
5. SQL Server – Влияют ли форматы дат на производительность запросов? – DBA Stack Exchange — вопрос влияния форматов даты на эффективность SQL-запросов.