Oracle PL/SQL Tutorial - PL/SQL TIMESTAMP

• « Previous
• Next »

The data type TIMESTAMP stores the year, month, day, hour, minute, and second.

The syntax is:

TIMESTAMP[(precision) 

where the precision specifies the number of digits in the fractional part of the seconds field.

The default is 6.

The default timestamp format is set by the Oracle initialization parameter NLS_TIMESTAMP_FORMAT.

Example

DECLARE 
  checkout TIMESTAMP(3); 
BEGIN 
  checkout := '22-JUN-2022 07:48:53.275'; 
  DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout)); 
END; 
/ 

The code above generates the following result.

Example 2

The following code shows how to use the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN functions to manipulate TIMESTAMPs.

DECLARE --   ww  w  . ja  v a  2s  .com
  right_now  TIMESTAMP; 
  yesterday  TIMESTAMP; 
  sometime   TIMESTAMP; 
  i_scn1       INTEGER; 
  i_scn2       INTEGER; 
  i_scn3       INTEGER; 
BEGIN 
  right_now := SYSTIMESTAMP; 
  i_scn1 := TIMESTAMP_TO_SCN(right_now); 
  DBMS_OUTPUT.PUT_LINE(i_scn1); 
 
  yesterday := right_now - 1; 
  i_scn2 := TIMESTAMP_TO_SCN(yesterday); 
  DBMS_OUTPUT.PUT_LINE(i_scn2); 
 
  i_scn3 := (i_scn1 + i_scn2) / 2; 
  sometime := SCN_TO_TIMESTAMP(i_scn3); 
  DBMS_OUTPUT.PUT_LINE (i_scn3);
  DBMS_OUTPUT.PUT_LINE (sometime); 
END; 
/ 

The code above generates the following result.

TIMESTAMP WITH TIME ZONE Data Type

TIMESTAMP WITH TIME ZONE includes a time-zone displacement.

The time-zone displacement is the difference between local time and Coordinated Universal Time (UTC,) formerly Greenwich Mean Time (GMT).

The syntax is:

TIMESTAMP[(precision)] WITH TIME ZONE 

precision specifies the number of digits in the fractional part of the seconds field, range 0..9.

The default is 6.

The default timestamp with time zone format is set by the Oracle initialization parameter NLS_TIMESTAMP_TZ_FORMAT.

The following code shows how to assign a Literal to a TIMESTAMP WITH TIME ZONE Variable.

DECLARE 
  logoff TIMESTAMP(3) WITH TIME ZONE; 
BEGIN 
  logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; 
  DBMS_OUTPUT.PUT_LINE (TO_CHAR(logoff)); 
END; 
/ 

The code above generates the following result.

For example, the following literals all represent the same time.

TIMESTAMP '15-APR-2014 8:00:00 -8:00' 
TIMESTAMP '15-APR-2014 8:00:00 US/Pacific' 
TIMESTAMP '31-OCT-2014 01:30:00 US/Pacific PDT' 

The available names for time zones are in the TIMEZONE_REGION and TIMEZONE_ABBR columns of the static data dictionary view V$TIMEZONE_NAMES.

TIMESTAMP WITH LOCAL TIME ZONE Data Type

TIMESTAMP WITH LOCAL TIME ZONE includes a time-zone displacement.

The time-zone displacement is the difference between local time and Coordinated Universal Time.

We can use named time zones, as with TIMESTAMP WITH TIME ZONE.

The syntax is:

TIMESTAMP[(precision)] WITH LOCAL TIME ZONE 

precision specifies the number of digits in the fractional part of the seconds field.

When inserting a value into a database column, the value is normalized to the database time zone, and the time-zone displacement is not stored in the column.

When you retrieve the value, Oracle returns it in your local session time zone.

The following code shows how to Assignment to TIMESTAMP WITH LOCAL TIME ZONE.

DECLARE 
  logoff  TIMESTAMP(3) WITH LOCAL TIME ZONE; 
BEGIN 
  logoff := '10-OCT-2014 10:42:37.111 AM '; 
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(logoff)); 
END; 
/ 

The code above generates the following result.

• Next »
• « Previous