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

Use data types to store the time difference between two date-time values

Use the following date-time functions:

-CURRENT_DATE

-CURRENT_TIMESTAMP

-LOCALTIMESTAMP

-DBTIMEZONE

-SESSIONTIMEZONE

-EXTRACT

-TZ_OFFSET

-FROM_TZ

-TO_TIMESTAMP

-TO_YMINTERVAL

-TO_DSINTERVAL

TIME_ZONE can be set to:

Absolute offset

Time zone of the database

OS local time zone

Zone domain name

Alter session set time_zone ='- 05 00'

Alter session set time_zone = dbtimezone

Alter session set time_zone = local

Alter session set time_zone = 'America/New_York'

TIMESTAMP data type

Data type range TIMESTAMP decimal part of year, month, day, hour, minute, second and second TIMESTAMP WITH TIMEZONE is the same as TIMESTAMP data type; also includes: TIMEZONE_HOUR,TIMEZONE_MINUTE or TIMEZONE_REGION

TIMESTAMP WITH LOCAL TIME ZONE

The storage type is similar to TIMESTAMP. When the user submits the time to the database, this type will be converted to the time zone of the database to save the data, that is, the time saved by the database is the local time zone of the database. When the user accesses the database, oracle will automatically convert that time to the time of the current client.

TIMESTAMP field

Datetime field valid value YEAR-4712 to 9999 (excluding 0 years) MONTH01 to 12DAY01 to 31HOUR00 to 23MINUTE00 to 59SECOND00 to 59.9 (N)-Note: 9 (N) is precision TIMEZONE_HOUR-12 to 14TIMEZONE_MINUTE00 to 59

Create table web_orders (order_date timestamp with time zone,delivery_time timestamp with local time zone)

Insert into web_orders values (current_date, current_timestamp + 2)

Select * from web_orders

The difference between DATE and TIMESTAMP

Select hire_date from employees

Alter table employees modify hire_date timestamp

Select hire_date from employees

CURRENT_DATE:

-returns the current date from the user session

-the DATE data type is returned

CURRENT_TIMESTAMP:

-returns the current date and time from the user session

-the TIMESTAMP WITH TIME ZONE data type is returned

LOCALTIMESTAMP:

-returns the current date and time from the user session

-the TIMESTAMP data type is returned

Compare the date and time of the time zone of the session

Set the parameter TIME_ZONE to-5:00, and then use the SELECT statement to see the difference comparison for each date and time.

Alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'

Alter session set time_zone ='- 5VR 00'

Select sessiontimezone, current_date from dual

SESSIONTIMEZONE CURRENT_DATE

-

-05:00 27-3-2017 01:12:37

Select sessiontimezone, current_timestamp from dual

SESSIONTIMEZONE CURRENT_TIMESTAMP

-

-05:00 27-3-17 01.13.23.473132-05:00

Select sessiontimezone, localtimestamp from dual

SESSIONTIMEZONE LOCALTIMESTAMP

-

-05:00 27-3-17 01.14.06.470998

DBTIMEZONE and SESSIONTIMEZONE

Display the database time zone:

Select dbtimezone from dual

DBTIMEZONE

-

+ 00:00

Displays the session time zone:

Select sessiontimezone from dual

INTERVAL data type

The INTERVAL data type is used to store the difference between two dates.

There are two types of intervals:

-Year-month

-Day-time

Precision of time interval:

-the interval formed by a subset of the actual scope

-specified time interval

Data type range INTERVAL YEAR TO MONTH year, month INTERVAL DAY TO SECOND days, hours, minutes, seconds and decimal parts

INTERVAL range

INTERVAL range interval valid value YEAR any positive or negative integer MONTH00 to 11DAY any positive or negative integer HOUR00 to 23MINUTE00 to 59SECOND00 to 59.9 (N)-Note: 9 (N) is precision

INTERVAL YEAR TO MONTH: exampl

Create table warranty (prod_id number, warranty_time interval year (3) to month)

Insert into warranty values (123, interval'8' month)

Insert into warranty values (155, interval '200' year (3))

Insert into warranty values (678, 20011)

Select * from warranty

INTERVAL DAY TO SECOND example

Create table lab (exp_id number, test_time interval day (2) to second)

Insert into lab values (100012, '9000, 9000, 0015, 0015)

Insert into lab values (56098

Interval '603 3015 day to second)

EXTRACT

Display the year from SYSDATE:

Select extract (year from sysdate) from dual

Displays the month of the HIRE_DATE for the employee with a MANAGER_ID of 100:

Select last_name, hire_date, extract (month from hire_date) from employees

Where manager_id = 100

TZ_OFFSET

Displays time zone offsets for UTC and 'US/Eastern' (US / East),' Canada/Yukon' (Canada / Yukon) and 'Europe/London' (Europe / London)

Select tz_offset ('us/eastern')

Tz_offset ('canada/yukon')

Tz_offset ('europe/london')

From dual

FROM_TZ

Displays the TIMESTAMP value '2000-03-28 08 TIMESTAMP WITH TIME 00' time zone as' Australia/North' (Australia / North), and the ZONE value.

Select from_tz (timestamp '2000-07-1208 australia/north') from dual

TO_TIMESTAMP

Displays the TIMESTAMP value of the string '2007-03-06 1100-001-12':

Select to_timestamp ('2007-03-06 1100VlV 00mm HH:MI:SS') from dual

TO_YMINTERVAL

Displays the date 1 year and 2 months after the employment date of the employee with a DEPARTMENT_ID of 20.

Select hire_date,hire_date + TO_YMINTERVAL ('01-02') AS

HIRE_DATE_YMININTERVAL

From employees where department_id = 20

TO_DSINTERVAL

Displays the date of employment of all employees for 100 days and 10 hours.

Select last_name

TO_CHAR (hire_date, 'mm-dd-yy:hh:mi:ss') hire_date

TO_CHAR (hire_date +)

TO_DSINTERVAL ('100, 10, 100, 100, 10, 00, 00, 00, 100, 10, 100, 10, 00, 00, 00, 100, 100, 10, 100, 10, 00, 00, 00, 100, 10, 100, 100, 10, 100, 100, 10, 100, 100, 10, 00, 00, 00, 100, 100, 10, 100, 100, 10, 00, 00, 00, 00, 100, 10, 100, 100, 10, 100, 100, 10, 100, 100, 10, 00, 00, 00, 00, 00).

'mm-dd-yy:hh:mi:ss') hiredate2

From employees

Daylight saving time

The first Sunday in April

-Time jumps from 01:59:59 AM to 03:00:00 AM.

-Values from 02:00:00 AM to 02:59:59 AM are not valid.

-time jumps from 01:59:59 to 03:00:00

-the value is not valid from 02:00:00 to 02:59:59

The last Sunday in October

-Time jumps from 02:00:00 AM to 01:00:01 AM.

-Values from 01:00:01 AM to 02:00:00 AM are ambiguous

Because they are visited twice.

Time jumps from 02:00:00 to 01:00:01

The value from 01:00:01 to 02:00:00 is not clear because they have been there twice

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.