how to insert date and time in oracle?
Im having trouble inserting a row in my table. Here is the insert statement and table creation. This is part of a uni assignment hence the simplicity, what am i doing wrong? Im using oracle SQL developer Version 3.0.04.’
The problem i am having is that it is only inserting the dd/mon/yy but not the time. How do i get it to insert the time as well?
Thanks for the help.
EDIT: This is making no sense, i enter just a time in the field to test if time is working and it outputs a date WTF? This is really weird i may not use a date field and just enter the time in, i realise this will result in issues manipulating the data but this is making no sense.
Как пишутся датафикс на oracle
Словарь данных. Первыми таблицами, создаваемыми в любой базе данных, являются системные таблицы, или словарь данных Oracle. Системные таблицы хранят информацию о структуре базы данных и объектов внутри нее, и Oracle обращается к ним, когда нуждается в информации о базе данных или когда выполняет оператор DDL (Data Definition Language — язык определения данных) либо оператор DML (Data Manipulation Language — язык манипулирования данными). Эти таблицы никогда непосредственно не обновляются, однако обновление в них происходит в фоновом режиме всякий раз, когда выполняется оператор DDL. Главные таблицы словаря данных содержат нормализованную информацию, которая является довольно трудной для восприятия человеком, так что в Oracle предусмотрен набор представлений, выдающих информацию главных системных таблиц в более понятном виде. Oracle запрашивает информацию из таблиц словаря данных для синтаксического разбора любого оператора SQL. Информация кэшируется в области словаря данных разделяемого пула в SGA.
Сегменты отката . Когда данные в Oracle изменяются, изменение должно быть или подтверждено, или отменено. Если изменение отменяется («откатывается назад»), содержимое блоков данных восстанавливается в исходное состояние, существовавшее до изменения. Сегменты отката — это системные объекты, которые поддерживают этот процесс. Всякий раз, когда осуществляются какие-либо изменения в таблицах приложения или в системных таблицах, в сегмент отката автоматически помещается предыдущая версия изменяемых данных, так что старая версия данных всегда доступна, если требуется отказ. Другие пользователи при необходимости чтения данных, в то время как изменение не завершено, всегда имеют доступ к прежней версии из сегмента отката. Им предоставляется непротиворечивая по чтению версия данных. После того как изменение фиксируется, доступной становится измененная версия данных. Сегменты отката получают внешнюю память таким же образом, как другие сегменты — экстентами. Сегменту отката, однако, нужно первоначально распределить минимум два экстента.
Временные сегменты используют пространство в файлах базы данных, чтобы создать временную рабочую область для промежуточных стадий обработки SQL и для больших операций сортировки. Oracle создает временные сегменты в процессе работы и они автоматически удаляются, когда фоновый процесс SMON больше в них не нуждается. Если требуется только небольшая рабочая область, Oracle не создает временного сегмента, но вместо этого как временная рабочая область используется часть памяти PGA (глобальная область программы). Администратор базы данных может определять, в каких табличных пространствах будут располагаться временные сегменты для различных пользователей.
Сегмент начальной загрузки (или кэш-сегмент) — специальный тип объекта в базе данных, выполняющий начальную загрузку кэша словаря данных в область разделяемого пула SGA. Oracle использует кэш-сегмент только при запуске экземпляра и не обращается к нему вплоть до рестарта экземпляра. Сегмент необходим, чтобы выполнить начальную загрузку кэша словаря данных, после чего занимаемая им память освобождается.
Фундаментальное различие между RDBMS и другими БД и файловыми системами заключается в способе доступа к данным. RDBMS позволяет обращаться к физическим данным в более абстрактной, логической форме, обеспечивая легкость и гибкость при разработке кода приложения. Программы, использующие RDBMS, обращаются к данным через «машину» базы данных без непосредственной зависимости от фактического источника данных, изолируя приложение от деталей «нижележащих» физических структур данных. RDBMS сама заботится о том, где поле хранится в базе данных. Такая независимость данных возможна благодаря словарю данных RDBMS, который хранит метаданные (данные о данных) для всех объектов, расположенных в базе данных.
Словарь данных Oracle — множество таблиц и объектов базы данных, которое хранится в специальной области базы данных и ведется исключительно ядром Oracle. Словарь данных содержит информацию об объектах базы данных, пользователях и событиях. К этой информации можно обратиться с помощью представлений словаря данных. Как показано на рис.31, запросы чтения или обновления базы данных обрабатываются ядром Oracle с использованием информации из словаря данных.
Информация в словаре данных предназначена для подтверждения существования объектов, обеспечения доступа к ним и описания фактического физического расположения в памяти.
RDBMS не только обеспечивает размещение данных, но также определяет оптимальный путь доступа для хранения или выборки данных. Oracle использует сложные алгоритмы, которые позволяют выбирать информацию с наибольшей производительностью, исходя из критерия скорейшего получения первых строк результата или критерия минимального времени выполнения запроса в целом.
Представления словаря данных
Словарь данных содержит информацию об объектах базы данных, пользователях и событиях. К этой информации можно обратиться с помощью представления словаря данных:
ALL_OBJECTS
Системные привилегии, выданные текущему пользователю.
Динамические таблицы производительности, доступные пользователю SYS, позволяют управлять производительностью работы сервера СУБД.
| V$ACCESS | Заблокированные на текущий момент объекты и сеансы, в которых они используются. |
| V$ARCHIVE | Информация о журналах архива для каждого потока системы базы данных. . |
| V$BACKUP | Статус сброса всех ON-LINE баз данных. |
| V$BGPROCESS | Описание фоновых процессов. |
| V$CIRCUIT | Информация о виртуальных цепях. |
| V$DATABASE | Информация из контрольного файла о базе данных. |
| V$DATAFILE | Информация из контрольного файла о файлах базы данных. |
| V$DBFILE | Информация о всех файлах базы данных. |
| V$DB-OBJECT-CACHE | Объекты базы данных, находящиеся в библиотечном кеше. |
| V$DISPATCHER | Информация о процессах диспетчера. |
| V$ENABLEDPRIVS | Включенные привилегии. |
| V$F1LESTAT | Информация о статистике ввода/вывода в файл. |
| V$FIXED-TABLE | Все таблицы, представления и производные та |
| V$INSTANCE | блицы в базе данных. |
| V$INSTANCE | Статус текущего экземпляра |
| V$ LATCH | Число задержек каждого типа. (Строки этой таблицы однозначно соответствуют строкам таблицы V$ATCHHOLDER) |
| V$LATCHHOLDER | Информация о владельцах задержек. |
| V$LATCHNAME | Закодированные имена задержек из таблицы V$ATCH. |
| V$LIBRARYCACHE | Статистика по управлению буферами библиотечной памяти. |
| V$LICENSE | Параметры лицензии. |
| V$ADCSTAT | Статистика SQL*Loader при выполнении прямой загрузки. |
| V$LOADTSTAT | Статистика SQL* Loader при выполнении прямой загрузки. |
| V$LOCK | Блокировки и ресурсы. |
| V$LOG | Информация о журнальном файле. |
| V$LOGFILE | Информация о журнальных файлах. |
| V$LOGHIST | Информация об истории журнального файла. |
| V$LOG-HISTORY | Информация об истории журнального файла. |
| U$NLS-PARAMETERS | Текущие значения параметров NLS. |
| V$OPEN-CURSOR | Открытые пользователями курсоры. |
| V$PARAMETER | Информация о текущих значениях параметров. |
| V$PROCESS | Информация о всех активных процессах. |
| V$QUEUE | Информация об очереди мульти-серверных сообщений. |
| V$RECOVERY-LOG | Журнальные файлы, необходимые для полного восстановления базы данных. |
| V$RECOVER-FILE | Статус файлов, которые нужно восстанавливать. |
| V$REQD1ST | Гистограмма времен обращения, разделенная на 12 столбцов или периодов времени. |
| V$RESOURCE | Информация о ресурсах. |
| V$ROLLNAME | Имена всех активных сегментов отката. |
| V$ROLLSTAT | Статистика для всех активных сегментов отката. |
| V$ROWCACHE | Статистика активности словаря данных. (Одна строка для каждого буфера памяти) |
| V$SECONDARY | Представление Trusted ORACLE, u котором перечислены вторичнее смонтированные базы данных. |
| V$SESS10N | Информация о текущих сеансах. |
| V$SESS10N-WA1T | Список ресурсов или событий, которых ожидает текущий сеанс. |
| V$SESSTAT | Статистика для текущих сеансов. |
| V$SGA | Суммарная информация об SGA. |
| V$SHARED-SERVER | Информация о вcex разделяемых процессах сервера. |
| V$SQLAREA | Статистика о разделяемых буферах памяти курсора. Одна строка для каждого курсора. |
| V$SQLTEXT | Текст команд SQL, находящихся в разделенных курсорах SGA. |
| V$STATNAME | Раскодированные имена для статистик .из таблицы V$SESSTAT. |
| V$SYSLABEL | Представление Trusted ORACLE, в котором перечислены системные метки. |
| V$SYSSTAT | Текущие значения статистик из таблицы V$SESSTAT. |
| V$THREAD | Информация о потоках, содержащихся п контрольном файле. |
| V$TIMER | Текущее время в сотых долях секунды. |
| V$TRANSACTION | Информация о транзакциях. |
| V$TYPE-SIZE | Размеры различных компонентов базы данных. |
| V$VERSION | Имена версии компонентов библиотеки ядра ORACLE. |
| V$WAITSTAT | Статистика содержимого блока. Обновляется только при включенной временной статистики. |
Список сцепленных строк таблицы или кластера, использованного в команде ANALYZE.
| Столбец | Тип данных |
| OWNER-NAME | VARCHAR2 |
| TABLE-NAME | VARCHAR2 |
| CLUSTER-NAME | VARCHAR2 |
| HEAD_ROWID | ROWID |
| TIMESTAMP | DATE |
Эта таблица используется для определения строк, нарушающих правила целостности, если правила целостности включены.
| Столбец | Тип данных |
| HEAD.ROWID | ROWID |
| OWNER | VARCHAR2 |
| TABLE-NAME | VARCHAR2 |
| CONSTRAINT | VARCHAR2 |
Эта таблица может заполняться командой EXPLAIN PLAN для того, чтобы описать план выполнения оператора SQL.
| Столбец | Тип данных |
| STATEMENT.ID | VARCHAR2 |
| TIMESTAMP | DATE |
| REMARKS | VARCHAR2 |
| OPERATION | VARCHAR2 |
| OPTIONS | VARCHAR |
| OBJECT_NODE | VARCHAR2 |
| OBJECT_OWNER | VARCHAR2 |
| OBJECT.NAME | VARCHAR |
| OBJECT_INSTANCE | NUMBER |
| OBJECT_TYPE | VARCHAR2 |
| SEARCH_COLUMNS | NUMBER |
| ID | NUMBER |
| PARENT.ID | NUMBER |
| POSITION | NUMBER |
| OTHER | LONG |
2.3.3 Защита данных.
Транзакции, фиксация и откат. Изменения в базе данных не сохраняются, пока пользователь явно не укажет, что результаты вставки, модификации и удаления должны быть зафиксированы окончательно. Вплоть до этого момента изменения находятся в отложенном состоянии, и какие-либо сбои, подобные аварийному отказу машины, аннулируют изменения.
Транзакция — элементарная единица работы, состоящая из одного или нескольких операторов SQL;
Все результаты транзакции или целиком сохраняются (фиксируются), или.целиком отменяются (откатываются назад). Фиксация транзакции делает изменения окончательными, занося их в базу данных, и после того как транзакция фиксируется, изменения не могут быть отменены. Откат отменяет все вставки, модификации и удаления, сделанные в транзакции; после отката транзакции ее изменения не могут быть зафиксированы. Процесс фиксации транзакции подразумевает запись изменений, занесенных в журнальный кэш SGA, в оперативные журнальные файлы на диске. Если этот дисковый ввод/вывод успешен, приложение получает сообщение об успешной фиксации транзакции. (Текст сообшения изменяется в зависимости от инструментального средства.) Фоновый процесс DBWR может записывать блоки актуальных данных Oracle в буферный кэш SGA базы данных позже. В случае сбоя системы Oracle может автоматически повторить изменения из журнальных файлов, даже если блоки данных Oracle не были перед сбоем записаны в файлы базы данных.
Oracle также реализует идею отката на уровне оператора. Если произойдет единственный сбой при выполнении оператора, весь оператор завершится неудачей. Если оператор терпит неудачу в пределах транзакции, остальные операторы транзакции будут находиться в отложенном состоянии и должны либо фиксироваться, либо откатываться.
Все блокировки, захваченные транзакцией, автоматически освобождаются, когда транзакция фиксируется или откатывается, или когда фоновый процесс PMON отменяет транзакцию. Кроме того, другие ресурсы системы (такие как сегменты отката) освобождаются для использования другими транзакциями.
Точки сохранения позволяют устанавливать маркеры внутри транзакции таким образом, чтобы имелась возможность отмены только части работы, проделанной в транзакции. Целесообразно использовать точки сохранения в длинных и сложных транзакциях, чтобы обеспечить возможность отмены изменения для определенных операторов. Однако это обусловливает дополнительные затраты ресурсов системы — оператор выполняет работу, а изменения затем отменяются; обычно усовершенствование в логике обработки могут оказаться более оптимальным решением. Oracle освобождает блокировки, захваченные отмененными операторами.
Целостность данных связана с определением правил проверки достоверности данных гарантирующих, что недействительные данные не попадут в ваши таблицы. Oracle позволяет определять и хранить эти правила для объектов базы данных, которых они касаются, таким образом, чтобы кодировать их только однажды. При этом они активируются всякий раз, когда какой-либо вид изменения проводится в таблице, независимо от того, какая программа выполняет вставки, модификации или удаления. Этот контроль осуществляется в форме ограничений целостности и триггеров базы данных.
Ограничения целостности устанавливают бизнес-правила на уровне базы данных, определяя набор проверок для таблиц системы, Эти проверки автоматически выполняются всякий раз, когда вызываются оператор вставки, модификации или удаления данных в таблице. Если какие-либо ограничения нарушены, операторы отменяются. Другие операторы транзакции остаются в отложенном состоянии и могут фиксироваться или отменяться согласно логике приложения.
2.3.4 Привилегии системного уровня
Каждый пользователь Oracle, определяемый в базе данных, может иметь одну или несколько из более чем 80 привилегий системного уровня. Эти привилегии очень тонко управляют правами выполнения команд SQL. Администратор базы данных назначает системные привилегии или непосредственно пользовательским учетным разделам Oracle, или ролям. Роли затем назначаются учетным разделам Oracle.
Например, прежде чем создать триггер для таблицы (даже если вы владелец таблицы как пользователь Oracle), нужно иметь системную привилегию, называемую CREATE TRIGGER, назначенную вашему учетному разделу пользователя Oracle, или роли, присвоенной учетному разделу.
Привилегия CREATE SESSION — другая часто используемая привилегия системного уровня. Чтобы выполнить соединение с базой данных, учетный раздел Oracle должен иметь привилегию системного уровня CREATE SESSION.
Привилегии объектного уровня . Привилегии объектного уровня обеспечивают возможность выполнить определенный тип действия (выбрать, вставить, модифицировать, удалить и т.д.) с указанным объектом. Владелец объекта имеет полный контроль над объектом и может выполнять любые действия с ним; он не обязан иметь привилегии объектного уровня. Фактически владелец объекта — пользователь Oracle, который может предоставлять привилегии объектного уровня другим пользователям.
Например, если пользователь, который владеет таблицей, желает, чтобы другой пользователя вставлял и выбирал строки из его таблицы (но не модифицировал или удалял), он предоставляет другому пользователю привилегии (объектного уровня) отбора и вставки для этой таблицы. Вы можете предоставлять привилегии объектного уровня непосредственно пользователям или ролям, которые затем назначаются учетным разделам пользователей Oracle.
Привилегии выдаются пользователям и ролям командой GRANT и отбираются командой REVOKE. Все привелегии можно разделить на системные и объектные. Системные привилегии относятся ко всему классу объектов, а объектные относятся к заданным объектам.
Мой блог
По умолчанию Oracle выводит даты в формате DD-MON-YY, где YY — две последние цифры года:
select sysdate from dual;
При вставке в таблицу значений типа date, по умолчанию можно использовать литерал в формате
DD-MON-YYYY
(две цифры номера дня, три буквы месяца и четыре цифры года)
insert into t1 (d) values (’28-APR-1971′);
или использовать ключевое слово DATE для передачи в базу литерала типа data в формате ANSI
YYYY-MM-DD
(четыре цифры года, две цифры месяца, две цифры номера дня)
insert into t1 (d) values ( DATE ‘1971-04-28’);
Конвертация даты в строку:
select to_char(sysdate) from dual;
select to_char(sysdate, ‘DD‘) from dual; — день
select to_char(sysdate, ‘MONTH‘) from dual; —месяц
select to_char(sysdate, ‘YYYY‘) from dual; — год
select to_char(sysdate, ‘HH24:MI:SS‘) from dual; — часы, минуты, секунды
select to_char(sysdate, ‘DD MONTH YYYY HH24:MI:SS‘) from dual; — комбинация параметров формата
02 ИЮЛЬ 2014 17:00:51
select to_char(sysdate, ‘CC‘) from dual; — двузначное столетие (век)
select to_char(sysdate — 1000000, ‘SCC‘) from dual; — двузначное столетие (век), со знаком минус до нашей эры
select to_char(sysdate, ‘Q‘) from dual; — однозначный квартал года
Немного о стандарте ISO.
В стандарте ISO, год, относящийся к номеру недели ISO, может отличаться от календарного года.
1 января 1988 года попадает на 53-ю неделю ISO для 1987 года.
Неделя всегда начинается с понедельника и заканчивается воскресеньем.
Как связан год с номером недели по стандарту ISO:
Если 1 января падает на пятницу, субботу или воскресенье, то неделя, включающая 1 января,
считается последней неделей предыдущего года, потому что большинство дней этой недели
принадлежат предыдущему году.
Если 1 января падает на понедельник, вторник, среду или четверг, то эта неделя считается
первой неделей нового года, потому что большинство дней этой недели принадлежат новому году.
1 января 1991 падает на вторник, поэтому неделя с понедельника, 31 декабря 1990 по воскресенье, 6 января 1991 считается неделей 1.
Чтобы получить номер недели ISO, используйте маску формата ‘IW‘ для номера недели и одну из масок вида ‘IY‘ для года.
select to_char( DATE ‘1991-01-01’, ‘YYYY WW‘) from dual; — в обычном календарном формате
select to_char( DATE ‘1991-01-01’, ‘IYYY IW‘) from dual; — в формате по ISO
в данном случае результаты совпадают.
Попробуем с другой датой:
select to_char( DATE ‘1988-01-01’, ‘YYYY WW‘) from dual; — в обычном календарном формате
select to_char( DATE ‘1988-01-01’, ‘IYYY WW‘) from dual; — год в формате ISO
select to_char( DATE ‘1988-01-01’, ‘IYYY IW’) from dual; — год и номер недели в формате ISO
Как видим результаты разные.
При вставке в таблицу даты, рекомендуется указывать все четыре цифры года.
Если указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет интерпретировать в зависимости от того, какой формат был использован при вводе.
Если использовать формат YY, то в качестве столетия будет использовано текущее столетие,
которое в настоящее время установлено на сервере.
select
to_char(to_date(’28-04-14′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’)
from dual;
28-04-2014 28-04-2077
Неважно какой год мы указали, столетие всегда будет текущее (т.е. 20)
Если использовать формат YYYY но при этом указать только две последние цифры года
то в качестве столетия Oracle подставит нули (т.е. 00)
select
to_char(to_date(’28-04-14′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’)
from dual;
28-04-0014 28-04-0077
Если использовать формат RR и указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет вычислять по следующим правилам:
Если указанный год находится в интервале от 00 до 49 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 50 до 99,
то столетие при этом будет увеличено на 1 (текущее столетие + 1).
Если указанный год находится в интервале от 50 до 99 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 00 до 49,
то столетие при этом будет уменьшено на 1 (текущее столетие — 1).
select
to_char(to_date(’28-04-14′, ‘DD-MM-RR’), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RR‘), ‘DD-MM-YYYY’)
from dual;
28-04-2014 28-04-1977
Вобщем запомнить легко, если указанный год, больше текущего диапазона, значит столетие уменьшаем
и наоборот если указанный год, меньше текущего диапазона, значит столетие увеличиваем.
Интересно, а что будет если использовать формат RRRR, но при этом указать только две последние цифры года:
select
to_char(to_date(’28-04-14′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’)
from dual;
28-04-2014 28-04-1977
В качестве столетия Oracle не подставил нули, вывод аналогичен формату RR.
Для выделения первой цифры столетия в формате года можно использовать запятую:
select to_char(sysdate, ‘Y,YYY‘) from dual; — год с разделителем
Допустимые форматы года:
select to_char(sysdate, ‘YYYY IYYY RRRR SYYYY Y,YYY YYY IYY YY IY RR Y I’) from dual; — год в различных форматах
2014 2014 2014 2014 2 014 014 014 14 14 14 4 4
А также год прописью:
select to_char(sysdate, ‘YEAR‘) from dual; — в верхнем регистре
select to_char(sysdate, ‘Year‘) from dual; — каждое слово с большой буквы
Форматы месяца:
select to_char(sysdate, ‘MM‘) from dual; — двузначный номер месяца
select to_char(sysdate, ‘MONTH‘) from dual; — полное название в верхнем регистре
select to_char(sysdate, ‘Month‘) from dual; — полное название с большой буквы
select to_char(sysdate, ‘MON‘) from dual; — три первые буквы в верхнем регистре
select to_char(sysdate, ‘Mon‘) from dual; — три первые буквы с большой буквы
select to_char(sysdate, ‘RM‘) from dual; — римскими цифрами
Форматы недели:
select to_char(sysdate, ‘WW‘) from dual; — двузначный номер недели года
select to_char(sysdate, ‘IW‘) from dual; — двузначный номер недели года по ISO
select to_char(sysdate, ‘W‘) from dual; — однозначный номер недели месяца
Форматы дня:
select to_char(sysdate, ‘DDD‘) from dual; — трехзначный номер дня года
select to_char(sysdate, ‘DD‘) from dual; — двузначный номер дня месяца
select to_char(sysdate, ‘D‘) from dual; — однозначный номер дня недели
select to_char(sysdate, ‘DAY‘) from dual; — полное название дня в верхнем регистре
select to_char(sysdate, ‘Day‘) from dual; — полное название дня с заглавной буквы
select to_char(sysdate, ‘DY‘) from dual; — первые две буквы названия в верхнем регистре
select to_char(sysdate, ‘Dy‘) from dual; — первые две буквы названия с заглавной буквы
select to_char(sysdate, ‘J‘) from dual; — Юлианский день — число дней, прошедшее с 1 января 4713 г. до нашей эры
Формат часов:
select to_char(sysdate, ‘HH24‘) from dual; — двузначный номер часа в 24 часовом формате
select to_char(sysdate, ‘HH24 PM‘) from dual; — с суффиксом
select to_char(sysdate, ‘HH‘) from dual; — двузначный номер часа в 12 часовом формате
select to_char(sysdate, ‘HH PM‘) from dual; — с суффиксом
select to_char(sysdate, ‘HH A.M.‘) from dual; — с суффиксом
Форматы минут:
select to_char(sysdate, ‘MI‘) from dual; — двузначное количество минут
Форматы секунд:
select to_char(sysdate, ‘SS‘) from dual; — двузначное количество секунд
Существует тип TIMESTAMP, который может хранить дробную часть секунд.
Необязательную точность представления секунд можно определить параметром FF[1..9]
Значение этого параметра по умолчанию равно 6 (справа от десятичной точки секунд можно поместить до 6 цифр)
При попытке поместить большее количество цифр в дробную часть секунд, значение дробной части будет округлено.
SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF‘) FROM dual; — шесть цифр после десятичной точки (по умолчанию)
2014-10-18 08:55.42.050000
SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF3‘) FROM dual; — три цифры после десятичной точки
2014-10-18 08:56.23.606
SELECT TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI.SS.FF9‘) FROM dual; — девять цифр после десятичной точки
2014-10-18 08:56.55.526000000
select to_char(sysdate, ‘SSSSS‘) from dual; — число секунд отсчитываемое от полуночи
В отчетах statspack применяются следующие обозначения долей секунд:
second (s)
centisecond (cs) — 100th of a second
millisecond (ms) — 1,000th of a second
microsecond (us) — 1,000,000th of a second
Символы, позволяющие разделять аспекты дат и времени.
— / , . ; : или любой текст в кавычках «текст»
SELECT TO_CHAR(SYSDATE, ‘YYYY—MM—DD HH24:MI.SS’) FROM dual;
2014—10—18 14:30.43
SELECT TO_CHAR(SYSDATE, ‘YYYY/MM/DD;HH24 «часов» MI «минут» SS «секунд»‘) FROM dual;
2014/10/18;14 часов 31 минут 18 секунд
AM или PM (A.M. или P.M.)
12-часовой формат исчисления времени предполагает разбиение 24 часов, составляющих сутки,
на два 12-часовых интервала, обозначаемых a.m. (лат. ante meridiem дословно — «до полудня»)
и p.m. (лат. post meridiem дословно — «после полудня»).
00:00 (полночь) 12:00 a.m.* (полночь)
12:00 (полдень) 12:00 p.m.* (полдень)
Проблемы в обозначениях полудня и полуночи:
Несмотря на наличие международного стандарта ISO 8601, 12 часов ночи и 12 часов дня обозначается в разных
странах по-разному. Это связано с тем, что в латинских словосочетаниях лат. ante meridiem и
лат. post meridiem слово meridiem означает буквально «середина дня» или «полдень»,
и нет однозначности между обозначением полудня как «12 a.m.» («12 ante meridiem»,
или «12 часов до середины дня») или как «12 p.m.» («12 post meridiem», или «12 часов после середины дня»).
С другой стороны, полночь также можно логично назвать «12 p.m.» (12 post meridiem,
12 часов после предыдущей середины дня) или «12 a.m.» (12 ante meridiem, 12 часов до следующей середины дня).
National Maritime Museum в Гринвиче рекомендует обозначать эти временные моменты как «12 дня» и «12 ночи».
То же советует и The American Heritage Dictionary of the English Language. Многие руководства по стилю,
принятые в США, предлагают «полночь» заменять на «11:59 p.m.», если мы хотим обозначить конец дня,
и «12:01 a.m.», если мы хотим обозначить начало следующего дня.
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI.SS AM‘) FROM dual;
2014-10-18 14:53.58 PM
AD или BC (A.D. или B.C.)
BC — до нашей эры
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI.SS BC‘) FROM dual;
2014-10-18 15:00.25 Н.З.
TH — суффикс для чисел
SELECT TO_CHAR(SYSDATE, ‘DDTH‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘ddTH‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘mmTH‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘YYYYTH‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘yyyyTH-MMTH-DDTH HH24TH:miTH.SSTH BC’) FROM dual;
2014th-10TH-18TH 17TH:56th.52ND Н.З.
SP — числовые значения записываются словами
SELECT TO_CHAR(SYSDATE, ‘DDSP‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘ddSP‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘mmTHSP‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘mmSP‘) FROM dual;
SELECT TO_CHAR(SYSDATE, ‘YYYYTHSP‘) FROM dual;
TWO THOUSAND FOURTEENTH
SELECT TO_CHAR(SYSDATE, ‘YYYYSP‘) FROM dual;
TWO THOUSAND FOURTEEN
EE — Полное название эпохи для японского календаря, календаря КНР и буддийского календаря.
E — Сокращенное название эпохи
select TO_DATE(‘H19-01-01′ , ‘EYY-MM-DD’ , ‘NLS_CALENDAR=»JAPANESE IMPERIAL»’) e_date
from dual;
select TO_DATE(‘平成19-01-01′ , ‘EEYY-MM-DD’ , ‘NLS_CALENDAR=»JAPANESE IMPERIAL»’) ee_date
from dual;
Часовые пояса:
В Oracle с версии 9i появилась возможность использовать различные часовые пояса.
Часовой пояс — это смещение от времени по Гринвичу(GMT).
Но теперь оно называется Всемирное скоординированное время(UTC).
Часовой пояс определяется либо как смещение относительно UTC, либо по имени региона (названию часового пояса).
Получить названия часовых поясов можно так:
select * from v$timezone_names;
Africa/Abidjan LMT
Africa/Abidjan GMT
Africa/Accra LMT
Africa/Accra GMT
Africa/Accra GHST
Africa/Addis_Ababa LMT
Africa/Addis_Ababa ADMT
Africa/Addis_Ababa EAT
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
.
При определении смещения используется формат HH:MI с префиксом в виде знака + или —
+/- HH:MI
Посмотрим какое смещение относительно UTC установлено в нашей БД:
select dbtimezone from dual;
(меняется параметром time_zone в spfile.ora)
Часовой пояс сеанса можно определить так:
select sessiontimezone from dual;
Europe/Moscow
Его легко можно поменять на время сеанса:
alter session set time_zone = ‘PST’;
select sessiontimezone from dual;
Стандартное Тихоокеанское время PST отстает от UTC на восемь часов.
Восточное стандартное время EST отстает от UTC на пять часов.
Текущую дату для сеанса в локальном часовом поясе можно определить так:
select current_date from dual;
select to_char(current_date, ‘YYYY-MM-DD HH24:MI.SS’ ) from dual;
sysdate() — возвращает значение даты и времени, установленных в ОС компьютера, на котором размещена БД.
current_date() — возвращает значение даты и времени для часового пояса вашего сеанса.
Для любого часового пояса можно найти величину смещения с помощью функции tz_offset().
select tz_offset(‘PST’) from dual;
select tz_offset(‘Europe/Moscow’) from dual;
TZH — время в часах часового пояса
TZM — минуты часового пояса
TZR — регион часового пояса
TZD — часовой пояс с информацией о переходе на летнее время
Tип TIMESTAMP, в отличие от типа DATE, может хранить информацию о часовых поясах.
select to_char(SYSTIMESTAMP, ‘TZH:TZM‘) from dual;
select to_char(SYSTIMESTAMP, ‘TZR‘) from dual;
select to_char(SYSTIMESTAMP, ‘TZD‘) from dual;
select to_char(SYSTIMESTAMP, ‘HH:MI:SS.FFTZH:TZM‘) from dual;
select to_char(SYSTIMESTAMP, ‘YYYY-MM-DD HH:MI:SS TZH:TZM‘) from dual;
2014-10-18 10:52:19 +04:00
select to_char(SYSTIMESTAMP, ‘YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD‘) from dual;
2014-10-18 10:52:31.802000 PM +04:00 +04:00
Чтобы конвертировать дату-время из одного часового пояса к другому,
можно воспользоваться функцией NEW_TIME().
select to_char( new_time( to_date( ’28-04-1971 10:30′ , ‘DD-MM-YYYY HH24:MI’), ‘PST‘ , ‘EST‘), ‘DD-MM-YYYY HH24:MI’)
from dual;
Конвертация строки в тип дата-время.
Функцию TO_DATE(x [, формат])
можно использовать для конвертирования строки x в тип дата-время.
Если строка формата опущена, то дата должна быть представлена в формате по умолчанию:
DD-MON-YYYY или DD-MON-YY
(Вообще формат даты по умолчанию определяет параметр БД NLS_DATE_FORMAT)
alter session set NLS_DATE_LANGUAGE = ‘AMERICAN’ ;
alter session set NLS_DATE_FORMAT = ‘SYYYY-MM-DD’ ;
alter session set NLS_TIMESTAMP_FORMAT = ‘SYYYY-MM-DD HH24:MI:SS’ ;
alter session set NLS_TIMESTAMP_TZ_FORMAT = ‘SYYYY-MM-DD HH24:MI:SS TZH:TZM’ ;
alter session set NLS_DATE_LANGUAGE = ‘AMERICAN’;
alter session set NLS_DATE_FORMAT = ‘DD-MON-RRRR’;
select to_date(’28-APR-1971′), to_date(’28-APR-71′) from dual;
Можно и явно задать формат
select to_date(‘April 28, 1971’ , ‘MONTH DD, YYYY‘) from dual;
select to_date(’28-APR-1971 18:30:55′ , ‘DD-MON-YYYY HH24:MI:SS‘) from dual;
Совместное использование to_date() и to_char()
select to_char(to_date(’28-APR-1971 18:30:55′ , ‘DD-MON-YYYY HH24:MI:SS’) , ‘HH24:MI:SS’) from dual;
Формат даты по умолчанию, можно использовать и при вставке строк в таблицу:
alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY‘;
insert into t1 ( id, bday ) values (1, ‘28-APR-1971‘ );
NLS — параметры:
National language_support (До Oracle9i)
Globalisation support (Начиная с Oracle9i)
Кодировка устанавливается только в переменных окружения!
Язык — RUSSIAN, AMERICAN
1) Язык вывода сообщений об ошибках
2) на каком языке выводить названия месяцев и дней недели
(Если явно не задан параметр NLS_DATE_LANGUAGE)
SELECT * FROM v$nls_valid_values
WHERE parameter = ‘LANGUAGE’
ORDER BY value
CIS — СНГ
1. первый день недели
2. символ национальной валюты
(Если явно не задан параметр NLS_CURRENCY)
3. Десятичный и групповой разделители чисел
SELECT * FROM v$nls_valid_values
WHERE parameter = ‘TERRITORY’
ORDER BY value
SELECT * FROM v$nls_valid_values
WHERE parameter = ‘CHARACTERSET’
— Русский язык, Кириллица
AND (value LIKE ‘CL%’
OR
value LIKE ‘RU%’)
ORDER BY value
WE8ISO8859P1 — Западная Европа
NLS_LANG = AMERICAN_CIS.CL8MSWIN1251
NLS_LANG = AMERICAN_AMERICA.RU8PC866
NLS_LANG = RUSSIAN_CIS.CL8ISO8859P1
Какие есть параметры NLS?
SELECT * FROM nls_session_parameters
PARAMETER VALUE
================ ==========
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=CIS
— Символ нац. валюты
NLS_CURRENCY=’р.’
— Символ нац. валюты по стандарту ISO
NLS_ISO_CURRENCY=’CIS’
— Десятичный разделитель и разделитель групп
NLS_NUMERIC_CHARACTERS=’, ‘
— Календарь
NLS_CALENDAR=GREGORIAN
— Формат ввода и вывода даты по-умолчанию
NLS_DATE_FORMAT=’DD.MM.RR’
— Язык для вывода названий месяцев и дней недели
NLS_DATE_LANGUAGE=’AMERICAN’
— Тип Сортировки
NLS_SORT=BINARY
— . (нет описания)
NLS_TIME_FORMAT=’HH24:MI:SSXFF’
— Формат ввода и вывода даты типа TIMESTAMP по-умолчанию
NLS_TIMESTAMP_FORMAT=’DD.MM.RR HH24:MI:SSXFF’
— . (нет описания)
NLS_TIME_TZ_FORMAT=’HH24:MI:SSXFF TZR’
— Формат ввода и вывода даты типа TIMESTAMP с временнОй зоной по-умолчанию
NLS_TIMESTAMP_TZ_FORMAT=’DD.MM.RR HH24:MI:SSXFF TZR’
— Замещает символ нац. валюты, установленный по умолчанию параметром NLS_TERRITORY
NLS_DUAL_CURRENCY=’р.’
— Как сравнивать строки BINARY или ASCII (по правилам нац. алфавита)
NLS_COMP=BINARY
— CHAR по умолчанию в байтах или в символах
NLS_LENGTH_SEMANTICS=BYTE
— NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is
— data loss during an implicit OR explicit CHARACTER TYPE conversion.
— The DEFAULT value results IN no error being reported.
NLS_NCHAR_CONV_EXCP=FALSE
Как можно устанавливать значения параметров NLS?
1. В системном реестре Windows
2. Установить переменные окружения
Для Windows (в bat-файле)
SET NLS_DATE_LANGUAGE=RUSSIAN
SET NLS_LANG=AMERICAN_CIS.CL8MSWIN1251
sqlplus .
3. ALTER SESSION SET
NLS_DATE_LANGUAGE=RUSSIAN
NLS_DATE_FORMAT=’DD.MM.YYYY’;
SELECT TO_CHAR(SYSDATE, ‘Month day’)
FROM dual
Посмотреть nls-параметры сессии, базы данных и инстанса можно так:
select * from
(select ‘SESSION’ SCOPE,s.* from nls_session_parameters s
union
select ‘DATABASE’ SCOPE,d.* from nls_database_parameters d
union
select ‘INSTANCE’ SCOPE,i.* from nls_instance_parameters i
) a
pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in (‘SESSION’ as «SESSION»,’DATABASE’ as «DATABASE»,’INSTANCE’ as «INSTANCE»));
Функции для работы с типом data.
ADD_MONTHS(data, n)
Позволяет добавить к дате целое количество месяцев (или отнять, если n отрицательное)
SELECT ADD_MONTHS(‘28.04.1971’ , 13) FROM DUAL; — Добавить 13 месяцев
SELECT ADD_MONTHS(‘28.04.1971’ , -12) FROM DUAL; — Отнять 12 месяцев