Oracle PL/SQL/Data Type/TIMESTAMP
Содержание
- 1 A normalization to DB timezone
- 2 Assign current_timestamp to timestamp type variable
- 3 Create a table with two columns, Use Datatypes: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE
- 4 Define and set timestamp value
- 5 Define a TIMESTAMP variable with a default null or initialized value
- 6 Demonstrate the new ANSI Timestamp literal. Use 0-9 digits for fractional seconds
- 7 Difference between the DATE and TIMESTAMP datatypes:
- 8 gets the date a little more exact
- 9 Insert sysdate value to timestamp type column
- 10 Retrieve the timestamp into a variable
- 11 system timestamp
- 12 The TIMESTAMP WITH LOCAL TIME ZONE datatype stores the date/time
- 13 The TIMESTAMP WITH TIME ZONE datatype stores the date/time
- 14 TIMESTAMP(3)
- 15 TIMESTAMP literal
- 16 TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone
- 17 timestamp procedure member variable
- 18 timestamp type column
- 19 TIMESTAMP Value WITH LOCAL TIME ZONE
- 20 TIMESTAMP WITH TIME ZONE
- 21 Two timestamp subtypes demonstrate similar behaviors. Their prototypes are
- 22 versions between timestamp minvalue and maxvalue
A normalization to DB timezone
<source lang="sql">
SQL> SQL> DECLARE
2 3 v1 DATE; 4 v2 TIMESTAMP; 5 v3 TIMESTAMP WITH TIME ZONE; 6 v4 TIMESTAMP WITH LOCAL TIME ZONE; 7 8 BEGIN 9 10 11 v4 := TIMESTAMP "2002-11-03 03:00:00 -07:00"; 12 dbms_output.put_line(v4); 13 14 END; 15 /
03-NOV-02 02.00.00.000000 AM PL/SQL procedure successfully completed. SQL> SQL>
</source>
Assign current_timestamp to timestamp type variable
<source lang="sql">
SQL> SQL> declare
2 t1 timestamp := current_timestamp + 1 ; 3 i1 INTERVAL DAY TO SECOND; 4 begin 5 i1 := t1 - current_timestamp; 6 dbms_output.put_line(i1); 7 end; 8 /
+00 23:59:59.789000 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Create a table with two columns, Use Datatypes: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE
<source lang="sql">
SQL> SQL> SQL> SQL> create table t(
2 c1 timestamp with time zone, 3 c2 timestamp with local time zone 4 ) 5 /
Table created. SQL> SQL> drop table t; Table dropped. SQL>
</source>
Define and set timestamp value
<source lang="sql">
SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime TIMESTAMP := TIMESTAMP "2004-06-05 22:14:01"; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(myDateTime); 5 END; 6 /
05-JUN-04 10.14.01.000000 PM PL/SQL procedure successfully completed. SQL> SQL>
</source>
Define a TIMESTAMP variable with a default null or initialized value
<source lang="sql">
SQL> --var1 TIMESTAMP; -- Implicitly assigns a null value. SQL> --var2 TIMESTAMP := SYSTIMESTAMP; -- Explicitly assigns a value. SQL> --var3 TIMESTAMP(3); -- Explicitly sets precision for null value. SQL> --var4 TIMESTAMP(3) := SYSTIMESTAMP; -- Explicitly sets precision and value. SQL>
</source>
Demonstrate the new ANSI Timestamp literal. Use 0-9 digits for fractional seconds
<source lang="sql">
SQL> DECLARE
2 3 v1 DATE; 4 v2 TIMESTAMP; -- No time zone kept. 5 v3 TIMESTAMP WITH TIME ZONE; -- Preserves time zone entered. 6 v4 TIMESTAMP WITH LOCAL TIME ZONE; -- Converts session to DB time zone. 7 8 BEGIN 9 10 11 v2 := TIMESTAMP "2002-11-03 03:00:00.00"; 12 dbms_output.put_line(v2); 13 END; 14 /
03-NOV-02 03.00.00.000000 AM PL/SQL procedure successfully completed. SQL> SQL>
</source>
Difference between the DATE and TIMESTAMP datatypes:
<source lang="sql">
SQL> SQL> DECLARE
2 d DATE := SYSTIMESTAMP;
3 t TIMESTAMP(3) := SYSTIMESTAMP;
4 BEGIN
5 dbms_output.put_line("DATE ["||d||"]");
6 END;
7 /
DATE [26-OCT-09] PL/SQL procedure successfully completed. SQL>
</source>
gets the date a little more exact
<source lang="sql">
SQL> SQL> DECLARE
2 v_college_deadline TIMESTAMP;
3 BEGIN
4 v_college_deadline := TO_TIMESTAMP("06/06/2004", "DD/MM/YYYY") + INTERVAL "12-3" YEAR TO MONTH + INTERVAL "19 9:0:0.0" DAY TO SECOND;
5
6 DBMS_OUTPUT.PUT_LINE("My daughter leaves for college in " ||v_college_deadline);
7 END;
8 /
My daughter leaves for college in 25-SEP-16 09.00.00.000000 AM PL/SQL procedure successfully completed. SQL> SQL>
</source>
Insert sysdate value to timestamp type column
<source lang="sql">
SQL> SQL> SQL> create table myTable (ts timestamp); Table created. SQL> SQL> insert into myTable values (sysdate); 1 row created. SQL> SQL> select * from myTable;
TS
26-OCT-09 08.31.47.000000 AM 1 row selected. SQL> SQL> drop table myTable ; Table dropped. SQL>
</source>
Retrieve the timestamp into a variable
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime TIMESTAMP; 3 BEGIN 4 SELECT systimestamp INTO myDateTime FROM dual; 5 DBMS_OUTPUT.PUT_LINE(myDateTime); 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.PUT_LINE(sqlerrm); 10 END; 11 /
26-OCT-09 10.40.28.429000 AM PL/SQL procedure successfully completed. SQL> SQL>
</source>
system timestamp
<source lang="sql">
SQL> SQL> DECLARE
2 d DATE := SYSTIMESTAMP;
3 t TIMESTAMP(3) := SYSTIMESTAMP;
4 BEGIN
5 dbms_output.put_line("TO_CHAR ["||TO_CHAR(d,"DD-MON-YY HH24:MI:SS")||"]");
6 END;
7 /
TO_CHAR [26-OCT-09 09:03:59] PL/SQL procedure successfully completed. SQL>
</source>
The TIMESTAMP WITH LOCAL TIME ZONE datatype stores the date/time
<source lang="sql">
SQL> SQL> DECLARE
2 v_datetime TIMESTAMP (0) WITH LOCAL TIME ZONE := SYSTIMESTAMP; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(v_datetime); 5 END; 6 /
26-OCT-09 10.40.53 AM PL/SQL procedure successfully completed. SQL>
</source>
The TIMESTAMP WITH TIME ZONE datatype stores the date/time
<source lang="sql">
SQL> SQL> DECLARE
2 v_datetime TIMESTAMP (3) WITH TIME ZONE := SYSTIMESTAMP; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(v_datetime); 5 END; 6 /
26-OCT-09 10.40.52.639 AM -08:00 PL/SQL procedure successfully completed. SQL>
</source>
TIMESTAMP(3)
<source lang="sql">
SQL> SQL> DECLARE
2 d DATE := SYSTIMESTAMP;
3 t TIMESTAMP(3) := SYSTIMESTAMP;
4 BEGIN
5 dbms_output.put_line("TIMESTAMP ["||t||"]");
6 END;
7 /
TIMESTAMP [26-OCT-09 09.03.59.767 AM] PL/SQL procedure successfully completed. SQL>
</source>
TIMESTAMP literal
<source lang="sql">
SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime_ltz TIMESTAMP WITH LOCAL TIME ZONE := TIMESTAMP "2004-06-05 22:14:01"; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(myDateTime_ltz); 5 END; 6 /
05-JUN-04 10.14.01.000000 PM PL/SQL procedure successfully completed. SQL> SQL>
</source>
TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone
<source lang="sql">
SQL> SQL> DECLARE
2 3 v1 DATE; 4 v2 TIMESTAMP; 5 v3 TIMESTAMP WITH TIME ZONE; 6 v4 TIMESTAMP WITH LOCAL TIME ZONE; 7 8 BEGIN 9 10 11 v3 := TIMESTAMP "2002-11-03 03:00:00 -07:00"; 12 dbms_output.put_line(v3); 13 14 END; 15 /
03-NOV-02 03.00.00.000000 AM -07:00 PL/SQL procedure successfully completed. SQL>
</source>
timestamp procedure member variable
<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE CompileError AS
2 myDateTime timestamp; 3 BEGIN 4 SELECT systimestamp INTO myDateTime FROM dual; 5 DBMS_OUTPUT.PUT_LINE(myDateTime); 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.PUT_LINE(SQLERRM); 10 END; 11 /
Procedure created. SQL> SQL> exec compileerror 26-OCT-09 10.40.32.605000 AM PL/SQL procedure successfully completed. SQL>
</source>
timestamp type column
<source lang="sql">
SQL> SQL> SQL> create table myTable (ts timestamp); Table created. SQL> SQL> insert into myTable values (sysdate); 1 row created. SQL> SQL> select * from myTable;
TS
26-OCT-09 08.31.47.000000 AM 1 row selected. SQL> SQL> drop table myTable ; Table dropped. SQL>
</source>
TIMESTAMP Value WITH LOCAL TIME ZONE
<source lang="sql">
SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime_ltz TIMESTAMP WITH LOCAL TIME ZONE := TIMESTAMP "2004-06-05 22:14:01"; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(myDateTime_ltz); 5 END; 6 /
05-JUN-04 10.14.01.000000 PM PL/SQL procedure successfully completed. SQL> SQL>
</source>
TIMESTAMP WITH TIME ZONE
<source lang="sql">
SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 myDateTime_tz TIMESTAMP WITH TIME ZONE := TIMESTAMP "2004-06-05 22:14:01 +06:00"; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(myDateTime_tz); 5 END; 6 /
05-JUN-04 10.14.01.000000 PM +06:00 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Two timestamp subtypes demonstrate similar behaviors. Their prototypes are
<source lang="sql">
TIMESTAMP[(precision)] WITH TIME ZONE TIMESTAMP[(precision)] WITH LOCAL TIME ZONE Declare a TIMESTAMP WITH TIME ZONE variable with a default null or initialized value, as shown: var1 TIMESTAMP WITH LOCAL TIME ZONE; var2 TIMESTAMP WITH LOCAL TIME ZONE := SYSTIMESTAMP; var3 TIMESTAMP(3) WITH LOCAL TIME ZONE; var4 TIMESTAMP(3) WITH LOCAL TIME ZONE := SYSTIMESTAMP;
</source>
versions between timestamp minvalue and maxvalue
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> col versions_starttime format a25 SQL> col versions_endtime format a25 SQL> break on empno SQL> SQL> select empno, sal
2 , versions_starttime 3 , versions_endtime 4 from emp 5 versions between timestamp minvalue and maxvalue 6 where deptno = 10 7 order by empno, versions_starttime nulls first;
no rows selected SQL> SQL> SQL> drop table emp; Table dropped.
</source>
