Oracle time type and function processing

Source: Internet
Author: User
1. date type used while date type subtraction only get days SQLgt; SELECTsysdate-(sysdate-1) FROMdual; SYSDATE-(SYSDATE-1 )--------------

1. date type used while date type subtraction only get days SQLgt; SELECT sysdate-(sysdate-1) FROM dual; SYSDATE-(SYSDATE-1 )--------------

I. date type usage



SQL> select systimestamp ts, systimestamp + 1 dt from dual;

TS DT
-------------------------------------------------------
-10 10.40.00.265000 AM + 08:00 2010-12-5 1

Solve the implicit conversion problem (retain the timestamp type)
SQL> select distinct imestamp ts, systimestamp + numtodsinterval (1, 'day') dt from dual;

TS DT
------------------------------------------------------------------------------------------
Am + AM


Iii. Use of the interval year to month type

Interval year to month can be replaced by the numtoyminterval or to_yminterval function.
SQL> selectNumtoyminterval (5, 'Year') + numtoyminterval (2, 'month ')From dual;

NUMTOYMINTERVAL (5, 'Year') + NUMT
---------------------------------------
+ 000000005-02

SQL> select numtoyminterval (5*12 + 2, 'month') from dual;

NUMTOYMINTERVAL (5*12 + 2, 'month'
---------------------------------------
+ 000000005-02

SQL> select to_yminterval ('5-2') from dual;

TO_YMINTERVAL ('5-2 ')
---------------------------------------
+ 000000005-02

SQL> select interval '5-2' year to month from dual;

INTERVAL '5-2' YEARTOMONTH
---------------------------------------
+ 05-02


Iv. Use of the interval day to second type

Interval day to second can be replaced with numtodsinterval or to_dsinterval
SQL> select numtodsinterval (10, 'day') + numtodsinterval (2, 'hour') +
2 numtodsinterval (3, 'minute ') + numtodsinterval (2.3312, 'second ')
3 from dual;

NUMTODSINTERVAL (10, 'day') + NUMT
---------------------------------------
+ 000000010 02:03:02. 331200000

SQL> select numtodsinterval (10*24*60*60*60 + 2*3600 + 3*60 + 2.3312, 'second') from dual;

NUMTODSINTERVAL (10*24*60*60 + 2 *
---------------------------------------
+ 000000010 02:03:02. 331200000

SQL> select to_dsinterval ('10 02:03:02. 3312 ') from dual;

TO_DSINTERVAL ('2017: 03: 661'
---------------------------------------
+ 000000010 02:03:02. 331200000

SQL> select interval '10 02:03:02. 3312 'Day to second from dual;

INTERVAL '2017: 03: 8080' DAYTO
---------------------------------------
+ 10 02:03:02. 331200

V. Time Functions

ADD_MONTHS Function
The ADD_MONTHS function adds the specified number of months to a date, so the day in the date will not change. However, if the start date is the last day of a month, the result will be adjusted
The returned value still corresponds to the last day of the new month. If the number of days in the result month is less than the number of days in the Start month, it is adjusted back to adapt to the valid date.
Select ADD_MONTHS (TO_DATE ('15-11-1961 ', 'dd-mm-yyyy'), 1) from dual; -- 1961-12-15
Select ADD_MONTHS (TO_DATE ('30-11-1961 ', 'dd-mm-yyyy'), 1) from dual; -- 1961-12-31
Select ADD_MONTHS (TO_DATE ('31-1-1999 ', 'dd-mm-yyyy'), 1) from dual; --

MONTHS_BETWEEN Function
MONTHS_BETWEEN (x, y) is used to calculate the number of months between x and y. If x is earlier than y in the calendar, MONTHS_BETWEEN () returns a negative number.
When the month difference between x and y is not the whole month, it can be expressed in decimal places.

NUMTODSINTERVAL () function
NUMTODSINTERVAL (x, interval_unit) is used TO convert the number x specified in interval_unit into an interval day to second type,
The interval_unit parameter can be set to DAY, HOUR, MINUTE, or SECOND.

NUMTOYMINTERVAL () function
NUMTOYMINTERVAL (x, interval_unit) is used TO convert the number x specified in interval_unit into an interval year to month type,
The interval_unit parameter can be set to YEAR or MONTH.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.