Oracle PL/SQL - PL SQL Data Type TIMESTAMP Type

Introduction

TIMESTAMP allows you to specify fractions of a second.

DATE precision is limited to seconds.

You can declare this data type as shown here:

declare
       variable1_ts TIMESTAMP[(precision)];
  ...

By default, Oracle stores 6 digits of precision, but you can specify precision within the range from 0 to 9.

TIMESTAMP(0) is equivalent to DATE.

The following code shows how to declare the TIMESTAMP data type.

To initialize the variable, you use SYSTIMESTAMP, not SYSDATE.

The format mask element FF[1-9] represents fractions of a second.

If you specify fewer digits than are stored, Oracle uses the same rounding technique as for floating-point numeric data.

Demo

SQL>
SQL> declare-- from ww w .j  a  v a  2  s  .  com
  2       v_ts TIMESTAMP(6):=systimestamp;
  3       v_tx VARCHAR2(2000);
  4  begin
  5      v_tx:=to_char(v_ts,'HH24:MI:SS.FF6 ');
  6      DBMS_OUTPUT.put_line(v_tx);
  7  end;
  8  /
09:49:49.200000

PL/SQL procedure successfully completed.
SQL>

Related Topics