CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Winter
Winter

Reputation: 1916

How to add one nanosecond to a timestamp in PL/SQL

I am trying to add a nanosecond to a timestamp variable without any luck.

This is the example code:

DECLARE
  lts_nextTimestamp TIMESTAMP = TO_TIMESTAMP('11-11-2013 22:10:10:111111111');
  ;
BEGIN
  lts_nextTimestamp := lts_nextTimestamp + 1 / (10000000*60*60*24)
END;

Any idea how can I accomplish this?

Upvotes: 3

Views: 8069

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

interval day to second literal can be used to add fractional seconds to a timestamp value:

In this example we add one nanosecond:

select timestamp '2013-11-11 22:10:10.111111111' + 
       interval '0 00:00:00.000000001' day to second(9) as res
  from dual

Result:

RES                           
-------------------------------
11-NOV-13 10.10.10.111111112 PM

Note: When you are using to_timestamp() function to convert character literal to a value of timestamp data type, it's a good idea to specify a format mask(not relay on NLS settings).

select TO_TIMESTAMP('11-11-2013 22:10:10:111111111', 'dd-mm-yyyy hh24:mi:ss:ff9') + 
       interval '0 00:00:00.000000001' day to second(9) as res
  from dual

Result:

RES                           
-------------------------------
11-NOV-13 10.10.10.111111112 PM

Note: As you intend to process values of timestamp data type using PL/SQL you should be aware of the following. The default precision of fractional seconds for values of timestamp data type, in PL/SQL, is 6 not 9 as it is in SQL, so you may expect truncation of fractional second. In order to avoid truncation of fractional seconds use timestamp_unconstrained and dsinterval_unconstrained data types instead of timestamp and interval day to second:

declare
  l_tmstmp timestamp_unconstrained := to_timestamp('11-11-2013 22:10:10:111111111',
                                                   'dd-mm-yyyy hh24:mi:ss:ff9');
  l_ns     dsinterval_unconstrained :=  interval '0.000000001' second;
begin
  l_tmstmp := l_tmstmp + l_ns;
  dbms_output.put_line(to_char(l_tmstmp, 'dd-mm-yyyy hh24:mi:ss:ff9'));
end;

Result:

anonymous block completed
11-11-2013 22:10:10:111111112

Upvotes: 8

Related Questions