Coskan’s Approach to Oracle

April 16, 2007

Take the control of TIME Part1 (Introduction to Date/Time)

Filed under: Basics — coskan @ 2:46 pm

In todays world data is controlled by time and if you don’t know how to control the time, you can’t have enough control over your data. Thanks Larry Ellison and his employees for building Oracle with powerful time manipulating functions and datatypes which you have to know to administer your Oracle DB. On the next lines I’m going to try to explain Date Time datatypes and functions related them within Oracle as in a nutshell paper. At second part of this issue I will try to cover more complex operations using the functions below over date time values.

First of all I want to tell about date time data types of Oracle then I will explain how to use this data types with Oracle functions.

Oracle Date Time Datatypes


DATE Data type: Stores date and time information ( century, year, month, date, hour, minute, and second). width is fixed (7-bytes).

TIMESTAMP Data type: Extended date datatype. It differs from date by ability to store fractional seconds. Fixed width (7 or 11 bytes).

TIMESTAMP [(fractional_seconds_precision)]

–up to 9 digits precision with default 6

TIMESTAMP WITH TIME ZONE Data type: We can call it timestamp with time zone support. It includes time zone offset (the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time)). Fixed width (13 Bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

–up to 9 digits precision with default 6

TIMESTAMP WITH LOCAL TIME ZONE Data type: Another variant of timestamp that includes a time zone offset but it differs from the timestamp with time zone datatype by not storing the time zone offset as a column data. It gets time zone offset information from user’s local session time zone. (this is useful for displaying data-time info of the client in a two tier application). Fixed width (7 or 11 bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

–up to 9 digits precision with default 6

INTERVAL YEAR TO MONTH Data type: Stores a duration between a period of time by means of year and month. It is usually used for math operations (addition+ subtraction). Fixed width (5 bytes)

INTERVAL YEAR [(year_precision)] TO MONTH

–default precision 2

INTERVAL DAY TO SECONDS Data type: Stores a duration between a period of time by means of days,hours, minutes,seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

–precision for date is up to 9 with default 2

–precision for fractional seconds is up to 9 with default 6

(more…)

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers