Oracle date and time is stored in processing

The contents of this chapter include:

• Processes and stores a specific date and time. The date type can store century, 4-bit years, months, days, hours, minutes, and seconds

• With timestamp timestamp, the timestamp can store a specific date and time. The advantage of timestamps is that you can store seconds with decimal digits, and you can store time zones

• The time interval can be used to store the length of time interval. such as 1 years and 3 months.

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

• DD is a two-bit number of days

• Mon is the first three letters of the month, such as the Feb

• The YYYY is a 4-bit year.

By default, the database returns the date in dd-mon-yy format, where YY is the last two bits 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 form 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)----------------September-June-16

To_date (X[,format]) is used to convert a string x into a time value, which can specify an optional string format parameter to describe the form of x. If you do not specify the format parameter, the date is in the default database format. The format of x is the same as the format specified by format.

Sql> Select To_char (to_date (' May-June -2016 12:38:23 ', ' dd-mon-yyyy HH24:MI:SS '), ' HH24:MI:SS ') as time 2 from dual; Time--------12:38:23

Sets the default date format. The default date format is specified in the database parameter Nls_date_format. You can use the following statement to modify it.

Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd '; Session Altered

2. Using the time value function

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

Function	Description
Add_months (x, y)	Returns the result of x plus y months, minus y months from X if Y is a negative number
Last_day (x)	Returns the last day of the month that contains X
Months_between (x, y)	Returns a couple of months between X and Y. Returns a negative number if x>y returns a positive number.
Next_day (X,day)	Returns the time value from X, to the next day, or a text string
ROUND (X[,unit])	Take an integer to X.
Sysdate	Returns the current time value set in the operating system where the database server resides
TRUNC (X[,unit])	Truncates the X. By default, X is truncated to the start time of the current 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 (' September-June-2016 ', ' August-October -2016 ')  from dual;select months_between (' September-June-2016 ', ' August-October -2016 ')   from dualora-01861:  text does not match the format string sql> select months_between (' 2016-06-09 ', ' 2016-10-08 ')  from dual; Months_between (' 2016-06-09 ', ' 2------------------------------              -3.96774193548387sql> select months_between (' 2016-10-09 ', ' 2016-06-09 ')  from Dual Months_between (' 2016-10-09 ', ' 2------------------------------                               4

3. Use time stamp

Current_timestamp, Localtimestamp, Systimestamp returns the date and time of the current reply, and the time zone

EXTRACT is extracted from a timestamp type or date type and returns the day of the month, minute or period

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

4, use time interval interval

Example: Coupon validity, time of sale

Type	Description
interval_year[(years_option)] To_month	Stores a time interval whose units are years and months; the precision of the year can be specified by the Years_option parameter, with the default precision of 2, which means that you can store two digits for the number of years in the interval. An error is returned if you attempt to add a number of years to a table that exceeds the records that the Interval_year to month column can store. The time interval can store positive numbers or negative values
interval_day[(days_option)] to_second[(seconds_precision)]	Stores a time interval whose units are days and seconds; You can specify the optional days_precision parameter to specify the precision of the day, which is an integer of 0~9. The default value is 2.

Use Interval_year to month type

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

sql> CREATE table coupons (2 coupon_id integer constraint COUPS_PK primary key, 3 name VARCHAR2 (+) not NULL, 4 D Uration INTERVAL Year (3) to MONTH 5); Table created

To provide a database with a interval year to month literal, you can use the following syntax:

INTERVAL ' [+|-][y][-m] ' [year[(years_precisiion)] [To_month]

Example:

tr>

interval ' 1 ' year
interval ' MONTH
interval ' 1-3 ' year to MONTH
interval ' 0-5 ' year to MONTH
interval ' -1-5 ' year to month

Sql> insert into coupons (coupon_id,name,duration)   2  values (1, ' $1  Off z files ',interval  ' 1 '  year); 1 row insertedsql> insert into  coupons (coupon_id,name,duration)   2  values (2, ' $2 off pop 3 ', INTERVAL   '  month '); 1 row insertedsql> insert into coupons (Coupon_id,name, Duration)  values (3, ' $2 off pop 31 ',interval  ' 2 '  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

This article is from the "Ah Cool blog source" blog, please make sure to keep this source http://aku28907.blog.51cto.com/5668513/1788951

Oracle date and time is stored in processing