April 16, 2007

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

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.

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 ZONEData 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

Oracle DateTime / Interval Literals :
Date Literals: You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function.Gregorian calendar is a must to specify DATE literal.

Oracle Date Time Functions:TRUNC: This is not a date time function but it is very useful with date time format related operations. It is used to truncate year or month or day.TRUNC(date [, fmt ])fmt can be ‘year’,’month’,’day’ by default it truncates the time portion. (for the permitted formats check the manual)

ADD_MONTHS: Adds / subtracts from given date. For last day of the month it refers the result months last day so you have to be careful about this feature. (For example adding 1 month to 31 Jan returns 28 Feb )ADD_MONTHS(date, integer)

DBTIMEZONE: Returns the value of the database time zone. The output can be different depending on how the user specified the database time zone value

hr@ORACOS> select dbtimezone from dual;DBTIME
——
+00:00

EXTRACT: It is used to extract portions fromgiven datetime or interval. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar.

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

hr@ORACOS> ALTER SESSION SET TIME_ZONE = ‘+3:00’;
Session altered.

hr@ORACOS> select localtimestamp from dual;

LOCALTIMESTAMP
—————————————————
14/04/2007 14:16:18,444000

hr@ORACOS> select current_timestamp from dual;

CURRENT_TIMESTAMP
—————————————-
14/04/2007 14:16:20,257000 +03:00

MONTHS_BETWEEN: Returns number of months between two date. If the day portion is same result is always integer but if it is not result is fractioned based on 31 day month.

NEW_TIME : Returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. Result is always date. NLS_DATE_FORMAT parameter must be set for 24 hours time. Function does not accept all the timezone as an input so you must make some mixture with FROM_TZ function. This function is useful for detecting what is the time in timezone2 for a given timezone1 date value

SYSDATE: returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE, and the format depends on the value of the NLS_DATE_FORMAT session parameter.
This function can not be used for check constraints

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE
datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. Refer to the manual for the format types. With this powerful function you can extract portions of timestamp and date datatypes you can get weekday and many more.

TO_CHAR({ datetime | interval } [, fmt [, ‘nlsparam’ ] ])

The 'nlsparam' argument specifies the language in which month and day names and abbreviations are returned. This argument can have this form: ‘NLS_DATE_LANGUAGE = language’

Document says fmt specifies the format of char with default default format of the TIMESTAM
datatype which is determined by the NLS_TIMESTAMP_FORMAT initialization parameter.
But I saw that no mather what you wrote sessions NLS_TIMESTAMP_FORMAT value overrides your fmt and without fmt you have to give the exact NLS_TIMESTAMP_FORMAT of your session.

‘nlsparam’ argument has the same purpose in this function as in the TO_CHAR function for date conversion.

TO_DSINTERVAL: Converts a character datatype to an INTERVAL DAY TO SECOND value.

TO_DSINTERVAL(char [ ‘nlsparam’ ])

char is the character string to be converted.

Valid ‘nlsparam’ you can specify in this function is NLS_NUMERIC_CHARACTERS. This argument can have the form: NLS_NUMERIC_CHARACTERS = “dg” where d and g represent the decimal character and group separator respectively. Neither character can be a space.

TO_YMINTERVAL: Converts a character datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted. Do not use this function for adding month!!!!! (reason is hidden at examples)

Now this is the end of first part of two part series. I hope this manual type info is useful for you. I think second part will be more interesting.“Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you.” Carl Sandburg