Oracle PL/SQL/Date Timezone/SYSTIMESTAMP
Содержание
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>