Date and Time storage and processing

Date and Time storage and processing
This section summarizes the knowledge about date and time that are frequently used in databases.
Because the character set has an effect on the date and time, this is the first thing we need to pay attention to, which is also briefly mentioned in the previous article (http://blog.itpub.net/29876893/viewspace-1627370)
SQL> select * from nls_session_parameters where rownum <4;
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
At this time, the character set of the session is simplified chinese. To do the experiment, change it to English:

SQL> alter session set NLS_LANGUAGE = AMERICAN;
Session altered.


SQL> select * from nls_session_parameters where rownum <4;
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥

If you have read the previous article, you already know that Language: affects oracle messages, validation, and date naming.


SQL> select sysdate from dual;

SYSDATE
------------------
09-MAY-15
By default, the date stored in the database is: DD-MON-YYYY, And the read is DD-MON-YY by default
We can use the keyword DATE to insert a DATE to the database:
SQL> create table t (t date );
Table created.

SQL> insert into t values ('09-MAY-15 ');
1 row created.
SQL> insert into t values (DATE '2017-8-25 ');
1 row created.
SQL> insert into t values (DATE '2017-08-20 ');
1 row created.


SQL> select * from t;
T
------------------
09-MAY-15
25-AUG-94
20-AUG-94

But with the DATE keyword, the DATE must use ANSI standard DATE Format: YYYY-MM-DD


We can use the to_char (x [, format]) function to convert x to a string and use the format provided later. Below we can convert the date to the desired format:
SQL> select to_char (t, 'dd-MON-YYYY ') FROM t;
TO_CHAR (T, 'dd-MON-YY
--------------------
09-MAY-2015
25-AUG-1994
20-AUG-1994
SQL> select to_char (t, 'mon-DD-YYYY ') FROM t;
TO_CHAR (T, 'mon-DD-YY
--------------------
MAY-09-2015
AUG-25-1994
AUG-20-1994

SQL> select to_char (sysdate, 'dd-MON-YYYY, HH24: MI: ss') from dual;

TO_CHAR (SYSDATE, 'dd-MON-YYYY,
-----------------------------
09-MAY-2015, 23:58:54

The above is just a simple example. For details about time formatting parameters, refer to the document.

To_date (x [, format]) converts x to the DATE type:

SQL> select to_date ('may-6-2015 ') from dual;
Select to_date ('may-6-2015 ') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select to_date ('5-MAY-2015 ') from dual;
TO_DATE ('5-MAY-201
------------------
05-MAY-15
Pay attention to the format DD-MON-YYYY (DD-MON-YY) of the date so the query above has an error
SQL> select to_date ('5-MAY-2015 9:50:45 ', 'dd-MON-YY HH24: MI: ss') FROM DUAL;
TO_DATE ('5-MAY-201
------------------
05-MAY-15

SQL> select to_date ('5-MAY-2015 9:50:45 ', 'dd-MON-YYYY HH24: MI: ss') FROM DUAL;
TO_DATE ('5-MAY-201
------------------
05-MAY-15

SQL> select to_char (to_date ('5-MAY-2015 9:50:45 ', 'dd-MON-YYYY HH24: MI: ss'), 'mon-DD-YYYY HH24: MI: ss ') from dual;
TO_CHAR (TO_DATE ('5-M
--------------------
MAY-05-2015 09:50:45

SQL> select to_char (to_date ('5-MAY-2015 9:50:45 ', 'dd-MM-YYYY HH24: MI: ss'), 'mon-DD-YYYY HH24: MI: ss ') from dual;
TO_CHAR (TO_DATE ('5-MAY-20159:
-----------------------------
MAY-05-2015 09:50:45

SQL> select to_char (to_date ('5-MAY-2015 9:50:45 ', 'dd-MM-YYYY HH24: MI: ss'), 'Mm-DD-YYYY HH24: MI: ss ') from dual;
TO_CHAR (TO_DATE ('5-
-------------------
09:50:45

You can combine to_char and to_date to use time values of different formats.
We specify the date format by setting nls_date_format. If it is set globally, you can also set it in the parameter file:

SQL> show parameter nls_date


NAME TYPE VALUE
-----------------------------------------------------------------------------
Nls_date_format string
Nls_date_language string


SQL> show user;
USER is "HR"
SQL> alter session set nls_date_format = 'mon-DD-YYYY ';
Session altered.

SQL> select sysdate from dual;
SYSDATE
--------------------
MAY-10-2015


Although oracle stores four-digit years, if only two digits are provided, oracle will explain the century based on the format YY or RR:

If the year in the date is in the YY format and only two years are provided, it is not necessary to say that the century of the year is the same as the current century on the data server.

If the date adopts the RR format and only the last two digits are provided, the first two digits of the year are determined by the following two parts:
Provides two-digit year (year specified), and the database server uses the last two digits of the year of the previous day. The rule is as follows: (set the first two digits of the current date of the server to F2, and the first two digits of the specified date to Z2)
Z2 in (00 ~ 49) F2 IN (00 ~ 49) same century F2
Z2 IN (50 ~ 99) F2 IN (00 ~ 49) F2-1
Z2 IN (0 ~ 49) F2 IN (50 ~ 99) F2 + 1
Z2 IN (50 ~ 99) F2 IN (50 ~ 99) F2


SQL> select to_char (to_date ('05-MAY-15', 'dd-MM-RR '), 'dd-MM-YYYY') FROM DUAL;

TO_CHAR (
----------
05-

SQL> select to_char (to_date ('05-MAY-65', 'dd-MM-RR '), 'dd-MM-YYYY') FROM DUAL;


TO_CHAR (
----------
05-05-1965


Let's summarize it here first!