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

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.

DATE ‘2007-04-13’

Ansi literal does not have minute hour second portion. Examples (Separators can be changed like -, . , / etc. or none ).

TO_DATE(’13/04/2007′,’DD/MM/YYYY’ )

Oracle literal includes minute hour second portion. Examples


TO_DATE(’13/04/2007 21:13:34′,’DD/MM/YYYY HH24:MI:SS’ )


TIMESTAMP ‘1997-01-31 09:26:50.124’


TIMESTAMP ‘1999-04-15 8:00:00 -8:00’

this value is same with below

TIMESTAMP ‘1999-04-15 11:00:00 -5:00’

because 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

also you can put the time zone definition with TZR and a corresponding TZD format element to not having issues with day light saving times.

TIMESTAMP ‘1999-10-29 01:30:00 US/Pacific PDT’

Follow this link for interval literals.

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)

=>usage of trunc

TO_DATE: Converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype

TO_DATE(char [, fmt [, ‘nlsparam’ ] ])

=>usage of to_date

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)

=>usage of add_months

CURRENT_DATE: Returns the current date in the session time zone. You must consider about clients NLS settings when inserting a value.

=>usage of current_date

CURRENT_TIMESTAMP: Returns the current date and time in the session time zone value of datatype TIMESTAMP WITH TIME ZONE.

CURRENT_TIMESTAMP [ (precision) ]

–precision specifies the fractional second precision with default 6

=>usage of current_timestamp

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

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.

FROM { datetime_value_expression| interval_value_expression}

=>usage of extract

FROM_TZ : FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.

FROM_TZ (timestamp_value, time_zone_value)

hr@ORACOS> select to_timestamp(sysdate) from dual;

13/04/2007 18:09:46

hr@ORACOS> select from_tz(to_timestamp(sysdate),’-3:00′) from dual;

13/04/2007 18:10:28 -03:00

LAST_DAY: It returns the last day of the month of the given date. I find this very useful for monthly batches.


hr@ORACOS> select last_day (sysdate) from dual;LAST_DAY(SYSDATE)
30-04-2007 18:15:12

hr@ORACOS> select last_day (add_months(sysdate,-2)) from dual;

28-02-2007 18:15:17

hr@ORACOS> select last_day (sysdate)-sysdate from dual;


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.

Session altered.

hr@ORACOS> select localtimestamp from dual;

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

hr@ORACOS> select current_timestamp from dual;

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.

MONTHS_BETWEEN(date1, date2)

=>usage of months between!!!!!Pay attentionto February related operations

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

NEW_TIME(date, timezone1, timezone2)

=>usage of new time

NEXT_DAY: This function returns the next given weekday after the given date.

NEXT_DAY(date, char)

Char value depends on your local setting if your language is ENGLISH than you must give English Weekdays

hr@ORACOS> select to_char(sysdate,’D’) from dual;


6                        –6th day of the week. saturday

hr@ORACOS> select sysdate,next_day(sysdate,’TUESDAY’) next_tuesday from dual;

SYSDATE                          NEXT_TUESDAY
——————-            ——————-
14-04-2007 15:17:39   17-04-2007 15:17:39

NUMTODSINTERVAL: Converts given integer value to INTERVAL DAY TO SECOND literal by using given interval unit.

NUMTODSINTERVAL(n, ‘interval_unit’)

interval unit is case insensitive and can be day,hour,minute or second

=>usage of numtodsinterval

NUMTOYMINTERVAL: Converts given integer value to INTERVAL YEARS TO MONTHS literal in by using given interval unit.

NUMTOYMINTERVAL(n, ‘interval_unit’)

=>usage of numtoyminterval

ROUND: returns date rounded to the unit specified by the format model fmt.
If you omit fmt given day is rounded to the next day by default. This behaviour is not the same with ‘day’ fmt

ROUND(date [, fmt ])

(for the permitted formats check the manual)

=>usage of round

SESSIONTIMEZONE: returns thetimezone value of the current session

hr@ORACOS> select sessiontimezone from dual;


hr@ORACOS> alter session set time_zone=’3:40′;

Session altered.

hr@ORACOS> select sessiontimezone from dual;


SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time–formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name.


hr@ORACOS> select sys_extract_utc(systimestamp) utc,systimestamp from dual;

UTC                                                             SYSTIMESTAMP
—————————————- —————————————-
14/04/2007 13:30:10,685000                14/04/2007 16:30:10,685000 +03:00

hr@ORACOS> select sys_extract_utc(current_timestamp) utc,current_timestamp from dual;

UTC                                                              CURRENT_TIMESTAMP
—————————————-   —————————————-
14/04/2007 13:30:16,716000                14/04/2007 17:10:16,716000 +03:40

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

=>usage of sysdate

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. Returning value is timestamp with timezone.

hr@ORACOS> select systimestamp,current_timestamp from dual;

SYSTIMESTAMP                                           CURRENT_TIMESTAMP
—————————————-     —————————————-
14/04/2007 16:39:35,318000 +03:00     14/04/2007 18:39:35,318000 +05:00

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’

=>usage of to_char

TO_TIMESTAMP converts char datatype to a value of TIMESTAMP datatype.

TO_TIMESTAMP(char [, fmt [ ‘nlsparam’ ] ])

  • 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.

=>usage of to_timestamp

TO_TIMESTAMP_TZ converts char datatype to a value of TIMESTAMP WITH TIME ZONE datatype. This function does not convert character strings to TIMESTAMP WITH LOCAL TIME ZONE to convert use CAST.

TO_TIMESTAMP_TZ(char [, fmt [ ‘nlsparam’ ] ])

  • fmt specifies the format of char with default format of the TIMESTAMP WITH TIME ZONE datatype. Look to the examples that nls_date_format overrides this value
  • ‘nlsparam’ has the same purpose in this function as in the TO_CHAR function for date conversion.

=>usage of to_timestamp_tz

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.

=>usage of to_dsinterval

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)


Char is the character string to be converted.

=>usage of to_yminterval

TZ_OFFSET: Returns the time zone offset corresponding to the argument based on the date the statement is executed

TZ_OFFSET({ ‘time_zone_name’| ‘{ + | – } hh : mi’| SESSIONTIMEZONE| DBTMEZONE })

Valid time_zone_name is located at the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.

=>usage of tz_offset

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

Note: All test are done under 10G EE

References Used: Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
(Chapter 12 – pg 522-540) Mr Thomas Kyte Oracle® Database SQL Reference 10g Release 2 (10.2)

Footnote 03/06/2009: For Different behaviours for different date format values please check this entry from David Fitzjarrell



  1. Great article…

    I am pretty much new to SQL. It gave a good idea about the dates. But I could not figure out how to convert a timestamp and find only the time portion and do a between.

    How do I find a time between two events which might fall every day.

    Say I need to find data where date should be, every day between 11.00 AM to 2 PM from 1st April 2007 to 3oth June 2007.

    Thanks in advance.

    Comment by Marimuthu P — July 23, 2007 @ 4:33 am

  2. I think you would like to get difference of two time intervals

    If I am not wrong you can use Thomas Kytes script from the link below

    If I find time to write the second part I will try to cover all

    thanks for your comment

    Comment by coskan — July 23, 2007 @ 1:51 pm

  3. […] How to see seconds past midnight (dont know how I missed this when I prepare take control of time […]

    Pingback by What I learned during Oracle SQL Expert Exam Study Part-1 « Coskan’s Approach to Oracle — March 5, 2009 @ 9:36 am

  4. I’ve read a few good stuff here. Certainly price bookmarking for
    revisiting. I wonder how so much attempt you set to make any such fantastic informative site.

    Comment by for kids — September 21, 2014 @ 11:25 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Blog at

%d bloggers like this: