📋Steps in this guide1/5

Oracle Dates, Timestamps and Intervals

An overview of the usage of dates, timestamps and intervals in Oracle databases.

oracle miscconfigurationintermediate

by OracleDba

28 views

Introduction

The way the Oracle database handles datetime values is pretty straightforward, but it seems to confuse many client-side and PL/SQL developers alike. The vast majority of problems people encounter are because of a misunderstanding about how dates are stored in the database. What you see on screen from a query is what's in the database right? Well actually, that is often not the case. Client tools, like SQL*Plus, convert datetime column values into something much nicer to look at. In the process, they often miss out very important information that can confuse you if you are not careful. The following examples use the type, but the issues apply equally to the type. Wrong! Both and columns contain a time component, which does not match in this case. SQL*Plus has converted the internal representation of the date into a nice string for us, but it has left out the time component. Why has it done this? Because it has used the format mask specified by the parameter to decide how to implicitly convert the date to a string. You can display the current database, instance and session NLS parameter values using this script . To get the full data we have to either explicitly ask for it using the function with a format mask. Or set the to the desired format mask. Another common mistake is when you specify a date as a string. That string looks perfectly acceptable to me, because I understand the variations in date formats and that looks like a UK representation of "27th April 2013" to me, but the database doesn't know that. To remedy this, we must either explicitly use the function with a format mask, set the appropriately, or use an ANSI literal. When using Oracle or values, remember the following simple rules and you will probably avoid most of the common pitfalls. - Both and types *always* contain a date and time component. At exactly midnight the time is 00:00:00. - Never rely on implicit conversions of strings to dates, or dates to strings. Always explicitly perform the conversions with the , and functions, or use ASNI or literals. - When doing date or timestamp comparisons, always consider the impact of the time component. If you want to discount the time component from the comparison, use the or functions to remove it from both sides of the comparison. The remainder of this article will discuss the , and types in more detail. The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD. The following table shows how each of the 7 bytes is used to store the date information. The following example uses the dump function to show the contents of a stored date. Comparing the date and dump values we see that subtracting 100 from the century component then multiplying the resulting value by 100 gives a value of 2000. Subtracting the 100 from the year component gives a value of 4. The month and day components need no modification, while subtracting 1 from the hour, minute and second components (18, 22 and 31) give values of 17, 21 and 30. Since dates are actually numbers, certain simple mathematical operations to can be performed on them. Adding a whole number to a date is like adding the equivalent number of days, while adding a fraction to a date is like adding that fraction of a day to the date. The same is true in reverse for subtraction. The following table shows how each specific time periods can be calculated. All three expressions equate to the same value, so pick the one you prefer. The following query shows how we might use these expressions to modify the value of the current operating system date. Oracle provides several date functions to make date manipulation simpler. The following table lists a selection of them and examples of their usage. Returns the current date-time from the operating system of the database server. Similar to the sysdate function, but returns the current date-time within the sessions time zone. Adds or subtracts the specified number of months from the specified date. Returns the last day of the month that contains the specified date. Returns the number of months between two dates. If the first date is prior to the second, the result is negative, otherwise it is positive. If both dates are on the same day of the month, or both the last day of the month the returned value is an integer, otherwise the return value includes a fraction of the month difference. Returns the date of the first day that matches the specified day that occurs after the specified date. Converts a date from timezone1 into the appropriate date for timeszone2. Converts a specified date to a string using the specified format mask. If the format mask is omitted the value is used. There is also an overload of this function to deal with timestamps where the default format mask is take from the or value. Converts a specified string to a date using the specified format mask. If the format mask is omitted the value is used. Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding. Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down. The and functions can be especially useful, so we will discuss their format models in more detail. The table below lists some of the available format models, their meanings and examples of their usage. The dates have been adjusted where necessary to show the difference between the return values of the functions. To the first year of the century (1901, 2001, 2101 etc.) To the year. Rounds up on January 1st. To the ISO Year. To the quarter, rounding up on the 16th day of the second month. To the month, rounding up on the 16th day. To the same day of the week as the first day of the year. To the same day of the week as the first day of the ISO year. To the same day of the week as the first day of the month. To the day. To the starting day of the week. To the hour. To the minute. Next we will discuss the datatype, which has many similarities with the datatype. The datatype is an extension on the datatype. In addition to the datetime elements of the datatype, the datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called and . As their names imply, these timestamps also store time zone offset information. Like dates, timestamps are stored using a binary date format. In the case of a this is 11 bytes long, while those with timezone information require 13 bytes. The following table shows how each of the 11-13 bytes is used to store the timestamp information. The following example uses the dump function to show the contents of a stored timestamp. The first 7 components match those of the DATE datatype, although they can look confusing due to the action of the offset. In this example the offset of +01:00 makes the hour component appear to be in 0 base notation rather than excess-1, but when we add the offset we can clearly see it is not. The offset component represents the number of minutes the time is offset due to the timezone. The mathematical operations and most of the date functions mentioned previously are also valid for timestamps. In addition to the date functions Oracle provides several timestamp specific functions listed in the table below. Returns the current from the operating system of the database server to the specified precision. If no precision is specified the default is 6. Similar to the function, but returns the current within the sessions time zone to the specified precision. If no precision is specified the default is 6. Similar to the current_timestamp function, but returns the current with time zone within the sessions time zone to the specified precision. If no precision is specified the default is 6. Converts a specified string to a using the specified format mask. If the format mask is omitted the or value is used depending on the context. Converts a and a string representing the time zone to a . Returns the database time zone. Returns the current sessions time zone. Returns the UTC, or GMT timestamp from a specified . Extracts the specified datepart from the specified timestamp. Next we will see how to convert between timestamps and dates. The CAST function can be used to convert a to a and vice versa. First let's convert a to a . To convert a to a do the following. Next we will see how intervals can be stored in the database and defined using the interval literal syntax. Intervals provide a way of storing a specific period of time that separates two datetime values. There are currently two supported types of interval, one specifying intervals in years and months, the other specifying intervals in days, hours, minutes and seconds. The syntax of these datatypes is shown below. The precision elements are defined as follows. - year_precision â€“ The maximum number of digits in the year component of the interval, such that a precision of 3 limits the interval to a maximum of 999 years. The default value is 2. - day_precision â€“ The maximum number of digits in the day component of the interval, such that a precision of 4 limits the interval to a maximum of 9999 days. The day precision can accept a value from 0 to 9, with the default value being 2. - fraction_second_precision â€“ The number of digits in the fractional component of the interval. Values between 0 and 9 are allowed, with the default value being 6. The following table is created to show how intervals can be used as column definitions. Interval literals are used to define intervals in an easy to understand manner. There are two separate syntax definitions, one for each type of interval. The full syntax definitions can be a little confusing so we will skip those in favor of examples that should make their usage clear. First we will start with the interval literal syntax. The default precision for the fields is listed below, along with the allowable values if specified as a trailing field. - YEAR - Number of years with a default precision of 2 digits. - MONTH - Number of months with a default precision of 4 digits. If specified as a trailing field it has allowable values of 0 to 11. These intervals can be tested by substituting them into the following query. Notice how month syntax is converted into a years and months value. A interval can be added to, or subtracted from, another with the result being another interval. The following examples relate to the interval literal syntax. As with the previous example, if a trailing field is specified it must be less significant than the previous field. - DAY - Number of days with a default precision of 2 digits. - HOUR - Number of hours with a default precision of 3 digits. If specified as a trailing field it has allowable values of 0 to 23. - MINUTE - Number of minutes with a default precision of 5 digits. If specified as a trailing field it has allowable values of 0 to 59. - SECOND - Number of seconds with a default precision of 7 digits before the decimal point and 6 digits after. If specified as a trailing field is has allowable values of 0 to 59.999999999. Substituting the above intervals into the following query will allow you to test them. Notice how the default precision for seconds is used because we have not limited it to 3 decimal places. A interval can be added to, or subtracted from, another with the result being another interval. Intervals can also be combined with dates to manipulate date values. The following query shows how. Oracle provides several interval specific functions, which are listed in the table below. Converts the specified integer to a interval where the integer represents the number of units. Converts the specified integer to interval where the integer represents the number of units. Converts a string representing an interval into a interval. Extracts the specified datepart from the specified interval. For more information see: - Datetime and Interval Data Types - Datetime Literals - TO_DATE - TO_CHAR(datetime) - ROUND(date) - TRUNC(date) Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

CREATE TABLE t1 (
  col1 DATE,
  col2 DATE
);

INSERT INTO t1 VALUES (TRUNC(SYSDATE), SYSDATE);
COMMIT;

SELECT * FROM t1;

COL1      COL2
--------- ---------
27-APR-13 27-APR-13

1 row selected.

SQL>

SELECT COUNT(*)
FROM   t1
WHERE  col1 = col2;

  COUNT(*)
----------
         0

1 row selected.

SQL>

SELECT TO_CHAR(col1, 'DD-MON-YYYY HH24:MI:SS') AS col1,
       TO_CHAR(col2, 'DD-MON-YYYY HH24:MI:SS') AS col2
FROM   t1;

COL1                 COL2
-------------------- --------------------
27-APR-2013 00:00:00 27-APR-2013 11:20:00

1 row selected.

SQL>

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t1;

COL1                 COL2
-------------------- --------------------
27-APR-2013 00:00:00 27-APR-2013 11:20:00

1 row selected.

SQL>

SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = '27/04/2013';
              *
ERROR at line 3:ISO
ORA-01843: not a valid month

SQL>

-- Explicit using TO_DATE
SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = TO_DATE('27/04/2013','DD/MM/YYYY');

  COUNT(*)
----------
	 1

SQL> 

-- Implicit using NLS_DATE_FORMAT
ALTER SESSION SET nls_date_format='DD/MM/YYYY';

SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = '27/04/2013';

  COUNT(*)
----------
	 1

SQL>

-- Explicit using ANSI DATE literal 
SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = DATE '2013-04-27';

  COUNT(*)
----------
	 1

SQL>

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

DROP TABLE date_test;

CREATE TABLE date_test AS
SELECT SYSDATE AS now
FROM   dual;

SELECT now,
       dump(now)
FROM   date_test;

NOW                  DUMP(NOW)
-------------------- -----------------------------------------
10-JUL-2004 17:21:30 Typ=12 Len=7: 120,104,7,10,18,22,31

1 row selected.

ALTER SESSION SET nls_date_format='DD/MM/YYYY HH24:MI:SS';

SELECT SYSDATE AS current_date,
       SYSDATE + 1 AS plus_1_day,
       SYSDATE + 2/24 AS plus_2_hours,
       SYSDATE + 10/24/60 AS plus_10_minutes,
       SYSDATE + 30/24/60/60 AS plus_30_seconds
FROM   dual;

CURRENT_DATE        PLUS_1_DAY          PLUS_2_HOURS        PLUS_10_MINUTES     PLUS_30_SECONDS
------------------- ------------------- ------------------- ------------------- -------------------
10/07/2004 17:57:30 11/07/2004 17:57:30 10/07/2004 19:57:30 10/07/2004 18:07:30 10/07/2004 17:58:00

1 row selected.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE 
FROM   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.

SELECT CURRENT_DATE 
FROM   dual;

CURRENT_DATE
-------------------
10/07/2004 18:36:24

1 row selected.

SELECT SYSDATE, 
       ADD_MONTHS(SYSDATE, 2) 
FROM   dual;

SYSDATE             ADD_MONTHS(SYSDATE,
------------------- -------------------
10/07/2004 18:40:46 10/09/2004 18:40:46

1 row selected.

SELECT SYSDATE, 
       LAST_DAY(SYSDATE) 
FROM   dual;

SYSDATE             LAST_DAY(SYSDATE)
------------------- -------------------
10/07/2004 18:42:14 31/07/2004 18:42:14

1 row selected.

SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+30) 
FROM   dual;

MONTHS_BETWEEN(SYSDATE,SYSDATE+30)
----------------------------------
                                -1

1 row selected.

SELECT SYSDATE, 
       NEXT_DAY(SYSDATE, 'MONDAY') 
FROM   dual;

SYSDATE             NEXT_DAY(SYSDATE,'M
------------------- -------------------
10/07/2004 18:43:44 12/07/2004 18:43:44

1 row selected.

SELECT SYSDATE, 
       NEW_TIME(SYSDATE, 'GMT', 'EST') 
FROM   dual;

SYSDATE             NEW_TIME(SYSDATE,'G
------------------- -------------------
10/07/2004 18:46:12 10/07/2004 13:46:12

1 row selected.

SELECT TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI') 
FROM   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.

SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') 
FROM   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.

SELECT SYSDATE, 
       ROUND(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.

SELECT SYSDATE, 
       TRUNC(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'CC'),
       ROUND(SYSDATE, 'CC')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'CC')  ROUND(SYSDATE,'CC')
-------------------- -------------------- --------------------
16-JAN-1999 08:48:09 01-JAN-1901 00:00:00 01-JAN-2001 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'YY'),
       ROUND(SYSDATE, 'YY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'YY')  ROUND(SYSDATE,'YY')
-------------------- -------------------- --------------------
08-JUL-2004 08:08:49 01-JAN-2004 00:00:00 01-JAN-2005 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'IY'),
       ROUND(SYSDATE, 'IY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IY')  ROUND(SYSDATE,'IY')
-------------------- -------------------- --------------------
08-JUL-2004 08:10:39 29-DEC-2003 00:00:00 03-JAN-2005 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'Q'),
       ROUND(SYSDATE, 'Q')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'Q')   ROUND(SYSDATE,'Q')
-------------------- -------------------- -------------------
22-AUG-2004 08:23:56 01-JUL-2004 00:00:00 01-OCT-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'MM'),
       ROUND(SYSDATE, 'MM')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'MM')  ROUND(SYSDATE,'MM')
-------------------- -------------------- --------------------
16-JUL-2004 08:15:31 01-JUL-2004 00:00:00 01-AUG-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'WW'),
       ROUND(SYSDATE, 'WW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'WW')  ROUND(SYSDATE,'WW')
-------------------- -------------------- --------------------
12-JUL-2004 08:20:28 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'IW'),
       ROUND(SYSDATE, 'IW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IW')  ROUND(SYSDATE,'IW')
-------------------- -------------------- --------------------
16-JUL-2004 08:26:02 12-JUL-2004 00:00:00 19-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'W'),
       ROUND(SYSDATE, 'W')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'W')   ROUND(SYSDATE,'W')
-------------------- -------------------- --------------------
13-JUL-2004 08:28:10 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'DD'),
       ROUND(SYSDATE, 'DD')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'DD')  ROUND(SYSDATE,'DD')
-------------------- -------------------- --------------------
08-JUL-2004 20:34:24 08-JUL-2004 00:00:00 09-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'D'),
       ROUND(SYSDATE, 'D')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'D')   ROUND(SYSDATE,'D')
-------------------- -------------------- --------------------
09-JUL-2004 08:33:01 04-JUL-2004 00:00:00 11-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'HH'),
       ROUND(SYSDATE, 'HH')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:36:22 08-JUL-2004 08:00:00 08-JUL-2004 09:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSD'MI'),
       ROUND(SYSDAT
  'MI')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:37:32 08-JUL-2004 08:37:00 08-JUL-2004 08:38:00

1 row selected.

ALTER SESSION SET nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';

DROP TABLE timestamp_test;

CREATE TABLE timestamp_test AS
SELECT SYSTIMESTAMP AS now
FROM   dual;

SELECT now,
       DUMP(now)
FROM   timestamp_test;

NOW                                                      DUMP(NOW)
-------------------------------------------------------- --------------------------------------------------------
31-JUL-04 11.15.05.662509 +01:00                         Typ=181 Len=13: 120,104,7,31,11,16,6,39,125,21,200,21,60

1 row selected.

SELECT SYSTIMESTAMP(3) 
FROM   dual;

SYSTIMESTAMP(3)
-----------------------------
10-JUL-04 19.09.35.793 +01:00

1 row selected.

SELECT CURRENT_TIMESTAMP(3) 
FROM   dual;

CURRENT_TIMESTAMP(3)
-----------------------------
10-JUL-04 19.11.12.686 +01:00

1 row selected.

SELECT LOCALTIMESTAMP(3) 
FROM   dual;

LOCALTIMESTAMP(3)
----------------------
10-JUL-04 19.12.21.859

1 row selected.

SELECT TO_TIMESTAMP('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP('10/07/2004','DD/MM/YYYY')
---------------------------------------
10-JUL-04 00.00.00.000000000

1 row selected.

SELECT TO_TIMESTAMP_TZ('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP_TZ('10/07/2004','DD/MM/YYYY')
------------------------------------------
10-JUL-04 00.00.00.000000000 +01:00

1 row selected.

SELECT FROM_TZ(LOCALTIMESTAMP, '3:00') 
FROM   dual;

FROM_TZ(LOCALTIMESTAMP,'3:00')
--------------------------------
10-JUL-04 19.19.07.385684 +03:00

1 row selected.

SELECT DBTIMEZONE  
FROM   dual;

DBTIME
------
+00:00

1 row selected.

SELECT SESSIONTIMEZONE  
FROM ual;

SESSIONTIMEZONE
---------------
+01:00

1 row selected.

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)  
FROM dual;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
-----------------------------
10-JUL-04 18.23.09.393478

1 row selected.

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) 
FROM dual;

EXTRACT(HOURFROMSYSTIMESTAMP)
-----------------------------
                           18

1 row selected.

SELECT CAST(SYSTIMESTAMP AS DATE) ts_to_date
FROM   dual;

TS_TO_DAT
---------
08-SEP-13

SQL>

SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts
FROM   dual;

DATE_TO_TS
---------------------------------------------------------------------------
08-SEP-13 09.21.45.000000 AM

SQL>

INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

CREATE TABLE test_interval_table (
  id             NUMBER(10),
  time_period_1  INTERVAL YEAR TO MONTH,
  time_period_2  INTERVAL DAY TO SECOND,
  time_period_3  INTERVAL YEAR (3) TO MONTH,
  time_period_4  INTERVAL DAY (4) TO SECOND (9)
);

SQL> DESC test_interval_table
 Name                             Null?    Type
 -------------------------------- -------- ------------------------------------
 ID                                        NUMBER(10)
 TIME_PERIOD_1                             INTERVAL YEAR(2) TO MONTH
 TIME_PERIOD_2                             INTERVAL DAY(2) TO SECOND(6)
 TIME_PERIOD_3                             INTERVAL YEAR(3) TO MONTH
 TIME_PERIOD_4                             INTERVAL DAY(4) TO SECOND(9)

SELECT INTERVAL '20' MONTH
FROM   dual;

INTERVAL'20'MONTH
---------------------------------------------------------------------------
+01-08

1 row selected.

SELECT INTERVAL '1' YEAR â€“ INTERVAL '1' MONTH
FROM dual;

INTERVAL'1'YEAR-INTERVAL'1'MONTH
---------------------------------------------------------------------------
+000000000-11

1 row selected.

SELECT INTERVAL '2 3:04:11.333' DAY TO SECOND
FROM   dual;

INTERVAL'23:04:11.333'DAYTOSECOND
---------------------------------------------------------------------------
+02 03:04:11.333000

1 row selected.

SELECT INTERVAL '1' DAY â€“ INTERVAL '1' SECOND
FROM dual;

INTERVAL'1'DAY-INTERVAL'1'SECOND
---------------------------------------------------------------------------
+000000000 23:59:59.000000000

1 row selected.

SELECT SYSDATE,
       SYSDATE + INTERVAL '1' MONTH + INTERVAL '1' DAY â€“ INTERVAL '3' SECOND
FROM   dual;

SYSDATE              SYSDATE+INTERVAL'1'M
-------------------- --------------------
10-JUL-2004 19:55:53 11-AUG-2004 19:55:50

1 row selected.

SELECT NUMTOYMINTERVAL(2, 'MONTH') 
FROM   dual;

NUMTOYMINTERVAL(2,'MONTH')
--------------------------
+000000000-02

1 row selected.

SELECT NUMTODSINTERVAL(2, 'HOUR') 
FROM   dual;

NUMTODSINTERVAL(2,'HOUR')
-----------------------------
+000000000 02:00:00.000000000

1 row selected.

SELECT TO_YMINTERVAL('3-10') 
FROM   dual;

TO_YMINTERVAL('3-10')
---------------------
+000000003-10

1 row selected.

SELECT TO_DSINTERVAL('2 10:3:45.123') 
FROM   dual;

TO_DSINTERVAL('210:3:45.123')
-----------------------------
+000000002 10:03:45.123000000

1 row selected.

SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(2, 'HOUR')) 
FROM   dual;

EXTRACT(HOURFROMNUMTODSINTERVAL(2,'HOUR'))
------------------------------------------
                                         2

1 row selected.

DATE

The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD. The following table shows how each of the 7 bytes is used to store the date information. The following example uses the dump function to show the contents of a stored date. Comparing the date and dump values we see that subtracting 100 from the century component then multiplying the resulting value by 100 gives a value of 2000. Subtracting the 100 from the year component gives a value of 4. The month and day components need no modification, while subtracting 1 from the hour, minute and second components (18, 22 and 31) give values of 17, 21 and 30. Since dates are actually numbers, certain simple mathematical operations to can be performed on them. Adding a whole number to a date is like adding the equivalent number of days, while adding a fraction to a date is like adding that fraction of a day to the date. The same is true in reverse for subtraction. The following table shows how each specific time periods can be calculated. All three expressions equate to the same value, so pick the one you prefer. The following query shows how we might use these expressions to modify the value of the current operating system date. Oracle provides several date functions to make date manipulation simpler. The following table lists a selection of them and examples of their usage. Returns the current date-time from the operating system of the database server. Similar to the sysdate function, but returns the current date-time within the sessions time zone. Adds or subtracts the specified number of months from the specified date. Returns the last day of the month that contains the specified date. Returns the number of months between two dates. If the first date is prior to the second, the result is negative, otherwise it is positive. If both dates are on the same day of the month, or both the last day of the month the returned value is an integer, otherwise the return value includes a fraction of the month difference. Returns the date of the first day that matches the specified day that occurs after the specified date. Converts a date from timezone1 into the appropriate date for timeszone2. Converts a specified date to a string using the specified format mask. If the format mask is omitted the value is used. There is also an overload of this function to deal with timestamps where the default format mask is take from the or value. Converts a specified string to a date using the specified format mask. If the format mask is omitted the value is used. Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding. Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down. The and functions can be especially useful, so we will discuss their format models in more detail. The table below lists some of the available format models, their meanings and examples of their usage. The dates have been adjusted where necessary to show the difference between the return values of the functions. To the first year of the century (1901, 2001, 2101 etc.) To the year. Rounds up on January 1st. To the ISO Year. To the quarter, rounding up on the 16th day of the second month. To the month, rounding up on the 16th day. To the same day of the week as the first day of the year. To the same day of the week as the first day of the ISO year. To the same day of the week as the first day of the month. To the day. To the starting day of the week. To the hour. To the minute. Next we will discuss the datatype, which has many similarities with the datatype.

Code/Command (click line numbers to comment):

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

DROP TABLE date_test;

CREATE TABLE date_test AS
SELECT SYSDATE AS now
FROM   dual;

SELECT now,
       dump(now)
FROM   date_test;

NOW                  DUMP(NOW)
-------------------- -----------------------------------------
10-JUL-2004 17:21:30 Typ=12 Len=7: 120,104,7,10,18,22,31

1 row selected.

ALTER SESSION SET nls_date_format='DD/MM/YYYY HH24:MI:SS';

SELECT SYSDATE AS current_date,
       SYSDATE + 1 AS plus_1_day,
       SYSDATE + 2/24 AS plus_2_hours,
       SYSDATE + 10/24/60 AS plus_10_minutes,
       SYSDATE + 30/24/60/60 AS plus_30_seconds
FROM   dual;

CURRENT_DATE        PLUS_1_DAY          PLUS_2_HOURS        PLUS_10_MINUTES     PLUS_30_SECONDS
------------------- ------------------- ------------------- ------------------- -------------------
10/07/2004 17:57:30 11/07/2004 17:57:30 10/07/2004 19:57:30 10/07/2004 18:07:30 10/07/2004 17:58:00

1 row selected.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE 
FROM   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.

SELECT CURRENT_DATE 
FROM   dual;

CURRENT_DATE
-------------------
10/07/2004 18:36:24

1 row selected.

SELECT SYSDATE, 
       ADD_MONTHS(SYSDATE, 2) 
FROM   dual;

SYSDATE             ADD_MONTHS(SYSDATE,
------------------- -------------------
10/07/2004 18:40:46 10/09/2004 18:40:46

1 row selected.

SELECT SYSDATE, 
       LAST_DAY(SYSDATE) 
FROM   dual;

SYSDATE             LAST_DAY(SYSDATE)
------------------- -------------------
10/07/2004 18:42:14 31/07/2004 18:42:14

1 row selected.

SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+30) 
FROM   dual;

MONTHS_BETWEEN(SYSDATE,SYSDATE+30)
----------------------------------
                                -1

1 row selected.

SELECT SYSDATE, 
       NEXT_DAY(SYSDATE, 'MONDAY') 
FROM   dual;

SYSDATE             NEXT_DAY(SYSDATE,'M
------------------- -------------------
10/07/2004 18:43:44 12/07/2004 18:43:44

1 row selected.

SELECT SYSDATE, 
       NEW_TIME(SYSDATE, 'GMT', 'EST') 
FROM   dual;

SYSDATE             NEW_TIME(SYSDATE,'G
------------------- -------------------
10/07/2004 18:46:12 10/07/2004 13:46:12

1 row selected.

SELECT TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI') 
FROM   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.

SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') 
FROM   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.

SELECT SYSDATE, 
       ROUND(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.

SELECT SYSDATE, 
       TRUNC(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'CC'),
       ROUND(SYSDATE, 'CC')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'CC')  ROUND(SYSDATE,'CC')
-------------------- -------------------- --------------------
16-JAN-1999 08:48:09 01-JAN-1901 00:00:00 01-JAN-2001 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'YY'),
       ROUND(SYSDATE, 'YY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'YY')  ROUND(SYSDATE,'YY')
-------------------- -------------------- --------------------
08-JUL-2004 08:08:49 01-JAN-2004 00:00:00 01-JAN-2005 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'IY'),
       ROUND(SYSDATE, 'IY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IY')  ROUND(SYSDATE,'IY')
-------------------- -------------------- --------------------
08-JUL-2004 08:10:39 29-DEC-2003 00:00:00 03-JAN-2005 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'Q'),
       ROUND(SYSDATE, 'Q')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'Q')   ROUND(SYSDATE,'Q')
-------------------- -------------------- -------------------
22-AUG-2004 08:23:56 01-JUL-2004 00:00:00 01-OCT-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'MM'),
       ROUND(SYSDATE, 'MM')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'MM')  ROUND(SYSDATE,'MM')
-------------------- -------------------- --------------------
16-JUL-2004 08:15:31 01-JUL-2004 00:00:00 01-AUG-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'WW'),
       ROUND(SYSDATE, 'WW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'WW')  ROUND(SYSDATE,'WW')
-------------------- -------------------- --------------------
12-JUL-2004 08:20:28 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'IW'),
       ROUND(SYSDATE, 'IW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IW')  ROUND(SYSDATE,'IW')
-------------------- -------------------- --------------------
16-JUL-2004 08:26:02 12-JUL-2004 00:00:00 19-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'W'),
       ROUND(SYSDATE, 'W')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'W')   ROUND(SYSDATE,'W')
-------------------- -------------------- --------------------
13-JUL-2004 08:28:10 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'DD'),
       ROUND(SYSDATE, 'DD')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'DD')  ROUND(SYSDATE,'DD')
-------------------- -------------------- --------------------
08-JUL-2004 20:34:24 08-JUL-2004 00:00:00 09-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'D'),
       ROUND(SYSDATE, 'D')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'D')   ROUND(SYSDATE,'D')
-------------------- -------------------- --------------------
09-JUL-2004 08:33:01 04-JUL-2004 00:00:00 11-JUL-2004 00:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSDATE, 'HH'),
       ROUND(SYSDATE, 'HH')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:36:22 08-JUL-2004 08:00:00 08-JUL-2004 09:00:00

1 row selected.

SELECT SYSDATE,
       TRUNC(SYSD'MI'),
       ROUND(SYSDAT
  'MI')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:37:32 08-JUL-2004 08:37:00 08-JUL-2004 08:38:00

1 row selected.

TIMESTAMP

The datatype is an extension on the datatype. In addition to the datetime elements of the datatype, the datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called and . As their names imply, these timestamps also store time zone offset information. Like dates, timestamps are stored using a binary date format. In the case of a this is 11 bytes long, while those with timezone information require 13 bytes. The following table shows how each of the 11-13 bytes is used to store the timestamp information. The following example uses the dump function to show the contents of a stored timestamp. The first 7 components match those of the DATE datatype, although they can look confusing due to the action of the offset. In this example the offset of +01:00 makes the hour component appear to be in 0 base notation rather than excess-1, but when we add the offset we can clearly see it is not. The offset component represents the number of minutes the time is offset due to the timezone. The mathematical operations and most of the date functions mentioned previously are also valid for timestamps. In addition to the date functions Oracle provides several timestamp specific functions listed in the table below. Returns the current from the operating system of the database server to the specified precision. If no precision is specified the default is 6. Similar to the function, but returns the current within the sessions time zone to the specified precision. If no precision is specified the default is 6. Similar to the current_timestamp function, but returns the current with time zone within the sessions time zone to the specified precision. If no precision is specified the default is 6. Converts a specified string to a using the specified format mask. If the format mask is omitted the or value is used depending on the context. Converts a and a string representing the time zone to a . Returns the database time zone. Returns the current sessions time zone. Returns the UTC, or GMT timestamp from a specified . Extracts the specified datepart from the specified timestamp. Next we will see how to convert between timestamps and dates.

Code/Command (click line numbers to comment):

ALTER SESSION SET nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';

DROP TABLE timestamp_test;

CREATE TABLE timestamp_test AS
SELECT SYSTIMESTAMP AS now
FROM   dual;

SELECT now,
       DUMP(now)
FROM   timestamp_test;

NOW                                                      DUMP(NOW)
-------------------------------------------------------- --------------------------------------------------------
31-JUL-04 11.15.05.662509 +01:00                         Typ=181 Len=13: 120,104,7,31,11,16,6,39,125,21,200,21,60

1 row selected.

SELECT SYSTIMESTAMP(3) 
FROM   dual;

SYSTIMESTAMP(3)
-----------------------------
10-JUL-04 19.09.35.793 +01:00

1 row selected.

SELECT CURRENT_TIMESTAMP(3) 
FROM   dual;

CURRENT_TIMESTAMP(3)
-----------------------------
10-JUL-04 19.11.12.686 +01:00

1 row selected.

SELECT LOCALTIMESTAMP(3) 
FROM   dual;

LOCALTIMESTAMP(3)
----------------------
10-JUL-04 19.12.21.859

1 row selected.

SELECT TO_TIMESTAMP('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP('10/07/2004','DD/MM/YYYY')
---------------------------------------
10-JUL-04 00.00.00.000000000

1 row selected.

SELECT TO_TIMESTAMP_TZ('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP_TZ('10/07/2004','DD/MM/YYYY')
------------------------------------------
10-JUL-04 00.00.00.000000000 +01:00

1 row selected.

SELECT FROM_TZ(LOCALTIMESTAMP, '3:00') 
FROM   dual;

FROM_TZ(LOCALTIMESTAMP,'3:00')
--------------------------------
10-JUL-04 19.19.07.385684 +03:00

1 row selected.

SELECT DBTIMEZONE  
FROM   dual;

DBTIME
------
+00:00

1 row selected.

SELECT SESSIONTIMEZONE  
FROM ual;

SESSIONTIMEZONE
---------------
+01:00

1 row selected.

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)  
FROM dual;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
-----------------------------
10-JUL-04 18.23.09.393478

1 row selected.

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) 
FROM dual;

EXTRACT(HOURFROMSYSTIMESTAMP)
-----------------------------
                           18

1 row selected.

Converting Between Timestamps and Dates

The CAST function can be used to convert a to a and vice versa. First let's convert a to a . To convert a to a do the following. Next we will see how intervals can be stored in the database and defined using the interval literal syntax.

Code/Command (click line numbers to comment):

SELECT CAST(SYSTIMESTAMP AS DATE) ts_to_date
FROM   dual;

TS_TO_DAT
---------
08-SEP-13

SQL>

SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts
FROM   dual;

DATE_TO_TS
---------------------------------------------------------------------------
08-SEP-13 09.21.45.000000 AM

SQL>

INTERVAL

Intervals provide a way of storing a specific period of time that separates two datetime values. There are currently two supported types of interval, one specifying intervals in years and months, the other specifying intervals in days, hours, minutes and seconds. The syntax of these datatypes is shown below. The precision elements are defined as follows. - year_precision â€“ The maximum number of digits in the year component of the interval, such that a precision of 3 limits the interval to a maximum of 999 years. The default value is 2. - day_precision â€“ The maximum number of digits in the day component of the interval, such that a precision of 4 limits the interval to a maximum of 9999 days. The day precision can accept a value from 0 to 9, with the default value being 2. - fraction_second_precision â€“ The number of digits in the fractional component of the interval. Values between 0 and 9 are allowed, with the default value being 6. The following table is created to show how intervals can be used as column definitions. Interval literals are used to define intervals in an easy to understand manner. There are two separate syntax definitions, one for each type of interval. The full syntax definitions can be a little confusing so we will skip those in favor of examples that should make their usage clear. First we will start with the interval literal syntax. The default precision for the fields is listed below, along with the allowable values if specified as a trailing field. - YEAR - Number of years with a default precision of 2 digits. - MONTH - Number of months with a default precision of 4 digits. If specified as a trailing field it has allowable values of 0 to 11. These intervals can be tested by substituting them into the following query. Notice how month syntax is converted into a years and months value. A interval can be added to, or subtracted from, another with the result being another interval. The following examples relate to the interval literal syntax. As with the previous example, if a trailing field is specified it must be less significant than the previous field. - DAY - Number of days with a default precision of 2 digits. - HOUR - Number of hours with a default precision of 3 digits. If specified as a trailing field it has allowable values of 0 to 23. - MINUTE - Number of minutes with a default precision of 5 digits. If specified as a trailing field it has allowable values of 0 to 59. - SECOND - Number of seconds with a default precision of 7 digits before the decimal point and 6 digits after. If specified as a trailing field is has allowable values of 0 to 59.999999999. Substituting the above intervals into the following query will allow you to test them. Notice how the default precision for seconds is used because we have not limited it to 3 decimal places. A interval can be added to, or subtracted from, another with the result being another interval. Intervals can also be combined with dates to manipulate date values. The following query shows how. Oracle provides several interval specific functions, which are listed in the table below. Converts the specified integer to a interval where the integer represents the number of units. Converts the specified integer to interval where the integer represents the number of units. Converts a string representing an interval into a interval. Extracts the specified datepart from the specified interval. For more information see: - Datetime and Interval Data Types - Datetime Literals - TO_DATE - TO_CHAR(datetime) - ROUND(date) - TRUNC(date) Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

CREATE TABLE test_interval_table (
  id             NUMBER(10),
  time_period_1  INTERVAL YEAR TO MONTH,
  time_period_2  INTERVAL DAY TO SECOND,
  time_period_3  INTERVAL YEAR (3) TO MONTH,
  time_period_4  INTERVAL DAY (4) TO SECOND (9)
);

SQL> DESC test_interval_table
 Name                             Null?    Type
 -------------------------------- -------- ------------------------------------
 ID                                        NUMBER(10)
 TIME_PERIOD_1                             INTERVAL YEAR(2) TO MONTH
 TIME_PERIOD_2                             INTERVAL DAY(2) TO SECOND(6)
 TIME_PERIOD_3                             INTERVAL YEAR(3) TO MONTH
 TIME_PERIOD_4                             INTERVAL DAY(4) TO SECOND(9)

SELECT INTERVAL '20' MONTH
FROM   dual;

INTERVAL'20'MONTH
---------------------------------------------------------------------------
+01-08

1 row selected.

SELECT INTERVAL '1' YEAR â€“ INTERVAL '1' MONTH
FROM dual;

INTERVAL'1'YEAR-INTERVAL'1'MONTH
---------------------------------------------------------------------------
+000000000-11

1 row selected.

SELECT INTERVAL '2 3:04:11.333' DAY TO SECOND
FROM   dual;

INTERVAL'23:04:11.333'DAYTOSECOND
---------------------------------------------------------------------------
+02 03:04:11.333000

1 row selected.

SELECT INTERVAL '1' DAY â€“ INTERVAL '1' SECOND
FROM dual;

INTERVAL'1'DAY-INTERVAL'1'SECOND
---------------------------------------------------------------------------
+000000000 23:59:59.000000000

1 row selected.

SELECT SYSDATE,
       SYSDATE + INTERVAL '1' MONTH + INTERVAL '1' DAY â€“ INTERVAL '3' SECOND
FROM   dual;

SYSDATE              SYSDATE+INTERVAL'1'M
-------------------- --------------------
10-JUL-2004 19:55:53 11-AUG-2004 19:55:50

1 row selected.

SELECT NUMTOYMINTERVAL(2, 'MONTH') 
FROM   dual;

NUMTOYMINTERVAL(2,'MONTH')
--------------------------
+000000000-02

1 row selected.

SELECT NUMTODSINTERVAL(2, 'HOUR') 
FROM   dual;

NUMTODSINTERVAL(2,'HOUR')
-----------------------------
+000000000 02:00:00.000000000

1 row selected.

SELECT TO_YMINTERVAL('3-10') 
FROM   dual;

TO_YMINTERVAL('3-10')
---------------------
+000000003-10

1 row selected.

SELECT TO_DSINTERVAL('2 10:3:45.123') 
FROM   dual;

TO_DSINTERVAL('210:3:45.123')
-----------------------------
+000000002 10:03:45.123000000

1 row selected.

SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(2, 'HOUR')) 
FROM   dual;

EXTRACT(HOURFROMNUMTODSINTERVAL(2,'HOUR'))
------------------------------------------
                                         2

1 row selected.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!