Oracle PL/SQL/Date Timezone/SYSTIMESTAMP

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

  
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> --

Get the SYSTIMESTAMP

 
SQL> --SYSTIMESTAMP()
SQL>
SQL> SELECT SYSTIMESTAMP FROM dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
16-SEP-06 05.35.06.531000 PM -07:00
SQL>

Use systimestamp in insert statement

  
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> --

Using systimestamp to do benchmark

   
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.