The time zone function of the SQL Foundation (21)

Use a data type to store the time difference between two datetime 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 types

Data type	Range
TIMESTAMP	Years, months, days, hours, minutes, seconds and seconds of the small number of parts
TIMESTAMP with Time ZONE	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, the type is converted to the database's time zone to hold the data, that is, the time the database is saved is the local time zone of the database, and when the user accesses the database, Oracle automatically converts that time to the current client's time

TIMESTAMP Field

datetime   field
year	–4712 to 9999 (excluding 0 years)
month	01 to
day	01 to +
hour	00 to +
minute	00 to the
second	00 to 59.9 (n)--Note: 9 (n) for precision
timezone_hour	-12 to 14
timezone_minute	00 to

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;

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8F/3F/wKiom1jYq7iSohpYAAAYKbYAM_A405.jpg "title=" Qq20170327140526.jpg "alt=" Wkiom1jyq7isohpyaaaykbyam_a405.jpg "/>

ALTER TABLE employees modify hire_date timestamp;

Select hire_date from Employees;

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/8F/3F/wKiom1jYrBbApMmGAAA5RJHsG70584.jpg "title=" Qq20170327140526.jpg "alt=" Wkiom1jyrbbapmmgaaa5rjhsg70584.jpg "/>

• Current_date:
  

– Returns the current date from a user session

– The date data type is returned

• Current_timestamp:

– Returns the current date and time from a user session

– The timestamp with time zone data type is returned

• Localtimestamp:

– Returns the current date and time from a user session

– The timestamp data type is returned

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

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 = ' -5: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

To display the database time zone:

Select Dbtimezone from dual;

Dbtimezone

------------------

+00:00

To display the session time zone:

Select Sessiontimezone from dual;

INTERVAL Data types

The INTERVAL data type is used to store a difference of two dates.

There are two types of intervals:

–year-month

–day-time

The accuracy of the time interval:

– The interval of the actual range subset

– The specified time interval

Data type	Range
INTERVAL year to MONTH	Year, month
INTERVAL Day to SECOND	Days, hours, minutes, seconds, and number of decimal parts

INTERVAL Range

Interval Range	Interval valid value
Year	Any positive, negative integer
MONTH	XX to 11
Day	Any positive, negative integer
HOUR	XX to 23
MINUTE	XX to 59
SECOND	XX to 59.9 (n) – Note: 9 (n) for accuracy

INTERVAL year to MONTH: example

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 ' year (3));

INSERT into warranty values (678, ' 200-11 ');

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, ' 90 00:00:00 ');

INSERT into lab values (56098,

Interval ' 6 03:30:16 ' day to second);

EXTRACT

Show year from Sysdate:

Select Extract (year from sysdate) from dual;

month of Hire_date showing manager_id of 100 employees:

Select Last_Name, Hire_date, extract (month from hire_date) from employees

where manager_id = 100;

Tz_offset

Show UTC with ' Us/eastern ' (US/East), ' Canada/yukon ' (Canada/Yukon) and ' Europe/london ' (Europe/London) time zone offsets

Select Tz_offset (' Us/eastern '),

Tz_offset (' Canada/yukon '),

Tz_offset (' Europe/london ')

from dual;

From_tz

The TIMESTAMP value ' 2000-03-28 08:00:00 ' time zone is displayed as ' Australia/north ' (Australia/North), TIMESTAMP with the zone value.

Select From_tz (timestamp ' 2000-07-12 08:00:00 ', ' Australia/north ') from dual;

To_timestamp

Displays the TIMESTAMP value of the string ' 2007-03-06 11:00:00 ':

Select To_timestamp (' 2007-03-06 11:00:00 ', ' yyyy-mm-dd HH:MI:SS ') from dual;

To_yminterval

Displays the date of the hire date 1 years and 2 months after the employee of DEPARTMENT_ID 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 100 days and 10 hours of employment for all employees

Select Last_Name,

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

To_char (hire_date +

To_dsinterval (' 100 10:00:00 '),

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

From employees;

Daylight saving time

First Sunday of April

–time jumps from 01:59:59 am to 03:00:00 am.

–values from 02:00:00 am to 02:59:59 am is 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

Last Sunday of October

–time jumps from 02:00:00 am to 01:00:01 am.

–values from 01:00:01 am to 02:00:00 am is ambiguous

Because they is visited twice.

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

– Values from 01:00:01 to 02:00:00 are unclear as they went twice

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910754

The time zone function of the SQL Foundation (21)