Oracle PL/SQL/Date Timezone/SYSTIMESTAMP

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

Display the sessions current time/timezone AND the Operating systems time/timezone in PL/SQL code block

   <source lang="sql">
 

SQL> -- SQL> SQL> SQL> begin

 2  dbms_output.put_line("current_timestamp (session) "|| current_timestamp);
 3  dbms_output.put_line("systimestamp (database) "||systimestamp);
 4  end;
 5  /

current_timestamp (session) 16-JUN-08 04.36.26.959000000 PM -08:00 systimestamp (database) 16-JUN-08 05.36.26.959000000 PM -07:00 PL/SQL procedure successfully completed. SQL> SQL> --

 </source>
   
  


Get the SYSTIMESTAMP

   <source lang="sql">

SQL> --SYSTIMESTAMP() SQL> SQL> SELECT SYSTIMESTAMP FROM dual; SYSTIMESTAMP


16-SEP-06 05.35.06.531000 PM -07:00 SQL>


 </source>
   
  


Use systimestamp in insert statement

   <source lang="sql">
 

SQL> create table tstz

 2  (c1 timestamp with time zone,
 3   c2 timestamp with time zone,
 4   c3 timestamp with time zone);

Table created. SQL> SQL> SQL> insert into tstz (c1,c2,c3)values( systimestamp ,systimestamp ,systimestamp); 1 row created. SQL> SQL> select * from tstz; C1


C2


C3


16-JUN-08 05.40.39.576000 PM -07:00 16-JUN-08 05.40.39.576000 PM -07:00 16-JUN-08 05.40.39.576000 PM -07:00

1 row selected. SQL> SQL> drop table tstz; Table dropped. SQL> --

 </source>
   
  


Using systimestamp to do benchmark

   <source lang="sql">
  

SQL> SQL>CREATE TABLE project (

 2    pro_id              NUMBER(4),
 3    pro_name            VARCHAR2(40),
 4    budget          NUMBER(9,2),
 5    CONSTRAINT project_pk   PRIMARY KEY (pro_id)
 6  );

Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage (

 2    pro_id                   NUMBER(4),
 3    emp_id                  NUMBER,
 4    time_log_date                DATE,
 5    hours_logged                 NUMBER(8,2),
 6    dollars_charged              NUMBER(8,2),
 7    CONSTRAINT server_usage_pk  PRIMARY KEY (pro_id, emp_id, time_log_date)
 8  );

Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);

1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);

1 row created. SQL> SQL>CREATE TABLE emp (

 2    emp_id               NUMBER,
 3    ename             VARCHAR2(40),
 4    hire_date        DATE DEFAULT sysdate,
 5    end_date DATE,
 6    rate     NUMBER(5,2),
 7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
 8  );

Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL>--Obtain the starting time SQL>COLUMN start_time NEW_VALUE start SQL>SELECT systimestamp(9) start_time FROM dual; User System Privileges Page 1 START_TIME


26-OCT-09 11.02.43.953000000 AM -08:00 1 row selected. SQL> SQL>--Do some work that you want to measure SQL>SELECT COUNT(*) FROM emp; User System Privileges Page 1

 COUNT(*)

       10

1 row selected. SQL>SELECT COUNT(*) FROM project; User System Privileges Page 1

 COUNT(*)

        5

1 row selected. SQL>SELECT COUNT(*) FROM server_usage; User System Privileges Page 1

 COUNT(*)

        7

1 row selected. SQL> SQL>--Grab the ending time SQL>COLUMN end_time NEW_VALUE end SQL>SELECT systimestamp(9) end_time FROM dual; User System Privileges Page 1 END_TIME


26-OCT-09 11.02.44.313000000 AM -08:00 1 row selected. SQL> SQL>--Compute the elapsed time SQL>COLUMN elapsed_time NEW_VALUE elapsed SQL>SELECT TO_TIMESTAMP_TZ("&end")

 2         - TO_TIMESTAMP_TZ("&start") elapsed_time
 3  FROM dual;

User System Privileges Page 1 ELAPSED_TIME


+000000000 00:00:00.360000000 1 row selected. SQL> SQL>--Display the elapsed time SQL>PROMPT The elapsed time for the SELECTs was: &elapsed The elapsed time for the SELECTs was: +000000000 00:00:00.360000000 SQL> SQL> SQL>drop table emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.


 </source>