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

1. Set time zone 1. Database time zone

A) modify

ALTER DATABASE SET TIME_ZONE ='- 05 00'

ALTER DATABASE SET TIME_ZONE = dbtimezone

ALTER DATABASE SET TIME_ZONE = local

ALTER DATABASE SET TIME_ZONE = 'America/New_York'

B) Verification

SELECT DBTIMEZONE FROM DUAL

2. Session time zone

A) modify

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'

B) Verification

SELECT SESSIONTIMEZONE FROM DUAL

Second, time data type function description CURRENT_DATE syntax: CURRENT_DATE () description: query the current time

Current_date returns the current session time, while sysdate returns the server time.

Current_date is sometimes one second faster than sysdate, which may be the result of rounding.

If you change the time zone of the current session, such as changing China's time zone to East Zone 8 and East Zone 9, the time displayed by current_date is East Zone Nine. According to the principle of east plus west subtraction, current_date should be one hour faster than sysdate. Example:

Select current_date, sysdate from dual;CURRENT_TIMESTAMP syntax: CURRENT_TIMESTAMP () description: returns the current date example sentence in the current session time zone as a timestamp with time zone data type:

SELECT CURRENT_TIMESTAMP

FROM DUAL;LOCALTIMESTAMP syntax: LOCALTIMESTAMP () description: returns the current date example sentence in the current session time zone as a local timestamp data type:

SELECT LOCALTIMESTAMP

FROM DUAL;EXTRACT syntax:

EXTRACT (

{YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

| | {TIMEZONE_HOUR | TIMEZONE_MINUTE} |

| | {TIMEZONE_REGION | TIMEZONE_ABBR} |

FROM {date_value | interval_value}) description:

We can only intercept year,month,day from one date type (the format of date date is yyyy-mm-dd)

We can only intercept TIMEZONE_HOUR and TIMEZONE_MINUTE; example sentences from a data type of timestamp with time zone:

Example 1:

SELECT EXTRACT (year FROM DATE '2011-05-17') "year"

EXTRACT (month FROM DATE '2011-05-17') "month"

EXTRACT (day FROM DATE '2011-05-17') "day"

Example 2 of FROM DUAL;:

SELECT EXTRACT (day FROM dt2-dt1) "day"

EXTRACT (hour FROM dt2-dt1) "hour"

EXTRACT (minute FROM dt2-dt1) "minute"

EXTRACT (second FROM dt2-dt1) "second"

FROM (SELECT TO_TIMESTAMP ('2011-02-04 15 purl 07VR 00')

'yyyy-mm-dd hh34:mi:ss') dt1

TO_TIMESTAMP ('2011-05-17 1914 08VR 46'

'yyyy-mm-dd hh34:mi:ss') dt1

FROM DUAL); FROM_TZ syntax: FROM_TZ (XJI timestamp zone) description: converts the specified timestamp and date into a timestamp with time zone type example sentence:

SELECT FROM_TZ (TO_TIMESTAMP ('19880719)

'America/Sao_Paulo')

FROM DUAL;TO_DSINTERVAL syntax: TO_DSINTERVAL (C) description: converts a string C that conforms to a specific format to a data example sentence of type INTERVALDAY TO SECOND:

SELECT SYSDATE + TO_DSINTERVAL ('1000VR 001R 00') A

FROM DUAL;TO_TIMESTAMP syntax: TO_TIMESTAMP (x [, format]) states that you can use format to specify format example sentences to convert string expression X to timestamp type:

SELECT TO_TIMESTAMP ('19880719)

FROM DUAL;TO_TIMESTAMP_TZ syntax: TO_TIMESTAMP_TZ (x [, format]) states that you can use format to specify format example sentences to convert string expression X to timestamp with time zone type:

SELECT TO_TIMESTAMP_TZ ('1988-07-19 07 Vista 10 10 12 320'

'YYYY-MM-dd HH24:MI:SS.FF') A

FROM DUAL;TO_YMINTERVAL syntax: TO_YMINTERVAL (C) description: converts a string C that conforms to a specific format to a data example sentence of type INTERVALYEAR TO MONTH:

SELECT SYSDATE+TO_YMINTERVAL ('01-13') A

FROM DUAL;TZ_OFFSET syntax: TZ_OFFSET (time_zone) description: return the offset time example sentence of time_zone according to the minimum ha minutes:

SELECT TZ_OFFSET ('America/New_York')

FROM DUAL

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.