Chapter One main SQL statements
1. Query the database system time, often in the default format of the server display (depending on the database character set);
Note: Dual is a virtual table in the database and is part of the Administrator sys user, but all users are able to access it. It has no practical meaning, and only acts as a structure of the SELECT statement (using Select to fetch system information, temporary results, etc., to dual as the statement structure).
[Email protected]>select sysdate from dual;
Sysdate
---------
18-jun-14
1.1 Change the display format of the system time, (session indicates that this change is valid for the current session)
[Email protected]>alter session Set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
Session altered.
[Email protected]>select sysdate from dual;
Sysdate
-------------------
2014-06-18 16:11:37
1.2 Add and subtract the system time.
1.2.1 Plus minus a number. Indicates how many days to add to the given time;
[Email protected]>select sysdate from dual;
Sysdate
-------------------
2014-06-18 16:11:37
[Email protected]>select sysdate+2,sysdate-2 from dual; (2 days, 2 days ago)
Sysdate+2 SYSDATE-2
------------------- -------------------
2014-06-20 16:13:58 2014-06-16 16:13:58
1.2.2 How many hours, how many minutes, how many seconds. (2 hours, 2 minutes, 2 seconds)
[Email protected]>select sysdate,sysdate+2/24,sysdate+2/24/60,sysdate+2/24/60/60 from dual;
Sysdate sysdate+2/24 sysdate+2/24/60 SYSDATE+2/24/60/60
------------------- ------------------- ------------------- -------------------
2014-06-18 16:15:50 2014-06-18 18:15:50 2014-06-18 16:17:50 2014-06-18 16:15:52
Note: The above time format changes. Valid for the current session only;
The result is only temporary data to display, and does not change the data values in the table
SQL Series (Basic)-chapter I about sysdate
