Trending September 2023 # How Timestamp Data Type Works In Oracle? # Suggested October 2023 # Top 11 Popular

You are reading the article How Timestamp Data Type Works In Oracle? updated in September 2023 on the website Lifecanntwaitvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How Timestamp Data Type Works In Oracle?

Introduction to Oracle TIMESTAMP

Oracle TIMESTAMP is a data type which stores in the format of century, Year, Month, Date, Hour, Minute, Second. TIMESTAMP is an extension of DATE data type. TIMESTAMP data type is an enhancement of DATE data type and provides more intelligence.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Points of Concentration:

Oracle introduced TIMESTAMP data type in 9i version.

It allows to store date time with fraction of seconds.

By default format of TIMESTAMP is ‘YYYY-MM-DD HH24:MI:SS.FF’.

By default format of TIMESTAMP WITH TIME ZONE is ‘YYYY-MM-DD HH24: MI: chúng tôi TZH: TZM’. Here TZH is Time Zone Hour and TZM is Time Zone Minute.

The digits / length of Fraction of Seconds can be specified from 0 – 9 digits. The default length is 6 digit.

Also Oracle introduced TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE which allows us to store date / time with time zone.

Syntax:

1. Without Time Zone

TIMESTAMP[{Fractional – Seconds - Precision}]

2. With Time Zone

TIMESTAMP [{Fractional – Seconds - Precision}] WITH TIME ZONE

3. With Local Time Zone

Description:

Fractional – Seconds – Precision: It optionally specifies the number of digits in the fractional part of the Seconds to be considered in date time field.

Default precision length is 6 digit.

WITH TIME ZONE: It allows column to store Time Zone. Time Zone cane be entered manually, by default it takes system time zone.

WITH LOCAL TIME ZONE: This also allows to store time zone. It stores data in database is normalized to the database time zone but the time zone offset is not stored as part of the column data. Oracle returns that data in the users’ local session time zone.

How TIMESTAMP Data Type works in Oracle?

As Oracle provides DATE data type to represent date and time values. This data type has ability to store the day, month, year, century, hour, minute and seconds. Oracle introduced a new data type TIMESTAMP which is an extension of DATE data type. TIMESTAMP data type stores the day, month, year, century, hour, minute, seconds and fraction of seconds. It has ability to store Date and time with Time zone.

Example #1

For Date data type.

Code:

SELECT DOJ, TO_CHAR(DOJ,'DD-MM-YY HH: MI: SS’) "Date/Time" FROM Employee;

Output:

As in Employee table DOJ column is DATE data type which stores Date/time date. The above output clearly shows that DOJ column consists DATE and Time value but does not consists fraction of seconds and also Time Zone.

But in the real world many events happen with in a second of each other. DATE data type does not provide that ability to store that information. So Oracle introduced TIMESTAMP data type to solve this real time problem.

Example #2

For TIMESTAMP data type.

Code:

desc error;

Output:

The above image showing the description of Error table where TIME column is TIMESTAMP data type and DTIME column is TIMESTAMP WITH LOCAL TIME ZONE data type.

Code:

SELECT Time FROM Error;

Output:

In the above example using TIME column from Error table which is TIMESTAMP data type. The Time column stores date time details with fraction of seconds with four precision digit (Because it specified during table creation). But there is no time zone indication because Time column specified only TIMESTAMP data type. So it stores date time without time zone.

Output:

Example #3

For TIMESTAMP data type WITH TIME ZONE.

Code:

desc msg;

Output:

Above image is the description of Msg table. In the table DTIME column Declared TIMESTAMP data type WITH TIME ZONE. It means this column stores date time with time zone information. By default it stores system time zone but it accepts manually as well.

Code:

Output:

In the above output +05:00 & -08:00 denoting respective time zones. So if we don’t specify Time Zone it takes system time zone else it stores specified Time zone.

Code:

insert into Msg values(3,TIMESTAMP ‘2003-01-01 2:00:00’);

Output:

In the above insert statement Time zone is not specified.

Code:

insert into Msg values(4,TIMESTAMP ‘2003-01-01 2:00:00 -08:00’);

Output:

In the above insert statement Time zone is specified -08:00. So DTIME column stores system time zone if Time zone is not specified.

Code:

select * from msg;

Output:

Tips

1. Oracle EXTRACT function is used to extract components of TIMESTAMP.

Syntax:

Components are year, month, day, hour, minute, and second, timezone_hour.

Example:

Code:

FROM Msg

Output:

2. TIMESTAMP default format can be changed using NLS_TIMESTAMP_TZ_FORMAT but the change will reflect only for the current session.

Syntax:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT=’Format';

3. Time Zone can also be altered in Oracle Database.

Syntax:

For Database Level:

ALTER DATABASE SET time_zone = ‘Format';

For Session level:

ALTER SESSION SET time_zone = ‘Format’

4. FROM_TZ is used to convert TIMESTAMP to a different Time Zone.

Example:

SELECT FROM_TZ (TIMESTAMP, '2010-01-10 09:10:00', '4:00') FROM dual;

5. CURRENT_TIMESTAMP, it returns the current date time with the session time zone.

Conclusion

Oracle TIMESTAMP data type was introduced to solve real time problems especially when events happen more than once with in a second. In that scenario fraction of second plays a vital role to track those events. Time zone can be tracked of the event using TIMESTAMP. TIME STAMP data type can be a more useful if events happen more frequent.