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
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>
Compare data and timestamp
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>
Create a table with two columns: "timestamp with time zone", "c2 timestamp with local time zone"
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> --
INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals
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>
TIMESTAMP(4) WITH TIME ZONE
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>
TIMESTAMP specifies a precision for the SECONDS field in a TIMESTAMP column (the DATE data type can only store whole seconds).
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.
TIMESTAMP WITH LOCAL TIME ZONE
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>
TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone
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>
Use the Timestamp data type in a table and insert data
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>
Use the TIMESTAMP type to define a column in a table
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;
Use timestamp as table column type and insert sysdate to it
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>
Use TIMESTAMP to mark string in insert statement
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.