Re: Q: Date/time arithmetics with Oracle Server

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 25 Jun 1999 17:01:37 GMT
Message-ID: <3775b58c.81133563@newshost.us.oracle.com>

A copy of this was sent to Uwe Schneider <uwe.schneider_at_xlink.net> (if that email address didn't require changing) On Fri, 25 Jun 1999 18:42:21 +0200, you wrote:

>Hi experts,
>
>is there an easy way to add or subtract a number of seconds to/from an
>Oracle date type such as
>
>ADD_SECONDS(date_val,-20)
>
>without using special packages???
>
>In my opinion the Oracle DATE type is quite strange.
>
>Best regards!

no need for a special function -- just add (see below for more info on date math).

for example

select date_val + (1/24/60/60)*5 from T

adds 5 seconds. if you add 1 to a date you get tomorrow. 1/24 -- adds an hour
1/24/6- -- adds a minute
etc.

from the oracle7 "sql reference" manual. (same place in the o8 docs as well) chapter 2 - elements of oracle7 sql
section on datatypes/Date datatype:

<quote>
DATE Datatype

The DATE datatype is used to store date and time information. Although date and time information can be represented in both CHAR and NUMBER datatypes, the DATE datatype has special associated properties.
For each DATE value the following information is stored: • century
• year
• month
• day
• hour
• minute
• second

To specify a date value, you must convert a character or numeric value to a data value with the TO_DATE function. Oracle7 automatically converts character values that are in the default date format into date values when they are used in date expressions. The default date format is specified by the initialization parameter NLS_DATE_FORMAT and is a string such as ’DD–MON–YY’. This example date format includes a two–digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

If you specify a date value without a time component, the default time is 12:00:00a.m. (midnight). If you specify a date value without a date, the default date is the first day of the current month. The date function SYSDATE returns the current date and time. For information on the SYSDATE and TO_DATE functions and the default date format, see Chapter 3 “Operators, Functions, Expressions, Conditions” of this manual.

Date Arithmetic

---

You can add and subtract number constants as well as other dates from dates. Oracle7 interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE – 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Oracle7 provides functions for many of the common date operations. For example, the ADD_MONTHS function allows you to add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31–day month. For more information on date functions, see the section “Date Functions” on page 3 – 37.

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

Using Julian Dates

---

A Julian date is the number of days since Jan 1, 4712 BC. Julian dates allow continuous dating from a common reference. You can use the date format model “J” with date functions TO_DATE and TO_CHAR to convert between Oracle7 DATE values and their Julian equivalents. This statement returns the Julian equivalent of January 1, 1992: SELECT TO_CHAR(TO_DATE(’01–01–1992’, ’MM–DD–YYYY’),’J’) FROM DUAL
TO_CHAR(TO_DATE(’01–01–1992’,’MM–DD–YYYY),’J’)

––––––––––––––––––––––––––––––––––––––––––––––

2448623

</quote>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 25 1999 - 12:01:37 CDT