Shulou(Shulou.com)05/31 Report--

What are the date types in ORACLE? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

There are 4 date types in the database:

1. DATE: is the earliest and most widely used date type. Examples are as follows.

DATE is the oldest and most widely used data type. Though the name is "DATE", it stores also information about the time. Internally, DATE stores year, month, day, hour, minute and second as explicit values. To get the current timestamp as an instance of DATE, use the SYSDATE SQL function.

SQL > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

SQL > select sysdate, dump (sysdate) as date_bytes from dual

SYSDATE DATE_BYTES

2017-11-23 23:41:08 Typ=13 Len=8: 225 Percience 7, 11, 23 23, 21, 41, 8, 0

2. TIMESTAMP: extends the DATA type, including minutes and seconds and time zone.

TIMESTAMP extends DATE by fractional seconds. Internally, time zone information is also contained, but in order to work with time zones, one of the other two data types, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE must be used. To get the current timestamp as a TIMESTAMP instance, use the LOCALTIMESTAMP SQL function. As the DUMP result below shows, TIMESTAMP stores year, month, day, hour, minute and second similar to DATE. The other bytes are there for fractional seconds and time zone information. TIMESTAMP instances consume more space on disk than DATE instances, which is natural-they contain more information.

SQL > alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6'

SQL > select localtimestamp, dump (localtimestamp) ts_bytes from dual

LOCALTIMESTAMP TS_BYTES

-

2017-11-24 08 Typ=187 Len=20 41.434175 Personality 41.434175 41.434175 Personality: 225pjorie 7pjre 11pyrmt 24jre 8pr 41g 0lle 0lle 24pr 252pr 224pr 251g 0je 3jingle 01jue 1271jue 0jue 0

3. SYSTIMESTAMP displays the time zone, SYSTIMESTAMP displays the current database time zone, and CURRENT_TIMESTAMP displays the current session time zone

TIMESTAMP WITH TIME ZONE allows to explicitly work with time zone information. A time zone can be used to create a TIMESTAMP WITH TIME ZONE instance and it is explicitly contained in the output (use the right format mask). When the output of a TIMESTAMP WITH TIME ZONE value does not contain a time zone, you have incomplete data.

Oracle does not implicitly convert instances of this data type between time zones; this can be done explicitly with the AT TIME ZONE clause. To get the current time as a TIMESTAMP WITH TIME ZONE instance, we can use two functions: SYSTIMESTAMP returns the current time in the database time zone and CURRENT_TIMESTAMP returns it in the current session time zone. The following example uses SYSTIMESTAMP.

SQL > alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZR'

SQL > select systimestamp as ts_bytes from dual

SYSTIMESTAMP

-

2017-11-23 23 5715 04.609608-08:00

4. Conversion between local time zone and database time zone. TIMESTAMP implicitly uses the local time zone, session time zone insertion by default, and database time zone by default when storing.

TIMESTAMP WITH LOCAL TIME ZONE does not expose the time zone information (to the application, it looks like a TIMESTAMP), but it uses tome zones implicitly: An instance is created without time zone information, the session time zone is assumed. Stored instances are normalized to the database time zone. When the data is retrieved, users see it (again) in the session time zone.

The following example illustrates how TIMESTAMP WITH LOCAL TIME ZONE works: First, a table is created. Then the current time is retrieved and stored into the table (SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE, but this is automatically converted to TIMESTAMP WITH LOCAL TIME ZONE). When the table is then being selected, the returned data changes with the session time zone.

SQL > create table mytimestamps (ts timestamp with local time zone)

SQL > insert into mytimestamps values (systimestamp)

SQL > alter session set time_zone='Europe/Berlin'

SQL > select * from mytimestamps

TS

2017-11-24 12 5515 39.761283

SQL > alter session set time_zone='EST'

SQL > select * from mytimestamps

TS

2017-11-24 06Rose 55Rose 39.761283

Format Masks

Whether we work within an application or a tool like SQL Plus or SQL Developer; whenever we output a DATE or TIMESTAMP instance, we're converting it to VARCHAR2. We can do this explicitly using TO_CHAR or Oracle will do it implcitly. And now NLS format masks kick in-the format mask decides how a DATE is converted to a VARCHAR2 and back. The are defaults for NLS format masks at the database level, we can set them at the session level or we can pass a format mask to the individual TO_CHAR call.

After reading the above, have you mastered the methods of date types in ORACLE? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.