Oracle date usage considerations

Oracle date types include date type and timestamp type

 

The date format supports the second level, the specific format can have the parameter init file: nls_date_format is set to the YYYY-MM-DD parameter to set, through the parameter settings can set different levels, it can be set to the session level and database level. If it is set to the session level, only the logged-on session can be seen. Generally, it is best to set it to the database level; you can also convert the data by using the to_char (date, 'format'), to_date ('datestr', 'format, generally, set 'yyyy-mm-dd' in the application system.

 

The timestamp type is related to the time zone settings. The returned seconds are from the system, and the returned date and time are converted based on the time zone. This type is generally used to indicate more accurate time records for system operations, for example, the transaction time in the bank transaction system.

 

In database design, if you want to create an index for the date type, you can usually create a function index by converting the to_char () function.

 

The system time sysdate is accurate to the second by default. If you need to compare the time field in the table with sysdate in the system, and the time field in the table is in the 'yyyy-mm-dd' format. Eg: the format of a date field submitdate is 'yyyy-mm-dd', submitdate = sysdate. This condition will lose data because sysdate is accurate to the second level, it is greater than submitdate and can be compared after conversion, for example, submitdate = to_date (to_char (sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd ') or convert it to a string to compare to_char (submitdate, 'yyyy-mm-dd') = to_char (sysdate, 'yyyy-mm-dd ').