Shulou(Shulou.com)06/01 Report--

This chapter includes:

Process and store a specific date and time. The DATE type can store century, 4-digit year, month, day, hour, minute and second

Using the timestamp timestamp, the timestamp can store a specific date and time. The advantage of timestamp is that it can store seconds with decimal places and time zone.

Using the interval interval, the interval can store the length of time. Such as 1 year and 3 months, etc.

In the Oracle database, by default, dates are saved to the database in DD-MON-YYYY format, where:

DD is a two-digit number of days.

MON is the first three letters of the month, such as FEB

YYYY is a 4-digit year.

By default, the database returns the date in DD-MON-YY format, where YY is the last two digits of the year.

1. Use TO_DATE () and TO_CHAR () to convert time values.

TO_CHAR (x [, format]) is used to convert the time value x to a string, which provides an optional parameter, format, to describe the format of x.

SQL > select sysdate from dual;SYSDATE-2016/6/9 12SQL > select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;TO_CHAR (SYSDATE,'YYYY-MM-DDHH2--2016-06-09 12:32:28SQL > select to_char (sysdate) from dual TO_CHAR (SYSDATE)-09-June-16

TO_DATE (x [, format]) is used to convert the string x to a time value, and this function specifies an optional string format parameter to describe the format of x. If no format parameter is specified, the date is in the default database format. The format of x should be the same as that specified by format.

SQL > select to_char (to_date ('05-June-2016 12-12-8-8-8-8-12-8-12-8-12-12-8-12-12-8-12-12-8-12-12-8-12-8-12-12-8-12-12-8-8-12-8-12-12-8-12-8-12-12-8-8-12-12-8-8-8-12-8-12-12-8-8-12-12-8-8-12-12-8-8-8-12-8-12-8-8-12-12-8-12-12-13-12-14-12-12-13-12-13-12-12-13-12-12-8-12-12-8-12-12-12-12-12-

Sets the default date format. The default date format is specified in the database parameter NLS_DATE_FORMAT. You can modify it with the following statement.

SQL > alter session set nls_date_format = 'YYYY-MM-DD';Session altered

2. Use the time value function

The time value function is used to obtain or process time values and timestamps.

Function

Description

ADD_MONTHS (XBI y)

Returns the result of x plus y months. If y is negative, subtract y months from x.

LAST_DAY (x)

Returns the last day of the month containing x

MONTHS_BETWEEN (XBI y)

Returns a few months between x and y. If x > y returns a positive number, otherwise returns a negative number.

NEXT_DAY (XBJ day)

Returns the time value from x to the next day; day is a text string

ROUND (x [, unit])

Round up x.

SYSDATE

Returns the current time value set in the operating system where the database server resides

TRUNC (x [, unit])

Truncate x. By default, x is truncated to the start time of the day.

SQL > select sysdate from dual;SYSDATE-2016/6/9 12SQL > select add_months (sysdate,1) from dual;ADD_MONTHS (SYSDATE,1)-2016-7-9 12:52:03SQL > select add_months (sysdate,-1) from dual ADD_MONTHS (SYSDATE,-1)-2016-5-9 12:52:15SQL > select last_day (to_char (sysdate)) from dual;LAST_DAY (TO_CHAR (SYSDATE))-2016/6/30SQL > select months_between ('09-06-2016) from dual Select months_between ('09-06-2016) from dualORA-01861: text and format string do not match SQL > select months_between ('2016-06-09) from dual MONTHS_BETWEEN ('2016-06-09) from dual-- 3.96774193548387SQL > select months_between MONTHS_BETWEEN ('2016-10-09)

3. Use timestamp

CURRENT_TIMESTAMP, LOCALTIMESTAMP, and SYSTIMESTAMP return the date and time and time zone of the current reply

EXTRACT extracts from the timestamp type or DATE type and returns the year, month, day, hour, minute, second or period

TO_TIMESTAMP (x [, format]) is used to convert the string x to a TIMESTAMP type, and format specifies the conversion type

4. Use interval INTERVAL

For example: the validity period of coupons, the time of discount promotion

Types

Description

INTERVAL_YEAR [(years_option)] TO_MONTH

Stores a time interval in years and months; the precision of the year can be specified through the years_option parameter, and the default precision is 2, meaning that two digits can be stored for the number of years in the interval. If you try to add a record to the table that has more years than the INTERVAL_YEAR TO MONTH column can store, an error is returned. The time interval can store either positive or negative numbers

INTERVAL_DAY [(days_option)] TO_SECOND [(seconds_precision)]

Stores an interval in days and seconds; you can specify the precision of the day by specifying the optional days_precision parameter, which is an integer of 0 to 9. The default value is 2.

Use INTERVAL_YEAR TO MONTH types

Create the table coupons to store coupon information. Duration is used to record the time interval when the coupon is valid.

SQL > create table coupons (2 coupon_id integer constraint coups_pk primary key, 3 name varchar2 (30) not null, 4 duration INTERVAL YEAR (3) TO MONTH 5); Table created

To provide an INTERVAL YEAR TO MONTH literal for the database, you can use the following syntax:

INTERVAL'[+ | -] [y] [- m]'[YEAR [(years_precisiion)]] [TO_MONTH]

Example:

Literal amount of time interval

Description

INTERVAL'1' YEAR

INTERVAL '11' MONTH

The interval is 11 months (14 > 12 = 1 year and 2 months)

INTERVAL'1-3 'YEAR TO MONTH

The interval is one year and three months.

INTERVAL'0-5' YEAR TO MONTH

The interval is 0 years and 5 months.

INTERVAL'- 1-5' YEAR TO MONTH

The interval is negative and the value is 1 year and 5 months.

SQL > INSERT INTO coupons (coupon_id,name,duration) 2 values (1 off values); 1 row insertedSQL > INSERT INTO coupons (coupon_id,name,duration) 2 values (2 off Pop 3 MONTH); 1 row insertedSQL > INSERT INTO coupons (coupon_id,name,duration) values (3 off Pop 31 MONTH); 1 row insertedSQL > select * from coupons COUPON_ID NAME DURATION -1 $1 off Z Files + 001-00 2 $2 off Pop 3 + 000-11 3 $2 off Pop 31 + 000-02

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.