Oracle PL/SQL/Date Timezone/Timestamp
Содержание
- 1 Cast string to TIMESTAMP
- 2 Compare data and timestamp
- 3 Create a table with two columns: "timestamp with time zone", "c2 timestamp with local time zone"
- 4 INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals
- 5 TIMESTAMP(4) WITH TIME ZONE
- 6 TIMESTAMP specifies a precision for the SECONDS field in a TIMESTAMP column (the DATE data type can only store whole seconds).
- 7 TIMESTAMP WITH LOCAL TIME ZONE
- 8 TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone
- 9 Use the Timestamp data type in a table and insert data
- 10 Use the TIMESTAMP type to define a column in a table
- 11 Use timestamp as table column type and insert sysdate to it
- 12 Use TIMESTAMP to mark string in insert statement
Cast string to TIMESTAMP
<source lang="sql">
SQL> SQL> create table MyTable (
2 title varchar2(100), 3 phone varchar2(20), 4 place varchar2(100), 5 starts timestamp with time zone);
Table created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Sales", "999.123.4567", "Washington",TIMESTAMP "2001-12-01 15:00:00.000000 EST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Product", "000.123.4567", "San Francisco",TIMESTAMP "2001-12-01 17:00:00.000000 PST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Highlights", "111 1234 5678", "London", TIMESTAMP "2001-12-01 20:00:00.000000 GMT");
1 row created. SQL> SQL> select dbtimezone from dual; DBTIME
+00:00 SQL> SQL> select title, phone
2 from MyTable 3 where starts = TIMESTAMP "2001-12-01 15:00:00.000000 -5:00";
TITLE PHONE
--------------------
Sales 999.123.4567 Highlights 111 1234 5678 SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> SQL> SQL>
</source>
Compare data and timestamp
<source lang="sql">
SQL> -- use timestamp SQL> SQL> create table company_events (
2 event_name varchar2( 100 ), 3 event_date date, 4 event_timestamp timestamp ) 5 /
Table created. SQL> SQL> insert into company_events ( event_name, event_date, event_timestamp )
2 values ( "Name 1", sysdate, sysdate ) 3 /
1 row created. SQL> column event_name format a28 SQL> column event_date format a18 SQL> column event_timestamp format a28 SQL> SQL> select event_name, to_char(event_date, "DD-MON-YY HH24:MI:SS") event_date,
2 event_timestamp 3 from company_events 4 /
EVENT_NAME EVENT_DATE EVENT_TIMESTAMP
------------------ ----------------------------
Name 1 09-SEP-06 16:12:40 09-SEP-06 04.12.40.000000 PM SQL> SQL> SQL> drop table company_events; Table dropped. SQL> SQL>
</source>
Create a table with two columns: "timestamp with time zone", "c2 timestamp with local time zone"
<source lang="sql">
SQL> SQL> SQL> create table t
2 (c1 timestamp with time zone, 3 c2 timestamp with local time zone) 4 /
Table created. SQL> SQL> insert into t (c1,c2)values( current_timestamp, current_timestamp ); 1 row created. SQL> SQL> select * from t; C1
C2
16-JUN-08 07.01.25.376000 PM -06:00 16-JUN-08 07.01.25.376000 PM
1 row selected. SQL> SQL> alter session set time_zone = "-08:00"; Session altered. SQL> SQL> select * from t; C1
C2
16-JUN-08 07.01.25.376000 PM -06:00 16-JUN-08 05.01.25.376000 PM
1 row selected. SQL> SQL> drop table t; Table dropped. SQL> SQL> --
</source>
INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals
<source lang="sql">
SQL> SQL> -- INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals SQL> SQL> CREATE TABLE purchases_with_timestamp (
2 product_id INTEGER, 3 customer_id INTEGER, 4 made_on TIMESTAMP(4) 5 );
Table created. SQL> SQL> SQL> INSERT INTO purchases_with_timestamp ( product_id, customer_id, made_on )
2 VALUES ( 1, 1, TIMESTAMP "2005-05-13 07:15:31.1234");
1 row created. SQL> SQL> select * from purchases_with_timestamp; PRODUCT_ID CUSTOMER_ID MADE_ON
----------- ---------------------------------------------------------------------------
1 1 13-MAY-05 07.15.31.1234 AM
SQL> SQL> drop table purchases_with_timestamp; Table dropped. SQL> SQL>
</source>
TIMESTAMP(4) WITH TIME ZONE
<source lang="sql">
SQL> SQL> CREATE TABLE purchases_timestamp_with_tz (
2 product_id INTEGER, 3 customer_id INTEGER, 4 made_on TIMESTAMP(4) WITH TIME ZONE 5 );
Table created. SQL> SQL> INSERT INTO purchases_timestamp_with_tz (product_id, customer_id, made_on)
2 VALUES (1, 1, TIMESTAMP "2005-05-13 07:15:31.1234 -07:00");
1 row created. SQL> SQL> INSERT INTO purchases_timestamp_with_tz (product_id, customer_id, made_on)
2 VALUES (1, 2, TIMESTAMP "2005-05-13 07:15:31.1234 PST");
1 row created. SQL> SQL> SELECT * FROM purchases_timestamp_with_tz; PRODUCT_ID CUSTOMER_ID MADE_ON
----------- ---------------------------------------------------------------------------
1 1 13-MAY-05 07.15.31.1234 AM -07:00 1 2 13-MAY-05 07.15.31.1234 AM PST
SQL> SQL> SQL> drop table purchases_timestamp_with_tz; Table dropped. SQL>
</source>
TIMESTAMP specifies a precision for the SECONDS field in a TIMESTAMP column (the DATE data type can only store whole seconds).
<source lang="sql">
SQL> SQL> create table other_MyTable (
2 event_name varchar2( 100 ), 3 event_date date, 4 event_timestamp timestamp );
Table created. SQL> SQL> insert into other_MyTable( event_name, event_date, event_timestamp ) values( "A", sysdate, sysdate ); 1 row created. SQL> SQL> column event_name format a28 SQL> column event_date format a18 SQL> column event_timestamp format a28 SQL> SQL> select event_name, to_char(event_date, "DD-MON-YY HH24:MI:SS") event_date, event_timestamp
2 from other_MyTable;
EVENT_NAME EVENT_DATE EVENT_TIMESTAMP
------------------ ----------------------------
A 10-JUN-08 17:06:23 10-JUN-08 05.06.23.000000 PM SQL> SQL> drop table other_MyTable; Table dropped.
</source>
TIMESTAMP WITH LOCAL TIME ZONE
<source lang="sql">
SQL> SQL> create table MyTable (
2 title varchar2(100), 3 phone varchar2(20), 4 place varchar2(100), 5 starts timestamp with local time zone);
Table created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Sales Strategy", "212.123.4567", "New York", 3 TIMESTAMP "2001-12-01 15:00:00.000000 EST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Product Features", "650.123.4567", "San Francisco", 3 TIMESTAMP "2001-12-01 17:00:00.000000 PST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Football Highlights", "44 1234 5678", "London", 3 TIMESTAMP "2001-12-01 20:00:00.000000 GMT");
1 row created. SQL> SQL> alter session set time_zone = "-05:00"; Session altered. SQL> SQL> column title format a25 SQL> column starts format a30 SQL> select title, starts from MyTable; TITLE STARTS
------------------------------
Sales Strategy 01-DEC-01 03.00.00.000000 PM Product Features 01-DEC-01 08.00.00.000000 PM Football Highlights 01-DEC-01 03.00.00.000000 PM SQL> SQL> alter session set time_zone = "GMT"; Session altered. SQL> SQL> select title, starts from MyTable; TITLE STARTS
------------------------------
Sales Strategy 01-DEC-01 08.00.00.000000 PM Product Features 02-DEC-01 01.00.00.000000 AM Football Highlights 01-DEC-01 08.00.00.000000 PM SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone
<source lang="sql">
SQL> SQL> --TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone. SQL> SQL> SQL> CREATE TABLE purchases_timestamp_with_tz (
2 product_id INTEGER, 3 customer_id INTEGER, 4 made_on TIMESTAMP(4) WITH TIME ZONE 5 );
Table created. SQL> SQL> desc purchases_timestamp_with_tz;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NUMBER(38) CUSTOMER_ID NUMBER(38) MADE_ON TIMESTAMP(4) WITH TIME ZONE
SQL> SQL> drop table purchases_timestamp_with_tz; Table dropped. SQL> SQL>
</source>
Use the Timestamp data type in a table and insert data
<source lang="sql">
SQL> create table conference_calls (
2 title varchar2(100), 3 phone varchar2(20), 4 place varchar2(100), 5 starts timestamp with time zone) 6 /
Table created. SQL> SQL> insert into conference_calls (title, phone, place, starts)
2 values ("Sales Strategy", "212.123.4567", "Washington", 3 TIMESTAMP "2001-12-01 15:00:00.000000 EST") 4 /
1 row created. SQL> SQL> insert into conference_calls (title, phone, place, starts)
2 values ("Product Features", "650.123.4567", "San Francisco", 3 TIMESTAMP "2001-12-01 17:00:00.000000 PST") 4 /
1 row created. SQL> SQL> insert into conference_calls (title, phone, place, starts)
2 values ("Football Highlights", "44 1234 5678", "London", 3 TIMESTAMP "2001-12-01 20:00:00.000000 GMT") 4 /
1 row created. SQL> SQL> SQL> select * from conference_calls; TITLE PHONE
--------------------
PLACE STARTS
---------------------------------------------------------------------------
Sales Strategy 212.123.4567 Washington 01-DEC-01 03.00.00.000000 PM EST Product Features 650.123.4567 San Francisco 01-DEC-01 05.00.00.000000 PM PST Football Highlights 44 1234 5678 London 01-DEC-01 08.00.00.000000 PM GMT
SQL> SQL> select title, phone
2 from conference_calls 3 where starts = TIMESTAMP "2001-12-01 15:00:00.000000 -5:00" 4 /
TITLE PHONE
--------------------
Sales Strategy 212.123.4567 Football Highlights 44 1234 5678 SQL> SQL> drop table conference_calls; Table dropped. SQL> SQL>
</source>
Use the TIMESTAMP type to define a column in a table
<source lang="sql">
SQL> -- Use the TIMESTAMP type to define a column in a table SQL> SQL> CREATE TABLE purchases_with_timestamp (
2 product_id INTEGER , 3 customer_id INTEGER , 4 made_on TIMESTAMP(4) 5 );
Table created. SQL> SQL> SQL> desc purchases_with_timestamp;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NUMBER(38) CUSTOMER_ID NUMBER(38) MADE_ON TIMESTAMP(4)
SQL> SQL> drop table purchases_with_timestamp;
</source>
Use timestamp as table column type and insert sysdate to it
<source lang="sql">
SQL> SQL> create table events (
2 event_name varchar2( 100 ), 3 event_date date, 4 event_timestamp timestamp ) 5 /
Table created. SQL> SQL> SQL> insert into events ( event_name, event_date, event_timestamp )
2 values ( "COMPANY_EVENTS table", sysdate, sysdate );
1 row created. SQL> SQL> SQL> column event_name format a28 SQL> column event_date format a18 SQL> column event_timestamp format a28 SQL> SQL> SQL> select event_name, to_char(event_date, "DD-MON-YY HH24:MI:SS") event_date,event_timestamp
2 from events;
EVENT_NAME EVENT_DATE EVENT_TIMESTAMP
------------------ ----------------------------
COMPANY_EVENTS table 10-JUN-08 21:03:00 10-JUN-08 09.03.00.000000 PM SQL> SQL> drop table events; Table dropped. SQL> SQL>
</source>
Use TIMESTAMP to mark string in insert statement
<source lang="sql">
SQL> SQL> create table MyTable (
2 title varchar2(100), 3 phone varchar2(20), 4 place varchar2(100), 5 starts timestamp with time zone);
Table created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Sales", "999.123.4567", "Washington",TIMESTAMP "2001-12-01 15:00:00.000000 EST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Product", "000.123.4567", "San Francisco",TIMESTAMP "2001-12-01 17:00:00.000000 PST");
1 row created. SQL> SQL> insert into MyTable (title, phone, place, starts)
2 values ("Highlights", "111 1234 5678", "London", TIMESTAMP "2001-12-01 20:00:00.000000 GMT");
1 row created. SQL> SQL> select dbtimezone from dual; DBTIME
+00:00 SQL> SQL> select title, phone
2 from MyTable 3 where starts = TIMESTAMP "2001-12-01 15:00:00.000000 -5:00";
TITLE PHONE
--------------------
Sales 999.123.4567 Highlights 111 1234 5678 SQL> SQL> SQL> drop table MyTable; Table dropped.
</source>