Oracle PL/SQL/Date Timezone/EXTRACT
Содержание
- 1 EXTRACT(DAY FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))
- 2 EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP()
- 3 EXTRACT(MONTH FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))
- 4 EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x
- 5 EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
- 6 EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM")
- 7 EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM"))
- 8 EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
- 9 select extract( year from add_months(sysdate,36) ) "3 Years Out"
- 10 The EXTRACT() function returns the value of a specified datetime field from a datetime or interval value expression.
EXTRACT(DAY FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))
<source lang="sql">
SQL> SQL> select EXTRACT(DAY FROM TO_DATE("01-JAN-2005 19:15:26",
2 "DD-MON-YYYY HH24:MI:SS")) AS DAY from dual; DAY
1
SQL>
</source>
EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP()
<source lang="sql">
SQL> SQL> SQL> --EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP(): SQL> SQL> SELECT
2 EXTRACT(HOUR FROM TO_TIMESTAMP("01-JAN-2005 19:15:26", 3 "DD-MON-YYYY HH24:MI:SS")) AS HOUR, 4 EXTRACT(MINUTE FROM TO_TIMESTAMP("01-JAN-2005 19:15:26", 5 "DD-MON-YYYY HH24:MI:SS")) AS MINUTE, 6 EXTRACT(SECOND FROM TO_TIMESTAMP("01-JAN-2005 19:15:26", 7 "DD-MON-YYYY HH24:MI:SS")) AS SECOND 8 FROM dual; HOUR MINUTE SECOND
---------- ----------
19 15 26
SQL>
</source>
EXTRACT(MONTH FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))
<source lang="sql">
SQL> SQL> select EXTRACT(MONTH FROM TO_DATE("01-JAN-2005 19:15:26",
2 "DD-MON-YYYY HH24:MI:SS")) As MONTH from dual; MONTH
1
SQL>
</source>
EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x
<source lang="sql">
SQL> SQL> SQL> -- EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x; SQL> SQL> SELECT EXTRACT(YEAR FROM TO_DATE("01-JAN-2005 19:15:26","DD-MON-YYYY HH24:MI:SS")) AS YEAR FROM dual;
YEAR
2005
SQL>
</source>
EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
<source lang="sql">
SQL> SQL> select EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(
2 "01-JAN-2005 19:15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR")) 3 AS TZA from dual;
TZA
PST SQL>
</source>
EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM")
<source lang="sql">
SQL> SQL> --EXTRACT(): get the time zone hour, minute, second, region, and region abbreviation from a TIMESTAMP WITH TIMEZONE returned by TO_TIMESTAMP_TZ(): SQL> SQL> SELECT
2 EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ( 3 "01-JAN-2005 19:15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM")) 4 AS TZH 5 FROM dual; TZH
-7
SQL> SQL>
</source>
EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM"))
<source lang="sql">
SQL> SQL> SQL> select EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(
2 "01-JAN-2005 19:15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM")) 3 AS TZM from dual; TZM
-15
</source>
EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
<source lang="sql">
SQL> SQL> select EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(
2 "01-JAN-2005 19:15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR")) 3 AS TZR from dual;
TZR
PST
</source>
select extract( year from add_months(sysdate,36) ) "3 Years Out"
<source lang="sql">
SQL> SQL> select extract( year from add_months(sysdate,36) ) "3 Years Out"
2 from dual;
3 Years Out
2011
1 row selected. SQL> SQL> SQL> --
</source>
The EXTRACT() function returns the value of a specified datetime field from a datetime or interval value expression.
<source lang="sql">
SQL> Syntax: EXTRACT([[YEAR]|[MONTH]|[DAY]|[HOUR]|[MINUTE]|[SECOND]]| [[TIMEZONE_HOUR]|[TIMEZONE_MINUTE]|[TIMEZONE_REGION]|[TIMEZONE_ABBR]) SQL> SQL> SQL> SELECT
2 EXTRACT(YEAR FROM SYSDATE) current_year 3 FROM dual;
CURRENT_YEAR
2008
SQL> SQL>
</source>