Сегодня в СУБД Oracle есть несколько типов данных для хранения дат и времени. Самый старый из них - тип DATE - совершенно точно был еще в Oracle 7 (с более ранними версиями СУБД я не работал). Тогда ввести значение типа DATE можно было только с помощью функции to_date. В версии 9 появился литерал для значений типа DATE (а также новые типы для дат и времени TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE и TIMESTAMP WITH TIME ZONE). Рассмотрим сегодня некоторые особенности типа DATE.
Тип DATE позволяет работать с датами и временем с точностью до 1 секунды в диапазоне от date '-4712-01-01' (1 января 4713 года до нашей эры (это не ошибка, читайте дальше)) до date '9999-12-31' (31 декабря 9999 нашей эры). Проверим это:
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss AD';
Session altered.
SQL> select date '-4712-01-01', date '9999-01-01' from dual;
DATE'-4712-01-01' DATE'9999-01-01'
---------------------- ----------------------
4712-01-01 00:00:00 BC 9999-01-01 00:00:00 AD
Днем раньше или позже:
SQL> select date '-4712-01-01'-1 from dual;
select date '-4712-01-01'-1 from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
SQL> select date '9999-12-31'+1 from dual;
select date '9999-12-31'+1 from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Как видим, попытка выйти за границы разрешенного диапазона не удалась. Однако, сообщение об ошибке утверждает, что минимальным допустимым годом является -4713, а не -4712, и что недопустимо указывать нулевой год 0000. На самом деле, попытка работать с -4713 годом привела нас к ошибке, а вот литерал с годом 0000 работает успешно (чего не скажешь о функции to_date):
SQL> select date '0000-01-01' from dual;
DATE'0000-01-01'
----------------------
0000-01-01 00:00:00 AD
SQL> select to_date('0000-01-01', 'yyyy-mm-dd') from dual;
select to_date('0000-01-01', 'yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
В чем здесь дело?
Как известно, существует соглашение о счете лет от рождения Иисуса Христа. По этому соглашению, 1-й год нашей эры (0001 AD) - это 1-й год жизни Иисуса. Год, предшествующий 1-му году нашей эры, считается 1-м годом до нашей эры (0001 BC). Между 1 января 1 года до н. э. и 1 января 1 года н. э. прошел один год. Нулевого года в традиционном летоисчислении просто нет.
Логично, что новая эра начинается с 1-го года. Ведь и для новорожденного сразу начинается 1-ый год жизни, а когда ему исполняется 1 год - начинается его второй год жизни. Когда 1 января 2001 года наступил XXI век, то сразу же начался 1-ый год нового века, правильно? Правильно.
Однако, отсутствие нулевого года не согласуется с арифметикой. Если 1 год до н. э. принять за -1, то разность между числами, представляющими два соседних года в календаре, оказывается равна двум: 1 - (-1) = 2. Другой казус состоит в том, что 1 год до н. э. должен быть високосным, поскольку он случился на 4 года раньше, чем 4-й год н. э., который является високосным. Но -1 не делится на 4 без остатка и потому не удовлетворяет правилу високосного года!
Чтобы устранить эти неудобства и упростить астрономические расчеты, уже давным-давно астрономы ввели в обращение нулевой год. Астрономический нулевой год
- соответствует 1-му году до н. э. в традиционном летоисчислении,
- арифметически отстоит от следующего за ним года на 1: 1 - 0 = 1,
- является високосным по правилу определения високосного года.
1-й и последующий астрономические годы соответствуют 1-му и последующим годам н. э., а 0-ой, минус 1-ый и предшествующие отрицательные годы соответствуют 1-му, 2-му и предшествующим годам до н. э., соответственно (со сдвигом на 1).
Так вот, в СУБД Oracle реализован астрономический счет лет:
SQL> select date '0001-01-01' - 1 from dual;
DATE'0001-01-01'-1
----------------------
0000-12-31 00:00:00 AD
SQL> select date '0001-01-01' - date '0000-01-01' from dual;
DATE'0001-01-01'-DATE'0000-01-01'
---------------------------------
366
SQL> select date '0000-01-01' - 1 from dual;
DATE'0000-01-01'-1
----------------------
0001-12-31 00:00:00 BC
Однако, индикатор BC (до нашей эры) в последнем примере создает впечатление, что перед нами не астрономическая дата, а дата традиционного христианского летоисчисления. Это некорректно со стороны Oracle, ведь на самом деле дата 0001-12-31 00:00:00 BC из последнего запроса соответствует 31 декабря 2 года до н. э.
Поэтому, имея дело в СУБД Oracle с датами до нашей эры, лучше избегать индикатора BC (и помнить об астрономическом летоисчислении):
SQL> alter session set nls_date_format = 'syyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select date '0000-01-01' - 1 from dual;
DATE'0000-01-01'-1
--------------------
-0001-12-31 00:00:00
Если вернуться к нашему первому запросу, то, в свете сказанного становится понятно, что литерал date '-4712-01-01', задающий минимальное допустимое значение типа DATE, соответствует 1 января 4713 до нашей эры.
SQL> select date '-4712-01-01', date '9999-01-01' from dual;
DATE'-4712-01-01' DATE'9999-01-01'
-------------------- --------------------
-4712-01-01 00:00:00 9999-01-01 00:00:00
Встретившееся ранее сообщение об ошибке
"ORA-01841: (full) year must be between -4713 and +9999, and not be 0"
исходит из того, что даты в СУБД Oracle имеют традиционное, а не астрономическое представление, что, как мы видим, расходится с практикой.
Минимально допустимая дата 1 января 4713 г. до н. э. в СУБД Oracle не случайна. Это день начала цикла Скалигера, с которого астрономы ведут отсчет так называемых "юлианских дней". На эту тему см. статью в Википедии.
Oracle позволяет преобразовывать даты в юлианские дни, и наоборот. Вот крайние даты диапазона значений DATE, выраженные в юлианских днях, и некоторые круглые юлианские дни, выраженные в традиционных датах:
SQL> select to_char(date '-4712-01-01', 'j'),
2 to_char(date '9999-12-31', 'j')
3 from dual;
TO_CHAR(DATE'-4712-01-01','J TO_CHAR(DATE'9999-12-31','J'
---------------------------- ----------------------------
0000001 5373484
SQL> select to_date(1000000, 'j'),
2 to_date(2000000, 'j'),
3 to_date(3000000, 'j')
4 from dual;
TO_DATE(1000000,'J') TO_DATE(2000000,'J') TO_DATE(3000000,'J')
-------------------- -------------------- --------------------
-1975-11-07 00:00:00 0763-09-14 00:00:00 3501-08-15 00:00:00
Какой сегодня у нас юлианский день?
SQL> select to_char(sysdate, 'j') from dual;
TO_CHAR(SYSDATE,'J')
----------------------------
2457861
Возможность преобразований между юлианскими днями и традиционными датами означает, в частности, что даты с точностью до суток можно хранить в СУБД Oracle как число - номер юлианского дня. Арифметика с юлианскими днями, очевидно, соответствует арифметике с датами в СУБД Oracle, за исключением того, что даты складывать нельзя, а юлианские дни - можно (например, чтобы найти середину диапазона):
SQL> select (date '2017-01-01' + date '2017-06-30')/2 from dual;
select (date '2017-01-01' + date '2017-06-30')/2 from dual
*
ERROR at line 1:
ORA-00975: date + date not allowed
SQL> select to_date(
2 (to_char(date '2017-01-01','j') + to_char(date '2017-06-30','j'))/2,
3 'j')
4 from dual;
TO_DATE((TO_CHAR(DAT
--------------------
2017-04-01 00:00:00
С помощью юлианских дней легко определить день недели (0 - понедельник, 1 - вторник, и т.д.):
SQL> select mod(to_char(trunc(sysdate),'j'), 7) from dual;
MOD(TO_CHAR(TRUNC(SYSDATE),'J'),7)
----------------------------------
0
Календарь - вещь не менее увлекательная, чем карта. Да он и есть карта времени.
Сегодня повсеместно принят григорианский календарь (иногда наряду с национальными календарями, как, например, в Японии), а ему исторически предшествовал юлианский календарь. Отличие юлианского и григорианского календарей - в правиле определения високосного года.
В юлианском календаре, установленном Юлием Цезарем в 46 году до н.э., правило определения високосного года такое: если число, представляющее год, делится на 4 без остатка, значит, год високосный и содержит 366 дней, иначе год не високосный и содержит 365 дней. Таким образом, в юлианском календаре каждый 4-й год високосный.
Применим это правило к годам с 1895 по 1904:
SQL> select to_char(lvl, '0000') year,
2 case when mod(lvl, 4) = 0 then 'yes' else null end is_leap_year
3 from (select level+1894 lvl from dual connect by level <= 10)
4 ;
YEAR IS_LEAP_YEAR
-------------------- ------------
1895
1896 yes
1897
1898
1899
1900 yes
1901
1902
1903
1904 yes
10 rows selected.
В григорианском календаре, впервые введенном папой Григорием XIII в XVI веке, правило високосного года изменилось из-за того, что были получены уточненные данные о продолжительности астрономического года. Оказалось, что астрономический год на 11 минут короче года, положенного в основу юлианского календаря и равного 365.25 дней. Поэтому правило определения високосного года стало таким: если число, представляющее год, делится на 4 без остатка и (не делится на 100 или делится на 400), значит, год високосный и содержит 366 дней, иначе год не високосный и содержит 365 дней. Таким образом, в григорианском календаре високосным является каждый 4-й год, за исключением кратных 100 и при этом не кратных 400.
Применим это правило к годам с 1895 по 1904:
SQL> select to_char(lvl, '0000') year,
2 case when mod(lvl, 4) = 0 and (mod(lvl, 100) != 0 or mod(lvl, 400) = 0) then 'yes' else null end is_leap_year
3 from (select level+1894 lvl from dual connect by level <= 10)
4 ;
YEAR IS_LEAP_YEAR
-------------------- ------------
1895
1896 yes
1897
1898
1899
1900
1901
1902
1903
1904 yes
10 rows selected.
Переход от юлианского к григорианскому календарю в разных странах имел место в разное время и растянулся с XVI по XX века. Первыми на григорианский календарь перешли несколько католических стран Европы в октябре 1582 года; при этом за 4-тым октября сразу последовало 15-е. Британия и ее колонии, включая североамериканские, перешли на григорианский календарь в сентябре 1752 года. Одной из последних на григорианский календарь перешла Россия в 1918 году: после 31 января наступило 14 февраля.
Следующие запросы демонстрируют, что в СУБД Oracle
- для дат ранее XVI века работает юлианское правило високосного года,
- в октябре 1582 реализован переход от юлианского к григорианскому календарю,
- для дат позднее XVI века работает григорианское правило високосного года.
SQL> select date '1200-02-29' from dual;
DATE'1200-02-29'
--------------------
1200-02-29 00:00:00
SQL> select date '1300-02-29' from dual;
DATE'1300-02-29'
--------------------
1300-02-29 00:00:00
SQL> select date '1400-02-29' from dual;
DATE'1400-02-29'
--------------------
1400-02-29 00:00:00
SQL> select date '1500-02-29' from dual;
DATE'1500-02-29'
--------------------
1500-02-29 00:00:00
SQL> select date '1582-10-04', date '1582-10-04'+1 from dual;
DATE'1582-10-04' DATE'1582-10-04'+1
-------------------- --------------------
1582-10-04 00:00:00 1582-10-15 00:00:00
SQL> select date '1582-10-31' - date '1582-09-30' from dual;
DATE'1582-10-31'-DATE'1582-09-30'
---------------------------------
21
SQL> select date '1600-02-29' from dual;
DATE'1600-02-29'
--------------------
1600-02-29 00:00:00
SQL> select date '1700-02-29' from dual;
select date '1700-02-29' from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL> select date '1800-02-29' from dual;
select date '1800-02-29' from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL> select date '1900-02-29' from dual;
select date '1900-02-29' from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL> select date '2000-02-29' from dual;
DATE'2000-02-29'
--------------------
2000-02-29 00:00:00
Любопытно, что согласно утилите cal в Linux переход к григорианскому календарю происходит не в октябре 1582 (вместе с католическими странами Европы), а в сентябре 1752 года (вместе с Британией и колониями):
[ay@tsuki ~]$ cal 10 1582
October 1582
Su Mo Tu We Th Fr Sa
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
[ay@tsuki ~]$ cal 9 1752
September 1752
Su Mo Tu We Th Fr Sa
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
[ay@tsuki ~]$ cal 2 1700
February 1700
Su Mo Tu We Th Fr Sa
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29
[ay@tsuki ~]$ cal 2 1900
February 1900
Su Mo Tu We Th Fr Sa
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28
В заключение разговора о типе DATE, пара этюдов практического характера.
Значение типа DATE с точностью до секунд и сегодня нельзя задать с помощью литерала. Как и прежде, сделать это можно с помощью функции to_date:
SQL> select date '2017-04-17 20:15:41' from dual;
select date '2017-04-17 20:15:41' from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> select to_date('2017-04-17 20:15:41', 'yyyy-mm-dd hh24-mi-ss') from dual;
TO_DATE('2017-04-172
--------------------
2017-04-17 20:15:41
Функция sysdate возвращает текущие системные дату и время, а функция current_date - текущие дату и время в часовом поясе, в котором работает пользователь:
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
2017-04-17 20:52:53 2017-04-17 20:52:53
Совпадение результатов говорит о том, что часовой пояс, в котором работает сервер СУБД Oracle, и часовой пояс пользователя совпадают. Изменим часовой пояс пользователя и увидим разницу:
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------------
+10:00
SQL> alter session set time_zone = '+3:00';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------------
+03:00
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE
-------------------- --------------------
2017-04-17 20:56:33 2017-04-17 13:56:33
Комментариев нет:
Отправить комментарий