Из этого урока вы узнаете о том, как в SQL обрабатываются значения дат и времени. Мы подробно поговорим не только об особенностях представления данных типа DATETIME, но и о том, как значения дат и времени используются в разных реализациях языка. Вы узнаете о некоторых общих правилах использования данных этого типа и о том, как извлекать из базы данных значения дат и времени в нужном формате.
Основными на этом уроке будут следующие темы.
• Использование дат и времени
• В каком виде хранятся значения дат и времени
• Типичные форматы дат и времени
• Использование функций дат и времени
• Преобразования дат и времени
Как вы уже знаете, существует множество различных реализаций SQL. В этой книге
рассмотрен стандарт ANSI и общие для многих реализаций языка функции, команды и операторы Для примеров выбрана реализация Oracle Но даже в рамках Oracle даты могут храниться в различных форматах. Уточнить особенности хранения дат можно по документации. Но, независимо от формы хранения дат, каждая конкретная реализация языка должна предлагать вам функции, позволяющие преобразовать даты из одного формата в другие.
В каждой реализации языка есть формат хранения дат, принятый по умолчанию. Такие форматы для различных реализаций SQL часто оказываются разными, но то же самое можно сказать и о форматах хранения других типов данных. В следующих разделах рассматривается стандартный формат и его составляющие для данных типа DATETIME- После этого мы с вами ознакомимся с особенностями представления дат и времени в основных реализациях SQL, таких как Oracle, Sybase и Microsoft SQL Server.
Стандартные типы данных для дат и времени
Имеется три стандартных типа данных SQL, предназначенных для хранения дат и времени (DATETIME).
Тип данных____ Использование
DATE Для хранения значений дат
Т1МЕ Для хранения значений времени
TIMESTAMP Для хранения значений дат и времени
Формат и диапазон допустимых значений для этих типов данных следующий.
DATE_______________
Формат: гпт-ММ-дд
Диапазон допустимых значений: от 0001-01-01 до 9999-12-31
TIME___________
Формат: чч: мм: сс.пп
Диапазон допустимых значений: от 00:00:00... до 23:59:61.999...
TIMESTAMP_________
Формат: гггг-ММ-ДД чч: мм: сс.пп
Диапазон допустимых значений: от 0001-01-01 ОО'ОО'ОО '
до 9999-12-31 23:59:61.999...
Компонентами типа данных DATETIME являются элементы, из которых складываются значения дат и времени. Вот список отдельных компонентов типа DATETIME вместе с диапазонами допустимых значений.
YEAR (ГОД) ОТ 0001 ДО 9999
MONTH (месяц) от 01 до 12
DAY (день) от 01 до 31
HOUR (час) от оо до 23
MINUTE (минута) от оо До 59
SECOND (секунда) от о.000... до 61.999... Секунды можно представить в виде десятичных чисел с десятыми, сотыми частями секунды, миллисекундами и т. д. С указанными здесь компонентами, кроме последнего, вопросов возникать не должно, поскольку они представляют компоненты дат и времени, используемые в повседневной жизни. Удивление может вызвать лишь допущение, что в минуте может содержаться более 60 секунд. В соответствии со стандартом ANSI число 61.999 Для секунд возникает из-за возможного добавления или пропуска секунд при корректировке точного времени, что само по себе является достаточно редким явлением. Чтобы уточнить диапазон допустимых значений, обратитесь к документации по используемой вами реализации языка, поскольку по вопросам хранения дат и времени у производителей единого мнения нет.
Вариации представления конкретных типов данных
Как и для других типов данных, разные реализации SQL предлагают свои представления и синтаксис. В этом разделе мы поговорим о том, как с датами и временем обращаются Oracle, Sybase и SQLBase.
Продукт___Тип данных_____Использование_______________________
Oracle DATE Хранит дату и время вместе
Sybase DATETIME Хранит дату и время вместе
SMALLDATETIME Хранит дату и время вместе, но допускает более
узкий диапазон для дат, чем DATETIME
SQLBase DATETIME Хранит дату и время вместе
TIMESTAMP Хранит дату и время вместе
DATE Хранит дату
TIME Хранит время
Каждая реализация языка предлагает свои типы данных для дат и времени, но чаще всего реализации поддерживают стандарт ANSI в том смысле, что все компоненты даты и времени включены в соответствующие типы данных Но то, в каком внутреннем формате хранятся значения дат и времени, зависит от конкретной реализации языка
Функции для работы с датами в SQL зависят от конкретной реализации языка. Функции для работы с датами используются для управления представлением значений дат и времени, подобно тому, как с символьными строками используются строковые функции. Функции для работы с датами и временем часто используются для того, чтобы представить соответствующие данные в формате, удобном для чтения, сопоставления, выполнения с ними операций и т. п.
Наверное, вы уже подумали: "Как получить из базы данных информацию о текущем времени?" Такая необходимость может возникнуть в самых разных ситуациях, но обычно текущая дата бывает необходима для сравнения со значениями дат, хранящимися в базе данных, или для использования текущей даты в качестве временной метки.
Текущая дата отслеживается главным (управляющим) компьютером базы
данных и называется системной датой. При работе с базой данных посредством использования определенной операционной системы имеется возможность извлечь системную дату для внутренних потребностей самой базы данных или для использования этой даты в различных операциях типа запросов.
Рассмотрим несколько методов извлечения системной даты с использованием команд разных реализаций языка.
В Sybase для получения системной даты используется функция GETDATE() • В запросах эта функция используется следующим образом (в данном случае текущей датой оказался новогодний вечер конца 1999 года).
SELECT GETDATEO
Dec 31, 1999
Обсуждаемые в этой книге возможности реализаций языка от Sybase и Microsoft применимы в обеих этих реализациях, поскольку обе они в качестве сервера своих баз данных используют SQL Server. Обе эти реализации языка используют одно и то же расширение стандартного SQL, известное как
Transact-SQL.
В Огас1едля извлечения системной даты используется то, что в нем называется псевдостолбцом SySDATE- Обращаться с SySDATE можно как с любым другим столбцом в таблице и выбрать его можно из любой таблицы базы данных, хотя он и не является частью определения таблицы.
Для извлечения системной даты в Oracle используется следующий оператор.
SELECT SYSDATE FROM ИМЯ_ТАБЛИЦЫ;
31-DEC-99
При работе с датами и временем может возникнуть необходимость учитывать различия во времени для разных часовых поясов. Например, 6 часов вечера в США не соответствует 6 часам вечера в Австралии, хотя с точки зрения мирового времени рассматривается один момент. Там, где используется летнее и зимнее время, приходится корректировать показания часов дважды в год. Если это необходимо, имеется возможность учесть различия во времени или осуществить необходимую корректировку времени с помощью подходящих операторов SQL, если таковые соответствующей реализацией языка предусмотрены.
Вот список некоторых из часовых поясов с принятыми для них аббревиатурами.
Сокращение____ Расшифровка_________
AST, ADT Атлантическое (нью-йоркское) поясное время, летнее время
BST, ВDT Берингово поясное время, летнее время
СSт, CDT Центральное поясное время, летнее время
EST, EDT Восточное пояснее время, летнее время
GMT Всемирное (гринвичское среднее) время или время по Гринвичу
HST, НОТ Гавайское поясное время, летнее время
NST Ньюфаундлендское поясное время
PST, PDT Тихоокеанское время, летнее время
YST, YDT Юкона поясное время, летнее время
В некоторых реализациях SQL имеются средства для учета поясного времени. Уточните по документации имеются ли в используемой вами реализации языка возможности для учета поясного времени, а также вообще оцените необходимость учета поясного времени в вашей конкретной базе данных.
Добавление интервалов времени к датам
К датам можно добавлять дни, месяцы и другие компоненты времени для сравнения дат или, например, для того, чтобы уточнить условия в выражении ключевого слова WHERE в запросе.
Интервалы времени можно добавлять к значениям типа DATETIME. В соответствии со стандартом интервалы времени должны использоваться так, как показано в следующих примерах.
DATE 4999-12-31' + INTERVAL Ч' DAY
'2000-01-01'
DATE 4999-12-31' + INTERVAL '!' MONTH
'2000-01-31'
В следующем примере используется функция DATEADD SQL Server.
SELECT DATEJHIRE, DATEADD(MONTH, 1, DATE_HIRE)
FROM EMPLOYEE_PAY_TBL
DATE_HIRE
DATEADD(M)
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91
6 rows affected.
В следующем примере используется функция ADD_MONTH Oracle.
SELECT DATEJHIRE, ADD_MONTH(DATE_HIRE,1)
FROM EMPLOYEE_PAY_TBL;
DATE_HIRE ADD_MONTH
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91
6 rows selected.
Чтобы добавить к дате один день в Oracle, используется следующий оператор.
SELECT DATE_HIRE, DATE_HIRE + 1
FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID = '311549902';
DATE_HIRE DATE_HIRE
23-MAY-89 24-MAY-89 1 row selected.
Обратите внимание на то, что эти примеры из Oracle и SQL Server хотя и отличаются синтаксически от стандарта ANSI, по сути базируются на тех же принципах, что и стандарт SQL.
Сравнение дат и значений времени
Очень полезным условным оператором стандарта SQL для значений типа DATETIME является оператор OVERLAPS. Оператор OVERLAPS используется для сравнения двух отрезков времени и возвращает TRUE (Истина), если эти отрезки времени пересекаются, и FALSE (Ложь) — если нет. Например, в результате следующего сравнения возвращается значение TRUE:
(TIME '01:00:00', TIME '05:59:00')
OVERLAPS
(TIME '05:00:00', TIME '07:00:00')
В результате следующего сравнения возвратится значение FALSE:
(TIME '01:00:00', TIME '05:59:00')
OVERLAPS
(TIME '06:00:00', TIME '07:00:00')
Другие функции для работы с датами
В следующем списке представлены некоторые полезные функции для работы с датами, предлагаемые реализациями Oracle и SQL Server.
SQL Server___________
DATE PART Возвращает числовое (целое) значение DATE PART для даты
DATENAME Возвращает текстовое значение DATEPART для даты
GETDATE () Возвращает системную дату
DATEDIFF Возвращает разность двух дат для указанных компонентов,
таких как дни, минуты или секунды
Oracle______________
NEXT_DAY Возвращает для указанной даты следующий день недели в
заданном виде(например, ПЯТНИЦА)
MONTH_BETWEEN Возвращает число месяцев между двумя заданными датами
Преобразования дат могут понадобиться по ряду причин. Главным образом, преобразования дат используются для изменения типа данных, изначально определенных по умолчанию как DATETIME или как-нибудь иначе в зависимости от реализации SQL.
Вот несколько типичных причин, по которым применяются преобразования дат:
• необходимость сравнения значений дат разных типов;
• необходимость представления значений дат в виде строк заданного формата;
• необходимость конвертирования символьных строк в формат даты.
Для конвертирования одних типов данных в другие используется ANSI-оператор CAST. Его базовый синтаксис следующий.
CAST ( выражение AS новый тип данных )
Конкретные примеры использования этого операторы будут показаны в следующих разделах.
Шаблон представления даты (date picture) состоит из элементов форматирования и используется для извлечения из базы данных информации о дате и времени в требуемом виде. В некоторых реализациях SQL использование шаблонов представления дат не предусмотрено.
Без использования форматирования информация о дате и времени извлекается из базы данных в определенном принятом по умолчанию виде, например,
1999-12-31
31-DEC-99
1999-12-31 23:59:01.11
Но как быть, если дату нужно представить, например, в следующем виде?
December 31, 1999
Тогда необходимо конвертировать дату из формата DATETIME в символьный. Это делается с помощью соответствующих функций, применение которых будет рассматриваться в следующих разделах.
Шаблоны представления дат Sybase
УУ год
qq квартал
mm месяц
dy день года
wk неделя
dw день недели
hh час
mi минута
ss секунда
ms миллисекунда
Шаблоны представления дат Oracle_____________________________
AD Н. Э.
AM ДО ПОЛУДНЯ
ВС ДО Н. Э.
CC столетие
D номер дня недели
DD номер дня месяца
DDD номер дня года
DAY название дня недели (MONDAY)
Day название дня недели (Monday)
day название дня недели (monday)
|
Шаблоны предстявления дат Oracle |
|
|
DY Dy dy HH НН12 НН24 J MI MM MON Mon mon MONTH Month month PM Q KM RR 3 SSSSS SYYYY W WW Y YY YYY YYYY YEAR Year year |
трехбуквенное сокращение для дня недели (MON) трехбуквенное сокращение для дня недели (Моп) трехбуквенное сокращение для дня недели (топ) час дня час дня час дня в 24-часовом формате день по юлианскому календарю с 12-31-4713 до н. э. минута часа номер месяца трехбуквенное сокращение для месяца (JAN) трехбуквенное сокращение для месяца (Jan) трехбуквенное сокращение для месяца (Jan) название месяца (JANUARY) название месяца (January) название месяца (January) после полудня номер квартала римский номер месяца две цифры года секунда минуты число секунд со времени полуночи год со знаком: например, 500 г. до н. э. = -500 номер недели месяца номер недели года последняя цифра года последние две цифры года последние три цифры года год год словами (NINETEEN-NINETY-NINE) год словами (Nineteen-Ninety-Nine) год словами (nineteen-ninety-nine) |
Преобразование дат в строки символов
Значения типа DATETIME конвертируют (преобразуют) в символьные строки для того, чтобы иметь возможность изменить представление дат при выводе данных запроса. Для этого используются функции преобразования. Вот пример преобразования значения даты и времени в символьную строку в запросе SQL Server:
SELECT DATE_HIRE = DATENAME(MONTH, DATE_HIRE)
FROM EMPLOYEE_PAY__TBL
DATE_HIRE
May
June
August
June
July
January
6 rows affected.
В следующем примере для преобразования используется функция TO_CHAR из Oracle:
SELECT DATE_HIRE, TO_CHAR(DATE_HIRE,'Month dd, yyyy') HIRE
FROM EMPLOYEE_PAY_TBL;
DATE_HIRE HIRE
23-MAY-89 May 23, 1989
17-JUN-90 June 17, 1990
14-AUG-94 August 14, 1994
28-JUN-97 June 28, 1997
22-JUL-96 July 22, 1996
14-JAN-91 January 14, 1991
6 rows selected.
Преобразование символьных строк в даты
Следующий пример иллюстрирует один из способов преобразования символьной строки в формат даты. После преобразования соответствующие данные можно сохранить в столбце, имеющем атрибут типа DATETIME.
SELECT TO_DATE('JANUARY 01 1999','MONTH DD YYYY')
FROM EMPLOYEE_PAY_TBL;
TO_DATE('
Ol-JAN-99
Ol-JAN-99
Ol-JAN-99
Ol-JAN-99
Ol-JAN-99
Ol-JAN-99
6 rows selected.
Вы, наверное, обратили внимание на то, что в выводе запроса представлены 6 строк, хотя в условии предлагается только одна дата. Такой результат получен по причине того, что преобразование выбиралось для данных таблицы EMPLOYEE_PAY_TBL, в которой шесть строк. Преобразование выполнялось для каждой из строк таблицы.
Вы получили представление о значениях типа DATETIME, определяемых на базе стандарта ANSI. Но как и в случае многих других элементов SQL, в большинстве реализаций языка имеются отклонения имен функций и их синтаксиса от стандарта SQL, хотя лежащие в их основе концепции определяются стандартом. На предыдущем уроке вы видели, как варьируют в зависимости от реализации языка предлагаемые функции, а в ходе этого урока вы получили возможность увидеть подобные отклонения для типов даты и времени и используемых с ними функций и операторов. Не забывайте о том, что не все предлагаемые здесь примеры будут
работать в рамках используемой вами реализации SQL, но вы должны понять принципы работы со значениями дат и времени, которые применимы в любой реализации языка.
По каким причинам в различных реализациях наблюдаются отклонения от стандартного набора типов данных и функций?
Такие различия в реализациях языка возникают, главным образом, по причине различного подхода производителей к выбору способа внутреннего хранения данных и, соответственно, оптимальных средств для извлечения этих данных. Все производители обязаны лишь обеспечить средства для хранения значений дат и времени на основе требований ANSI, предполагающих для дат и времени такие компоненты как год, месяц, день, час, минута, секунда и т. д.
Если я хочу сохранить дату и время не так, как предлагается в той конкретной реализации SQL, которую использую я, то какие возможности для этого у меня имеются?
Даты можно сохранить практически в любом виде, если сохранять их в столбце с атрибутами символьной строки переменной длины. Главное, о чем нужно не забывать, что при сравнении значений дат необходимо преобразовать строку со значением даты в значение подходящего типа DATETIME.
Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".
1. Откуда извлекаются системные дата и время?
2. Назовите стандартные компоненты значения типа DATETIME.
3. Какой фактор может оказаться важным при представлении и сравнении значений дат и времени, если ваша компания имеет статус международной организации?
4. Можно ли сравнить значение даты в виде символьной строки со значением даты типа DATETIME?
Запишите операторы SQL для представленных ниже упражнений, основываясь на следующей информации.
Используйте SYSDATE для получения текущих даты и времени. Используйте таблицу с именем DATES.
Для преобразования дат в символьные строки используйте функцию TO_CHAR со следующим синтаксисом:
TO_CHAR('выражение','шаблон представления даты')
Для преобразования символьных строк в даты используйте функцию TO_DATE со следующим синтаксисом:
TO_DATE('выражение','шаблон представления даты') Информация о шаблонах представления дат дана в следующей таблице.
Шаблон представления дат___Описание___________________
MONTH название месяца
DAY название дня недели
DD номер дня месяца
мм номер месяца года
YY последние две цифры года
YYYY ГОД
MI минута часа ss секунда минуты
1. Предположив, что сегодняшней датой является 1999-12-31, преобразуйте текущую дату в формат December 31 1999.
2. Преобразуйте строку 'DECEMBER 31 1999' в формат DATE.
3. Запишите оператор, возвращающий день недели, на который выпадает новогодний вечер конца 1999 года. Предположите, что дата хранится в виде 31-DEC-99, являясь значением типа DATETIME.