Oracle Date, Timestamp & Time Zone Explained

Back in 9i Oracle introduced a new data type called Timestamp.

Timestamp data type adds two new parts to the regular “Date” data type:

  1. Fraction of a second in microseconds ( 6 digits)
  2. Time Zone (shown only on selected functions like systimestamp and current_timestamp and shown on data types such as “Timestamp with time zone”)

Break down of Timestamp: 2/9/2011 10:39:36.802604 AM +03:00
“2/9/2011 10:39:36″ is exactly like older Date date type.
“802604” is a fraction of seconds only shows only in timestamp data types.
“+03:00″ is Riyadh Time Zone.

Why do I need a fraction of a second in my date, isn’t seconds enough?

For most applications, up to second precision is enough, but some applications do require microseconds.

Applications like trading, biding such as eBay, and some physics applications were the operations start and finish in microseconds can benefit from timestamp.

What is Time Zone?

The actual definition you find it here http://en.wikipedia.org/wiki/Time_zone

Time Zone are like EST (Eastern Standard Time) or CST (Central Standard Time), etc… is determined by how far you are from Greenwich Line.

Here in Riyadh we are UTC + 3.00

Do I need Time Zone?

Applications that gets deployed across multiple time zones will benefit from this feature greatly. Worldwide internet applications with one central database  or replicated worldwide databases  are some of application that utilize Time Zone. Prior to this Time Zone feature, we had to create a Date column that hold  database server date/time with database server time zone and an additional Date column that holds date/time with client’s time zone. Lots of manual conversion (not to forget Day Light Saving Time!). A time zone mess in short.

Here is a table that shows Timestamp data type compared to the older Date data type.

Date Equivalent Timestamp Explanation
sysdate systimestamp System clock on the database server.
current_date current_timestamp Will show date/time in client time zone. If client’s time zone changes, then output of these functions will change accordingly. “current_timestamp” will show time zone part
localtimestamp Will show timestamp in client’s time zone. If client’s time zone changes, then output of this function will change accordingly. This function will NOT show time zone as part of the output.

Data Types

Date Timestamp System clock on the database server.
No Equivalent Timestamp with Time Zone Saves timestamp in client time zone. If client time zone changes, the saved value will NOT be converted to new time zone. This data type will show time zone as part of the output.
No Equivalent Timestamp with Local Time Zone Saves timestamp in client time zone. When selected back, the value shown to user will be converted to client’s current time zone. That means, if client’s time zone changes, then value shown to user will be converted to new time zone. This data type will NOT show time zone as part of the output.

 
How can I know my database time zone?
SELECT dbtimezone FROM DUAL;

 
How to know client’s time zone?
SELECT sessiontimezone FROM DUAL;

 
How to change database time zone?
ALTER DATABASE SET TIME_ZONE = ‘+03:00′;

Then restart the database.
 
How to change client time zone?
ALTER DATABASE SET TIME_ZONE=’+03:00′;

OR by using a named region
ALTER DATABASE SET TIME_ZONE=’Asia/Riyadh';
 

This later “alter” will change current_timestamp  output to :
13-FEB-11 11.54.19.985565000 AM ASIA/RIYADH

OR by using environment variables

export ORA_SDTZ=’+05:00′

 
How to initially set up your database time zone?
CREATE DATABASE testdb
. . .
. . .
SET TIME_ZONE=’+03:00′;

 
Where can I find a list of all time zones?
Look up this view: V$TIMEZONE_NAMES

Hope this helps.
 
 
Hazem Ameen
Senior Oracle DBA

About these ads

2 Comments on “Oracle Date, Timestamp & Time Zone Explained”

  1. Mirza says:

    excellent article ..very comprehensive ..thanks for sharing.

  2. J says:

    Hi
    Thanks for the article, but it’s a bit misleading at the top.
    You say “Timestamp data type adds two new parts to the regular “Date” data type:

    1. Fraction of a second in microseconds ( 6 digits)
    2. Time Zone (shown only on selected functions like systimestamp and current_timestamp and shown on data types such as “Timestamp with time zone”)

    Break down of Timestamp: 2/9/2011 10:39:36.802604 AM +03:00″

    =============================

    But timestamp does not add time zone information.

    Timestamp adds fractional seconds to date.
    Timestamp with time zone adds time zone to timestamp.

    Best regards,
    J


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.