Modify the Oracle database default time format

Oracle database default time format can only be accurate to the day (DD-MON-RR), in the actual work environment, the development program usually needs to obtain a time value of accurate to seconds, the query data in Oracle to modify the time value can be roughly divided into the following ways:
1. Temporary Modification Time format
The first way is through the TO_CHAR function to get the exact time value
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;
This is the easiest way for DBAs to make changes to the database without changing any parameters, but it's a hassle to use a function conversion every time.
The second way is to modify the time format value of the current session
Alter session set nls_date_format= ' Yyyy-mm-dd HH24:MI:SS ';
The advantage of this approach is that all time values emitted in the current session will be ' Yyyy-mm-dd HH24:MI:SS ' until the current session is closed,
The disadvantage is that when you close the current session, the time value returns to the original format of the database.
After modifying this way, we can view the changes in the time format by querying the database view
orcl@ sys> SELECT * from V$nls_parameters;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------
Nls_language American
Nls_territory AMERICA
Nls_currency $
Nls_iso_currency AMERICA
Nls_numeric_characters.,
Nls_calendar Gregorian
Nls_date_format YYYY:MM:DD HH24:MI:SS
Nls_date_language American
Nls_characterset ZHS16GBK
Nls_sort BINARY
Nls_time_format HH.MI. Ssxff AM
Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM
Nls_time_tz_format HH.MI. Ssxff AM TZR
Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR
Nls_dual_currency $
Nls_nchar_characterset AL16UTF16
Nls_comp BINARY
Nls_length_semantics BYTE
NLS_NCHAR_CONV_EXCP FALSE
The value of Nls_date_format from the original "DD-MON-RR" into "YYYY:MM:DD HH24:MI:SS";
Note: In V$parameter, there are also nls_date_format parameter values, but modifying this parameter value does not cause changes to the database time format.
2. Permanent modification
After testing, the source of the Nls_date_format value in view v$nls_parameters is not the internal setting of the database, but the environment variables from the operating system,
So, if we want to make a permanent change, just add this parameter value to the system environment variable:
Add Export nls_date_format= ' YYYY:MM:DD HH24:MI:SS ' to the. bash_profile file
The source ~/.bash_profile is then executed, enabling the environment variable to take effect.
When we enter the database query system time again, we can get the time format value we want.