Oracle PL/SQL/Date Timezone/Timestamp

Материал из SQL эксперт
Перейти к: навигация, поиск

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>