изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
SET time_zone = 'Europe/Zurich'; Query OK, 0 rows affected (0.18 sec) Если снова проверить настройки часового пояса, увидим следующее: mysql> SELECT @@global.time_zone, @@session.time_zone; + + + | @@global.time_zone | @@session.time_zone | + + + | SYSTEM | Europe/Zurich | + + + 1 row in set (0.00 sec) Все даты, отображаемые в сеансе, теперь будут соответствовать Цю рихскому времени. 142 Глава 7. Создание, преобразование и работа с данными Пользователи Oracle Database могут изменить настройку часо вого пояса для сеанса следующей командой: ALTER SESSION TIMEZONE = 'Europe/Zurich' Создание временных данных Временные данные могут быть сформированы любым из следующих средств: • Копирование даты из имеющегося столбца типа date, datetime или time • Выполнение встроенной функции, возвращающей значение типа date , datetime или time • Создание строкового представления временных данных, которое потом преобразовывается сервером Для применения последнего метода необходимо понимать различные компоненты, используемые при форматировании дат. Строковые представления временных данных Напомним самые популярные компоненты дат: Таблица 7.2. Компоненты формата даты Чтобы создать строку, которая может быть интерпретирована серве ром как тип date, datetime или time, необходимо свести различные ком поненты вместе в порядке, показанном в табл. 7.3. Таблица 7.3. Обязательные компоненты дат Компонент Описание Диапазон YYYY Год, включая столетие от 1000 до 9999 MM Месяц от 01 (январь) до 12 (декабрь) DD День от 01 до 31 HH Час от 00 до 23 HHH Часы (прошедшие) от –838 до 838 MI Минута от 00 до 59 SS Секунда от 00 до 59 Тип Формат по умолчанию Date YYYY MM DD Datetime YYYY MM DD HH:MI:SS Timestamp YYYY MM DD HH:MI:SS Time HHH:MI:SS Временные данные 143 Загрузка данных часового пояса MySQL Если сервер MySQL выполняется на платформе Windows, то пре жде чем настраивать глобальные или сеансовые часовые пояса, пользователю необходимо загрузить данные часовых поясов вруч ную. Для этого надо сделать следующее: 1. Скачать данные часового пояса по адресу http://dev.mysql. com/downloads/timezones.html 2. Остановить сервер MySQL. 3. Извлечь файлы из загруженного zip файла (в моем случае этот файл назывался timezone 2004e.zip) и поместить его в подкаталог /data/mysql каталога установки MySQL (пол ный путь для моей установки был /Program Files/MySQL/ MySQL Server 4.1/data/mysql ). 4. Вновь запустить сервер MySQL. Чтобы посмотреть данные часового пояса, необходимо перейти к базе данных mysql с помощью команды use mysql и выполнить следующий запрос: mysql> SELECT name FROM time_zone_name; + + | name | + + | Africa/Abidjan | | Africa/Accra | | Africa/Addis_Ababa | | Africa/Algiers | | Africa/Asmera | | Africa/Bamako | | Africa/Bangui | | Africa/Banjul | | Africa/Bissau | | Africa/Blantyre | | Africa/Brazzaville | | Africa/Bujumbura | | US/Alaska | | US/Aleutian | | US/Arizona | | US/Central | | US/East Indiana | | US/Eastern | | US/Hawaii | | US/Indiana Starke | | US/Michigan | | US/Mountain | | US/Pacific | 144 Глава 7. Создание, преобразование и работа с данными Таким образом, чтобы заполнить столбец типа datetime показаниями времени 3:30 после полудня 27 марта 2005 года, понадобиться создать следующую строку: '2005 03 27 15:30:00' Если сервер ожидает значение datetime, как при обновлении столбца типа datetime или при вызове встроенной функции, принимающей ар гумент datetime, можно предоставить правильно форматированную строку с необходимыми компонентами даты, и сервер выполнит пре образование сам. Вот, например, выражение для корректировки даты банковской операции: UPDATE transaction SET txn_date = '2005 03 27 15:30:00' WHERE txn_id = 99999; Сервер определяет, что предоставленная строка блока set должна быть значением типа datetime, поскольку строка используется для заполне ния столбца типа datetime. Следовательно, сервер попытается преобра зовать эту строку, разбирая ее на шесть компонентов (год, месяц, день, час, минута, секунда), включенные в формат datetime по умолчанию. Преобразования строки в дату Если сервер не ожидает значение типа datetime, необходимо указать ему преобразовать строку в тип datetime. Вот, например, простой за прос, возвращающий значение типа datetime с помощью функции cast() (привести): mysql> SELECT CAST('2005 03 27 15:30:00' AS DATETIME); + + | CAST('2005 03 27 15:30:00' AS DATETIME) | + + | 2005 03 27 15:30:00 | + + 1 row in set (0.00 sec) | US/Samoa | | UTC | | W SU | | WET | | Zulu | + + 545 rows in set (0.01 sec) Чтобы изменить настройки своего часового пояса, надо выбрать одно из имен из предыдущего запроса, наиболее соответствую щее вашему местоположению. Временные данные 145 Функция cast() будет рассмотрена в конце данной главы. Хотя этот пример демонстрирует построение значений типа datetime, аналогич ная логика применятся и к типам date и time. Следующий запрос ис пользует функцию cast() для формирования значения типа date и зна чения типа time: mysql> SELECT CAST('2005 03 27' AS DATE) date_field, > CAST('108:17:57' AS TIME) time_field; + + + | date_field | time_field | + + + | 2005 03 27 | 108:17:57 | + + + 1 row in set (0.00 sec) Конечно, можно явно преобразовывать строки, даже когда сервер ожидает значение date, datetime или time, а не полагаться на неявное преобразование, выполняемое сервером. При явном или неявном преобразовании строк во временные значения все компоненты даты должны быть предоставлены в требуемом поряд ке. Некоторые серверы очень строги относительно формата даты, но сервер MySQL довольно мягок в отношении разделителя компонентов. Например, MySQL примет все нижеприведенные строки как допусти мые представления времени 3:30 дня 27 марта 2005 года: '2005 03 27 15:30:00' '2005/03/27 15:30:00' '2005,03,27,15,30,00' '20050327153000' Хотя это и обеспечивает немногим большую гибкость для вас, возмож на ситуация, в которой требуется сформировать временное значение без стандартных компонентов даты. В следующем разделе будут представ лены встроенные функции, гораздо более гибкие, чем функция cast(). Функции для создания дат Если требуется сгенерировать временные данные из строки, и форма строки не позволяет использовать функцию cast(), можно обратиться к встроенной функции, позволяющей предоставить вместе со строкой даты строку форматирования. MySQL включает для этой цели функ цию str_to_date(). Например, для обновления столбца date из файла извлекается строка 'March 27, 2005'. Строка не соответствует требуемо му формату YYYY MM DD, но вместо того чтобы переформатировать ее, делая пригодной для применения функции cast(), можно восполь зоваться функцией str_to_date(): UPDATE individual SET birth_date = STR_TO_DATE('March 27, 2005', '%M %d, %Y') WHERE cust_id = 9999; 146 Глава 7. Создание, преобразование и работа с данными Второй аргумент в вызове str_to_date() определяет формат строки да ты. В данном случае это название месяца (%M), число (%d) и четырех значное число, обозначающее год (%Y). Есть более 30 общепринятых компонентов форматирования. В табл. 7.4 приведено около десятка наиболее широко используемых компонентов. Таблица 7.4. Компоненты форматирования даты Функция str_to_date() возвращает значение типа datetime, date или time в зависимости от содержимого форматирующей строки. Напри мер, если форматирующая строка включает только %H, %i и %s, будет возвращено значение типа time. В распоряжении пользователей Oracle Database имеется функ ция to_date(), с которой можно работать так же, как с функцией MySQL str_to_date(). При формировании текущей даты/времени создавать строку не требу ется – следующие встроенные функции организуют доступ к систем ным часам и возвратят текущую дату и/или время в виде строки: mysql> SELECT CURRENT_DATE( ), CURRENT_TIME( ), CURRENT_TIMESTAMP( ); + + + + | CURRENT_DATE( ) | CURRENT_TIME( ) | CURRENT_TIMESTAMP( ) | + + + + | 2005 03 20 | 22:15:56 | 2005 03 20 22:15:56 | + + + + 1 row in set (0.00 sec) Возвращаемые этими функциями значения имеют формат по умолча нию для возвращаемого временного типа. В Oracle Database есть функ Компонент форматирования Описание %M Название месяца (от January до December) %m Номер месяца (от 01 до 12) %d Число (от 01 до 31) %j День года (от 001 до 366) %W Дни недели (от Sunday до Saturday) %Y Год, четырехзначное число %y Год, двузначное число %H Час (от 00 до 23) %h Час (от 01 до 12) %i Минуты (от 00 до 59) %s Секунды (от 00 до 59) %f Микросекунды (от 000000 до 999999) %p A.M. или P.M. Временные данные 147 ции current_date() и current_timestamp(), но нет функции current_time(). SQL Server включает только функцию current_timestamp(). Работа с временными данными В данном разделе рассматриваются встроенные функции, принимаю щие аргументы даты и возвращающие даты, строки или числа. Временные функции, возвращающие даты Многие встроенные временные функции принимают в качестве аргу мента одну дату и возвращают другую. Например, функция MySQL date_add() позволяет добавить любой интервал (т. е. дни, месяцы, года) к заданной дате, чтобы получить другую дату. Вот пример, демонстри рующий, как добавить к текущей дате пять дней: mysql> SELECT DATE_ADD(CURRENT_DATE( ), INTERVAL 5 DAY); + + | DATE_ADD(CURRENT_DATE( ), INTERVAL 5 DAY) | + + | 2005 03 26 | + + 1 row in set (0.00 sec) Второй аргумент заключает в себе три элемента: ключевое слово interval (интервал), требуемое количество и тип интервала. В табл. 7.5 приве дены некоторые широко используемые типы интервалов. Таблица 7.5. Общепринятые типы интервалов Первые шесть типов, перечисленные в табл. 7.5, довольно просты, а по следние три требуют немного более подробного объяснения, поскольку содержат по несколько элементов. Например, если оказалось, что опе рация с ID 9999 на самом деле имела место на 3 часа 27 минут и 11 се кунд позже того значения, которое было отправлено в таблицу Transac tion , исправить это можно следующим образом: Интервал Описание Second Количество секунд Minute Количество минут Hour Количество часов Day Количество дней Month Количество месяцев Year Количество лет Minute_second Количества минут и секунд, разделенные двоеточием Hour_second Количества часов, минут и секунд, разделенные двоеточием Year_month Количества лет и месяцев, разделенные дефмсом 148 Глава 7. Создание, преобразование и работа с данными UPDATE transaction SET txn_date = DATE_ADD(txn_date, INTERVAL '3:27:11' HOUR_SECOND) WHERE txn_id = 9999; В этом примере функция берет значение столбца txn_date, добавляет к нему 3 часа 27 минут и 11 секунд и изменяет столбец txn_date, встав ляя в него результирующее значение. Или если в отделе кадров обнаруживают, что сотрудник с ID 4789 по записанным данным моложе, чем на самом деле, можно добавить к да те его рождения, скажем, 9 лет и 11 месяцев: UPDATE employee SET birth_date = DATE_ADD(birth_date, INTERVAL '9 11' YEAR_MONTH) WHERE emp_id = 4789; Для пользователей SQL Server предыдущий пример можно бы ло бы реализовать с помощью функции dateadd(): UPDATE employee SET birth_date = DATEADD(MONTH, 119, birth_date) WHERE emp_id = 4789 В SQL Server нет комбинированных интервалов (т. е. year_month), поэтому 9 лет 11 месяцев были преобразованы в 119 месяцев. Пользователи Oracle Database могут для данного примера при менить функцию add_months(): UPDATE employee SET birth_date = ADD_MONTHS(birth_date, 119) WHERE emp_id = 4789; Иногда требуется добавить интервал времени к определенной дате, при этом известна конечная дата выполнения, но неизвестно, сколько дней осталось до этой даты. Например, клиент банка регистрируется в сете вой банковской системе и планирует перевод на конец месяца. Вместо того чтобы писать какой то код, определяющий текущий месяц и вы числяющий количество дней в этом месяце, можно вызвать функцию last_day() (последний день), которая сделает всю работу (и MySQL, и Oracle Database включают функцию last_day(); в SQL Server сопос тавимой функции нет). Если клиент запрашивает перевод 25 марта 2005 года, последний день марта можно найти следующим образом: mysql> SELECT LAST_DAY('2005 03 25'); + + | LAST_DAY('2005 03 25') | + + | 2005 03 31 | + + 1 row in set (0.04 sec) Временные данные 149 Независимо от того, предоставляется ли значение типа date или date time , функция last_day() всегда возвращает значение типа date. Хотя, может быть, и не заметно, что эта функция существенно экономит вре мя, но если требуется найти последний день февраля и для этого выяс нить, високосный этот год или нет, логика вычисления может быть до вольно сложной. Еще одна временная функция, возвращающая дату, преобразует зна чение типа datetime из одного временного пояса в другой. Для этого MySQL включает функцию convert_tz(), а Oracle Database – функцию new_time() (новое время). Например, если требуется преобразовать те кущее местное время в UTC, можно сделать следующее: mysql> SELECT CURRENT_TIMESTAMP( ) current_est, > CONVERT_TZ(CURRENT_TIMESTAMP( ), 'US/Eastern', 'UTC') current_utc; + + + | current_est | current_utc | + + + | 2005 04 18 21:23:25 | 2005 04 19 01:23:25 | + + + 1 row in set (0.50 sec) Эта функция очень полезна при получении дат из других часовых поя сов, отличных от того, в котором хранится база данных. Временные функции, возвращающие строки Большинство временных функций, возвращающих строковые значе ния, используются для получения отдельной части даты или времени. Например, MySQL включает функцию dayname() (название дня), опре деляющую, на какой день недели приходится определенная дата: mysql> SELECT DAYNAME('2005 03 22'); + + | DAYNAME('2005 03 22') | + + | Tuesday | + + 1 row in set (0.12 sec) В MySQL много таких функций, предназначенных для извлечения ин формации из значений дат, но я рекомендую пользоваться функцией extract() (извлечь), поскольку проще запомнить несколько разновид ностей одной функции, чем десяток разных функций. Кроме того, ext ract() является частью стандарта SQL:2003 и была реализована не только в MySQL, но и в Oracle Database. Для определения интересующего элемента даты функция extract() ис пользует те же типы интервалов, что и функция date_add() (см. табл. 7.5). Например, если из значения типа datetime требуется из влечь только год, можно поступить так: 150 Глава 7. Создание, преобразование и работа с данными mysql> SELECT EXTRACT(YEAR FROM '2005 03 22 22:19:05'); + + | EXTRACT(YEAR FROM '2005 03 22 22:19:05') | + + | 2005 | + + 1 row in set (0.02 sec) В SQL Server нет реализации extract(), но есть функция date part() (часть даты). Вот как можно извлечь год из значения da tetime с помощью datepart(): SELECT DATEPART(YEAR, GETDATE( )) Временные функции, возвращающие числа Ранее в этой главе была представлена функция, используемая для до бавления заданного интервала к значению даты и формирующая, та ким образом, другую дату. Другая распространенная операция при ра боте с датами – определение количества интервалов (дней, недель, лет) между двумя датами. MySQL включает предназначенную для этого функцию datediff(), которая возвращает количество полных дней ме жду двумя датами. Например, чтобы узнать, сколько дней будут про должаться школьные каникулы этим летом, можно сделать так: mysql> SELECT DATEDIFF('2005 09 05', '2005 06 22'); + + | DATEDIFF('2005 09 05', '2005 06 22') | + + | 75 | + + 1 row in set (0.00 sec) Итак, до благополучного возвращения детей в школу мне предстоит 75 дневная пытка ядовитым плющом, комариными укусами и разби тыми коленками. Функция datediff() в своих аргументах не учитыва ет время дня. Даже если включить время, задавая для первой даты од ну секунду до полуночи и для второй даты одну секунду после полуно чи, эти данные никак не отразятся на вычислениях: mysql> SELECT DATEDIFF('2005 09 05 23:59:59', '2005 06 22 00:00:01'); + + | DATEDIFF('2005 09 05 23:59:59', '2005 06 22 00:00:01') | + + | 75 | + + 1 row in set (0.00 sec) Если переставить аргументы, поместив первой более раннюю дату, datediff() вернет отрицательное число: mysql> SELECT DATEDIFF('2005 06 22', '2005 09 05'); Функции преобразования 151 + + | DATEDIFF('2005 06 22', '2005 09 05') | + + | 75 | + + В SQL Server тоже есть функция datediff(), но более гибкая, чем в MySQL. В ней можно задавать тип интервала (т. е. год, месяц, день, час), а не только вычислять количество дней между двумя датами. Вот как был бы выполнен предыдущий пример для SQL Server: SELECT DATEDIFF(DAY, '2005 06 22', '2005 09 05') Oracle Database позволяет определять число дней между двумя датами простым вычитанием одной даты из другой. Функции преобразования Ранее в этой главе было показано, как использовать функцию cast() для преобразования строки в значение типа datetime. Хотя у всех сер веров БД есть собственные функции преобразования данных из одного типа в другой, я рекомендую использовать функцию cast(), включен ную в стандарт SQL:2003 и реализованную в MySQL, Oracle Database и Microsoft SQL Server. Применяя функцию cast(), необходимо задать значение или выраже ние, ключевое слово as и тип, в который должно быть преобразовано значение. Вот пример преобразования строки в целое: mysql> |