Лонгрид-Работа с датами. ИмяРазмер
 Скачать 0.79 Mb.
|
|
3. Работа с датами Давайте попробуем теорию про основные типы данных перевести в практическую плоскость - посмотрим, какие есть варианты дат и когда какие применяются поддерживает полный набор типов даты и времени SQL. Все даты считаются по Григорианскому календарю, даже для времени до его введения Типы даты/времени Имя Размер Описание Наименьше е значение Наибольшее значение Точность timestamp [ ( p) ] [ without time zone ] 8 байт дата и время (без часового пояса дон. э н. э. 1микросекунда / 14 цифр timestamp [ ( p) ] with time zone 8 байт дата и время (с часовым поясом дон. э н. э. 1микросекунда / 14 цифр date 4 байта дата (без времени суток дон. э н. э день time [ (p) ] [ without time zone ] 8 байт время суток без даты 24:00:00 микросекунда 14 цифр time [ (p) ] with time zone 12 байт только время суток (с часовым поясом 9 24:00:00-1559 микросекунда 14 цифр interval [ #### ] [ (p) ] 16 байт временной интервал -178000000 лет 178000000 лет 1микросекунда / 14 цифр Примечание Стандарт SQL требует, чтобы тип timestamp подразумевал timestamp without time время без часового пояса, и Postgres Pro следует этому. Для краткости timestamp with time zone можно записать как timestamptz; это расширение Postgres Важно помнить, что при вводе значений их нужно заключать в одинарные кавычки, как и текстовые строки. Типы time, timestamp и interval принимают необязательное значение точности определяющее, сколько знаков после запятой должно сохраняться в секундах. По умолчанию точность не ограничивается. Для типов timestamp и interval p может принимать значения от 0 до Для типа time p может принимать значения от 0 допри хранении типа в восьмибайтном целом и от 0 допри хранении в числе с плавающей точкой. Тип interval дополнительно позволяет ограничить набор сохраняемых полей следующими фразами MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO Заметьте, что если указаны и ####, и точность p, указание #### должно включать, так как точность применима только к секундам. Локаль и восприятие формата Значения даты и времени принимаются практически в любом разумном формате, включая ISO 8601, совместимый, традиционный формат POSTGRES и другие. В некоторых форматах порядок даты, месяца и года во вводимой дате неоднозначен и поэтому поддерживается явное определение формата. Для этого предназначен параметр DateStyle. Когда он имеет значение MDY, выбирается интерпретация месяц-день-год, значению DMY соответствует день-месяц-год, а YMD — год-месяц-день. Локаль - набор параметров, идентифицирующих язык пользователя, страну и любые другие специальные параметры, относящиеся к языку (например национальная валюта, клавиатурная раскладка, формат даты и времени и пр. Обычно локаль состоит из идентификатора языка и идентификатора региона. Нужна локаль для различных программ, чтобы они смогли правильно выбрать кодировку или показать пользовательский интерфейс на том языке, который указан в локали, при условии, что имеется перевод этого интерфейса на искомый язык. Если вы находитесь в странах Атлантического союза либо ваша операционная система настроена на локаль, отличную от русский, то формат день, месяц, год” восприниматься не будет. В этом регионе формат должен быть год, месяц, число В российской локали можно дату вводить в любом формате - все будет воспринято: В таблице приведены некоторые допустимые значения типа date. Пример Описание 1999-01-08 ISO 8601; 8 января в любом режиме ( рекомендуемый формат воспринимается однозначно в любом режиме datestyle 1/8/1999 8 января в режиме MDY и 1 августа в режиме DMY 1/18/1999 18 января в режиме MDY; недопустимая дата в других режимах 2 января 2003 г. в режиме MDY; 1 февраля 2003 г. в режиме DMY и 3 февраля г. в режиме YMD 1999-Jan-08 8 января в любом режиме 8 января в любом режиме 8 января в любом режиме 99-Jan-08 8 января в режиме YMD; ошибка в других режимах 8 января ошибка в режиме YMD Jan-08-99 8 января ошибка в режиме YMD 19990108 ISO 8601; 8 января 1999 в любом режиме 8601; 8 января 1999 в любом режиме 1999.008 год и день года J2451187 дата по юлианскому календарю 8, 99 BC 99 дон. э. Для хранения времени суток без даты предназначены типы time [ (p) ] without time zone и time [ (p) ] with time zone. Тип time без уточнения эквивалентен типу time without time Допустимые вводимые значения этих типов состоят из записи времени суток и необязательного указания часового пояса. Если в значении для типа time without time zone указывается часовой пояс, он просто игнорируется. Также будет игнорироваться дата, если её указать, за исключением случаев, когда в указанном часовом поясе принят переход на летнее время, например America/New_York. В данном случае указать дату необходимо, чтобы система могла определить, применяется ли обычное или летнее время. Соответствующее смещение часового пояса записывается в значении time with time Вводимое время Пример Описание 04:05:06.789 ISO 8601 04:05:06 ISO 8601 040506 ISO 8601 04:05 тоже, что и 04:05; AM не меняет значение времени тоже, что и 16:05; часы должны быть <= 12 04:05:06.789-8 ISO 8601 04:05:06-08:00 ISO 8601 04:05-08:00 ISO 8601 040506-08 ISO 8601 04:05:06 часовой пояс задаётся аббревиатурой 04:05:06 America/New_York часовой пояс задаётся полным названием Вводимый часовой пояс Пример Описание PST аббревиатура (Pacific Standard Time, Стандартное тихоокеанское время America/New_York полное название часового пояса PST8PDT указание часового пояса в стиле смещение часового пояса PST по смещение часового пояса PST по смещение часового пояса PST по ISO-8601 zulu принятое у военных сокращение UTC z краткая форма Стандарт SQL различает константы типов timestamp without time zone и timestamp with time zone по знаку «+» или «-» и смещению часового пояса, добавленному после времени. Следовательно, согласно стандарту, записи '2004-10-19 должен соответствовать типа' тип timestamp with time zone. PostgreSQL никогда не анализирует содержимое текстовой строки, чтобы определить тип значения, и поэтому обе записи будут обработаны как значения типа timestamp without time zone. Чтобы текстовая константа обрабатывалась как timestamp with time zone, укажите этот тип явно WITH TIME ZONE '2004-10-19 В константе типа timestamp without time zone PostgreSQL просто игнорирует часовой пояс. То есть результирующее значение вычисляется только из полей даты/времени и не подстраивается под указанный часовой пояс. Значения timestamp with time zone внутри всегда хранятся в UTC (Universal Coordinated Time, Всемирное скоординированное время или время по Гринвичу, GMT). Вводимое значение, в котором явно указан часовой пояс, переводится в UTC с учётом смещения данного часового пояса. Если во входной строке не указан часовой пояс подразумевается часовой пояс, заданный системным параметром TimeZone и время также пересчитывается в UTC со смещением Когда значение timestamp with time zone выводится, оно всегда преобразуется изв текущий часовой пояс timezone и отображается как локальное время. Чтобы получить время для другого часового пояса, нужно либо изменить timezone, либо воспользоваться конструкцией AT TIME В преобразованиях между timestamp without time zone и timestamp with time zone обычно предполагается, что значение timestamp without time zone содержит местное время (для часового пояса timezone). Другой часовой пояс для преобразования можно задать с помощью AT TIME Правило записи Чтобы сказать СУБД, что введенное значение является датой, а непростой символьной строкой, мы использовали операцию приведения типа. Вона оформляется с использованием двойного символа двоеточие и именитого типа, к которому мы приводим данное значение. Важно учесть, что при выполнении приведения типа производится проверка значения на соответствие формату целевого типа и множеству его допустимых значений. Как SQL воспринимает дату? Пример: брали ли пользователи в аренду фильмы между двумя датами - использование Обратите внимание, “between” означает, что выборка включает начальную и конечную даты (те. учитываются и 27, и 28 мая). Для наглядности отсортировали и увидели, что 28 мая никто фильмы в аренду не брал. Так ли это Но по умолчанию SQL дату воспринимает как ‘28-05-2005 00:00:00’, поэтому в примере фактически 28 мая оказалось не учтено - учтены полные сутки 27 мая ив часов 28 мая счетчик остановился. Как с этим бороться- использование Возможно несколько способов, зависит оттого, что мы хотим получить- указать ‘28-05-2005 23:59:59’ - добавить интервал один день (interval ‘1 day’) - можно напрямую указать ‘29-05-2005’, но представьте, что это не май 2005 года, а февраль 2020 - какое число идет за 28 февраля Если делать вручную, можно ошибиться. Те. так делать нежелательно. Еще один пример нам надо вывести платежи, поступившие после какой-то даты (т.е. не включая ее 30.04 попадает в такую выборку. Чтобы справиться с этой задачей, мы можем пойти несколькими путями- добавить интервал один день (interval ‘1 day’); - payment_date приведем к дате и укажем ‘30-04-2007’ в скобках: А можем из rental_date сделать дату, убрав часы, и работать именно с датой: Но для этого обязательно тот столбец, с которым работаем, должны привести к дате. Оператор для перевода Вывод даты/времени В качестве выходного формата типов даты/времени можно использовать один из четырёх стилей ISO 8601, SQL (Ingres), традиционный формат POSTGRES (формат date вили. По умолчанию выбран формат ISO. (Стандарт SQL требует, чтобы использовался именно ISO 8601. Другой формат называется исключительно по историческим причинам) Примеры всех стилей вывода перечислены таблице. Вообще со значениями типов date и time выводилась бы только часть даты или времени из показанных примеров, но со стилем POSTGRES значение даты без времени выводится в формате Стили вывода даты/время Стиль Описание Пример ISO ISO 8601, стандарт традиционный стиль 07:37:16.00 PST Postgres изначальный стиль Dec 17 07:37:16 1997 PST German региональный стиль 07:37:16.00 Примечание 8601 указывает, что дата должна отделяться от времени буквой T в верхнем регистре. Postgres Pro принимает этот формат при вводе, но при выводе вставляет вместо T пробел, как показано выше. Это сделано для улучшения читаемости и для совместимости си другими СУБД. В стилях SQL и POSTGRES день выводится перед месяцем, если установлен порядок, а в противном случае месяц выводится перед днём. Соглашения о порядке компонентов даты Стиль даты/времени пользователь может выбрать с помощью команды SET параметра DateStyle в файле конфигурации postgresql.conf или переменной окружения на сервере или клиенте. Параметр Порядок при вводе Пример вывода, DMY ####/#####/### 17/12/1997 15:37:16.00 CET SQL, MDY #####/####/### 12/17/1997 07:37:16.00 PST Postgres, DMY ####/#####/### Wed 17 Dec 07:37:16 1997 Для большей гибкости при форматировании выводимой даты/времени можно использовать функцию Основные функции, которые нужны при работе с датами. Функция extract вытаскивает то, что мы передаем (в примере - год, месяц, неделю, день): Используя конкатенацию, можно красиво вывести сразу месяц и год: Как получить платежи, поступавшие после Здесь также можем использовать Как получить платежи по нечетным датам? Используем остаток отделения на 2 - если 0 - четная дата, если неравен нулю - нечетная дата Extract - это часть функции date_part, у которой всего навсего немного другой синтаксис указание, что надо получить и откуда: Когда выработаете с date_part, выработаете с конкретным значением, с конкретным числом, которое выдергиваете. И здесь часто возникает ошибка. Например, вам нужно получить данные за апрель месяц за несколько лет (апрель 2020, апрель апрель 2018 года. И когда выбудете работать с date_part, вы получаете просто апрель. Вам нужно выдергивать 2 столбца - именно год и месяца это неудобно Для того, чтобы работать с конкретным значением в рамках нужного года, можно использовать функцию date-trunc. Она имеет такой же синтаксис и если мы напишем ‘февраль’, то получим 1 февраля 2020 года. Функция date-trunc будет выводить при запросе месяца - на 1 число месяца, при запросе года - на первое января, те. конкретное число. Применяется при сравнении на конкретную дату. Если вы хотите получить текущую дату и время, используйте функцию now. Как создать колонку которая по умолчанию будет содержать текущее время? Используйте CURRENT_TIMESTAMP: CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP Входные значения now, today, tomorrow и yesterday вполне корректно работают в интерактивных командах, но когда команды сохраняются для последующего выполнения, например в подготовленных операторах, представлениях или определениях функций, их поведение может быть неожиданным. Такая строка может преобразоваться в конкретное значение времени, которое затем будет использоваться гораздо позже момента, когда оно было получено. В таких случаях следует использовать одну из функций. Например, CURRENT_DATE + 1 будет работать надёжнее, чем 'Как найти, сколько дней прошло между датами (например, сегодняшней датой и февраля 2020 года)? А вот чтобы вывести не в днях, а в годах или месяцах, придется писать сложные запросы (“костылить”), просто перевести в нужный формат, к сожалению, не получится. Можете переводить данные из просто даты в дату со временем, далее - дату со временем и с часовым поясом, потом - обратно. Для этого используется функция pg_typeof. Как сделать классическую разбивку по дням, месяцам, годам (часто бывает нужно для кадровиков): Всегда, когда видите какие-то непонятные типы данных, проверяйте, что это за данные. Например: чем является год выпуска (Есть вообще в этих данных год Что такое year? На самом деле, тип данных здесь - integer - пользовательский тип. Это легко проверяется в Типе данных |