Глава 4. Типи даних часу в стандарті SQL
4.1. Поради по роботі з датами, часом і тимчасовими позначками
Синтаксис і можливості дати, тимчасової позначки (timestamp) і часу настільки сильно залежать від конкретної реалізації, що можна запропонувати лише загальні поради. У цьому розділі передбачається, що ваша реалізація SQL підтримує лише найбільш просту арифметику дат, але, можливо, ви знайдете бібліотечну функцію, яка зможе виконати всю роботу більш ефективно. До повної реалізації стандарту SQL-92 за подробицями зверніться до інструкцій по роботі з вашим продуктом.
4.1.1. Стандарти формату дати
4.1.2. Робота з відмітками дати і часу
4.1.3. Робота з часом
4.2. Запити, які містять дати
Майже всі реалізації SQL містять тип даних DATE, але їх функції в кожному випадку не однакові. Найбільш поширена функція - конструктор, який будує дати на основі цілих чисел і рядків; функції-екстрактори витягають місяць, день або рік; інші можливості дозволяють форматувати висновок.
Можна розраховувати, що ваша реалізація SQL містить найпростіші функції роботи з датами, хоча їх синтаксис може певною мірою залежати від продукту. Це такі функції:
1. Дата плюс або мінус число днів призводить до нової дати.
2. Дата мінус друга дата дає в результаті ціле число днів.
Погляньте на список допустимих комбінацій типів даних
Існують і інші правила, що стосуються роботи з часовими поясами і визначають відносну точність двох операндів, вони досить зрозумілі без пояснень.
У будь-якій реалізації має бути присутня функція, яка повертає поточну дату на підставі системного годинника. Ім'я цієї функції залежить від реалізації - це може бути TODAY, SYSDATE, CURRENT DATE, getdate () і ін. Можлива також функція, яка повертає день тижня на підставі дати, іноді вона називається DOW () або WEEKDAY (). У стандарті SQL-92 є функції CURRENT_DATE, CURRENT_TIME [(
4.3. Персональні календарі
Тепер треба створити таблицю з датами угод і платежів для цілого року. Оператори INSERT INTO для завантаження другої таблиці можна задавати за допомогою електронних таблиць; вони завжди містять зручні функції роботи з датами.
Зверніть увагу на деякі особливості календаря. Якби свят не було, дати угод і платежів завжди відстояли б один від одного на три дні (CURRENT_DATE + INTERVAL 3 DAYS). Можна побудувати електронну таблицю з двох стовпців, так що дати угод будуть знаходитися в одному з них, а в іншому - (tradedate + 3 days). Потім збережемо електронну таблицю в форматі ASCII і завантажимо в таблицю БД з наступним визначенням:
Тепер таблиця заповнена, як якщо б свят і вихідних не було. Оновимо її, щоб дні платежів були задані правильно. Почнемо з свят зі списку:
Подібний код переміщує дату платежу на один день вперед, якщо в даному проміжку зустрічається свято. Наприклад, якщо свято припадає на п'ятницю, то дата буде перенесена на суботу. Тепер треба перенести відповідну дату з вихідних на понеділок:
Повторюйте ці три оновлення, поки не отримаєте системне повідомлення про те, що було змінено нуль записів. Це важливо; для прикладу уявімо ситуацію, коли вихідні разом зі святами тривають безперервно чотири дні. В такому випадку п'ятницю переноситься на суботу, субота та неділя на понеділок, а понеділок на вівторок. Оскільки понеділок - це свято, все його призначення теж переносяться на вівторок. Отже, щоб розташувати всі дати на правильних місцях, необхідно зробити друге коло оновлення.
Якщо ви хочете дещо скоротити таблицю, врахуйте, що всі дати укладення угод повинні доводитися на робочі дні, а не на вихідні. Це дозволяє скоротити готову таблицю приблизно на 100 рядків (52 вихідних х 2 дні):
Щоб дізнатися, чому дата була перенесена, необхідно прочитати назви і дати вихідних в запиті:
Підсумкова таблиця буде містити близько 250 рядків і тільки два стовпці. Це дуже мало і легко поміститься на диск практично будь-якого комп'ютера. Пошук дня платежу здійснюється за допомогою одного запиту; якщо ж у вас задана тільки таблиця свят, вам довелося б писати процедурний код.
4.4. тимчасові серії
Одна з великих проблем пов'язана з необхідністю обробки послідовності подій, що відбуваються в один і той же часовий період або в деякому конкретному порядку. Код для цього досить складний, його важко зрозуміти; основний задум полягає в тому, що у вас є таблиця з початковим і кінцевим часом подій, а ви хочете отримати інформацію про них як про групу.
4.4.1. Проміжки в тимчасових серіях
Лінію часу можна розбити на інтервали, а потім для створення звітів виводити групи таких інтервалів. Наприклад, одним з основних питань при прийнятті на роботу є прохання пояснити претенденту причини пропусків в його трудовій книжці. Як правило, такі пропуски означають, що людина не працював (іноді це називається: "був консультантом" - синонім безробітного).
Складемо запит SQL, що виводить тривалість періодів зайнятості (незайнятості) для кандидатів. Передбачається, що ваша реалізація SQL містить функції DATE, здатні виконувати найпростіші математичні операції з календарем.
Зверніть увагу, що кінцеву дату поточного завдання треба встановити на будь-яке віддалене час або можна видалити обмеження NOT NULL і використовувати NULL-значення. Стандарт SQL не підтримує значення типу 'eternity' або end of time '(вічність або кінець часу). Найбільша яка надається в SQL дата - це '9999-12-31 23:59 "59.999999', її і використовуйте.
Очевидно, такий запит повинен містити сполуки таблиці з собою, але необхідно застосувати також і деяку арифметику дат, щоб врахувати той факт, що перший день кожного періоду незайнятості є останнім днем попереднього періоду зайнятості плюс 1, а останній день періоду незайнятості є перший день наступного призначення мінус 1. Проблема початкового і кінцевого дня пояснює наявність в SQL предиката OVERLAPS.
Велика частина версій SQL підтримує дати, і в стандарті SQL-92 є визначення типів дат і арифметичні операції для роботи з ними. На жаль, у всіх реалізаціях ці типи представлені по-різному, і немає нічого схожого на єдиний стандарт ANSI. В даному прикладі буде використана добре читається, але нестандартна нотація, яку ви зможете перевести на мову вашого продукту.
Перша спроба скласти необхідний запит зазвичай призводить до чогось схожого на нижченаведений лістинг. Цей запит видає правильні результати, але також і кілька додаткових непотрібних рядків. Передбачається, що, додавши певну кількість днів до дати або віднявши їх, ви отримаєте нову дату.
Цей запит не підходить з наступних причин. Припустимо, в моїй таблиці міститься ім'я кандидата Bill Jones і історія його трудової діяльності:
Ми отримуємо результат:
Рядок з ім'ям John Smith виглядає цілком пристойно, і ви можете подумати, що і з рештою таблицею все в порядку. Він працював в двох місцях і, таким чином, повинен мати один період незайнятості. Однак рядок Bill Jones вже не може бути правильною - його три періоди зайнятості повинні розділяти два періоди незайнятості. Однак запит виводить три таких проміжку.
Запит застосовує з'єднання до всіх можливих комбінацій початковій і кінцевій дат початкової таблиці. Виникають помилкові дані, оскільки кінець одного періоду зайнятості ( 'Scut Worker') і початок іншого (Grand Poobah ') він вважає як період незайнятості. Нам же треба враховувати тільки останню завершену роботу. Це можна зробити за допомогою функції МАХ () і ще одного корельованого підзапиту. Фінальний результат такий:
4.4.2. Суцільні періоди часу
Якщо претендент представив список робіт, початок і кінець яких припадали на різний час, треба простежити, чи дійсно все це час він працював без перерви. Побудуємо для співробітника таблицю зайнятості:
Треба створити умова пошуку для всіх характеристик суцільного періоду часу.
Однак можна скласти запит, що виводить також і періоди зайнятості, що обмежують суцільний період:
Якщо ви працюєте з СУБД Sybase SQL Anywhere, цей запит можна модифікувати, включивши в нього строкову агрегатную функцію LIST () (подробиці див. В розділі 21.5).
Функція LIST () здійснює автоматичне сортування. Зверніть увагу, що останній рядок результату фактично є підмножиною передостанній рядки. Якщо ви збираєтеся зберегти відповідь в таблиці, то всі подібні підмножини можна видалити за допомогою строкової функції POSITION ().
Застосування цієї процедури до дат допустимо, хоча досить складно.
4.4.3. локалізація дат
Перший підхід до вирішення проблеми - використовувати фрагменти компонентів дат в умовах пошуку. Наприклад, так можна знайти дні народження всіх співробітників в одному і тому ж місяці:
Однак спроба розширити такий підхід працювати не буде, оскільки 45-денний проміжок часу може припасти на три місяці і, можливо, перейти на наступний рік, який може виявитися високосним. Дуже скоро число звернень до функції значно збільшиться, і логіка сильно ускладниться.
Другий підхід - написати просте умова пошуку з цими функціями і конструювати з його допомогою дні народження в поточному році з дат народження (dob) таблиці Employee:
У перекладі на звичайну мову вислів означає: якщо співробітник стане на рік старше через 45 днів, його день народження знаходиться в даному проміжку.
4.4.4. Перший і останній дні місяця
Дати можна включати в БД різними способами. Проектувальники одного продукту можуть віддати перевагу формат типу мови Cobol, де для року, місяця і дня в будь-яку дату є окрема область. Інший продукт може використовувати стиль UNIX, де дата визначається як зміщення від деякої початкової точки, виражене в деяких невеликих одиницях часу, а формат відображення дати обчислюється при необхідності. У стандарті SQL-92 формат зберігання даних не описаний, але "підхід Cobol" легше відобразити на екрані, а "підхід зміщення" дозволяє простіше здійснювати обчислення.
В результаті виявляється, що ідеального способу обчислення першого або останнього дня місяця цієї дати не існує. У методі Cobol можна отримати перший день місяця, перетворивши дату так, щоб в поле дня помістити цифру 1.
Останній день місяця отримати кілька важче. У методі Cobol, як правило, є функція, конструюються дату з числових полів року, місяця і дня. Вам може здатися, що, сконструювавши перший день наступного місяця і вирахувавши з нього один день, ви отримаєте шукане:
Він працює швидше з продуктами, які застосовують до дат метод зсуву. Загальна підвираз, а також вираз INTERVAL обчислюються тільки один раз. Все інше - це арифметика роботи з цілими числами. Перший день обчислити дуже просто:
Пам'ятайте, що розподіл є цілочисельним, так як беруть участь в ньому змінні відносяться до цілого типу. Погляньте на іншу версію алгоритму, що виконує той же обчислення:
Ця програма заснована на припущенні, що функція CAST (x AS INTEGER) усікає x, а не округлює його; перевірте документацію вашого процесора SQL.
Нижче наводиться написана на мові Pascal процедура перетворення григоріанських дат в юліанські, вона взята з книги Numerical Recipes in Pascal (Press, Flannery and Vetterling, 1989).
Щоб перетворити юліанський день в григоріанський, зробіть наступне:
З цими алгоритмами пов'язані дві проблеми. По-перше, астрономи починають юліанський день опівдні. Якщо подумати, в цьому є сенс, адже свою роботу вони виконують в основному ночами. Друга проблема пов'язана з великими розмірами беруть участь в обчисленнях цілих чисел; ви не зможете замінювати їх числами з плаваючими точками, так як при цьому сильно зростають помилки округлення. Більш того, вам будуть потрібні числа типу long integer (довгі цілі), оскільки їх діапазон охоплює 2.5 мільйона значень.
4.6. Функції вилучення дати і часу
Використання опцій для роботи з типами даних
Очевидно, такий метод можна розширити і на інші строкові функції, що дозволить шукати фрагменти дати або часу, діапазони дат і т.д.
Рекомендується прочитати керівництво по використанню вашого продукту SQL і в ньому пошукати опис можливостей його бібліотечних функцій.
4.7. Інші функції роботи з датами і часом
Існує дуже поширена група функцій, не представлена в стандарті SQL. Це функції роботи з тижнями. Наприклад, SQL Anywhere компанії Sybase (раніше WATCOM SQL) містить функцію DOW (
Продукти DB2 і XDB SQL містять функцію AGE (
У таблиці в розділі 10.1 наведено резюме по всіх допустимим арифметичним операторам в SQL-92, включаючи типи даних
Операції з даними типу
Операції з типами
Предикат OVERLAPS визначає, чи перетинаються в часі два хронологічних відрізка (детально це описано в розділі 13.2). Останній визначається або парою значень типу
Функція EXTRACT (
Друга проблема може шокувати. Як нас вчили в школі, в році 365,25 дня, і накопичення дрібних частин дня призводить до високосному році кожні чотири роки. Вчителі брехали - в році насправді 365,2422 дня; накопичуючись, кожні 400 років ця відмінність дає ще один день. Оскільки більшості з нас ще немає 400 років, нам поки не доводилося турбуватися з цього приводу. Мовою SQL / PSM коректна перевірка високосного року виглядає так:
Більш компактне рішення було запропоновано Філом Александером; його можна включити у вбудований код в якості вираження для пошуку:
4.8.4 Фатальні дати в успадкованих даних