Oracle PL/SQL/Data Type/TIMESTAMP

A normalization to DB timezone

    
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>

Assign current_timestamp to timestamp type variable

    
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>

Create a table with two columns, Use Datatypes: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE

    
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>

Define and set timestamp value

    
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>

Define a TIMESTAMP variable with a default null or initialized value

    
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>

Demonstrate the new ANSI Timestamp literal. Use 0-9 digits for fractional seconds

    
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>

Difference between the DATE and TIMESTAMP datatypes:

    
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>

gets the date a little more exact

    
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>

Insert sysdate value to timestamp type column

    
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>

Retrieve the timestamp into a variable

    
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>

system timestamp

    
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>

The TIMESTAMP WITH LOCAL TIME ZONE datatype stores the date/time

    
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>

The TIMESTAMP WITH TIME ZONE datatype stores the date/time

    
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>

TIMESTAMP(3)

    
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>

TIMESTAMP literal

    
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>

TIMESTAMP literal supports Time Zone (as offset from UTC). Default is SESSION Timezone

    
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>

timestamp procedure member variable

    
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>

timestamp type column

    
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>

TIMESTAMP Value WITH LOCAL TIME ZONE

    
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>

TIMESTAMP WITH TIME ZONE

    
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>

Two timestamp subtypes demonstrate similar behaviors. Their prototypes are

    
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;

versions between timestamp minvalue and maxvalue

    
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.