Back in 9i Oracle introduced a new data type called Timestamp.
Timestamp data type adds two new parts to the regular “Date” data type:
- Fraction of a second in microseconds ( 6 digits)
- 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
Oracle DBA in KSA 