How to Add Second in Oracle Timestamp

How to add 10 seconds in current_timestamp SQL ( Oracle )

In Oracle, if you want a timestamp as the result, rather than a date (a date always includes the time to the second, though, so you may just want a date), you'd want to add an interval to the timestamp. There are various ways to construct an interval-- you can use an interval literal

select current_timestamp + interval '10' second
  from dual

or you could use the numtodsinterval function

select current_timestamp + numToDSInterval( 10, 'second' )
  from dual

Oracle: add milliseconds to my timestamp object

You add time to a timestamp by adding an interval. Intervals can go down to seconds only, but an interval of 0.001 seconds is a milliseconds interval which you can multiply with the desired value. E.g:

select systimestamp + interval '0.001' second * :millisecs from dual;

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

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

Add Seconds to Timestamp from another Timestamp

Try:

timestamp2  + (extract (second from timestamp1)/86400) 

Oracle: how to add minutes to a timestamp?

All of the other answers are basically right but I don't think anyone's directly answered your original question.

Assuming that "date_and_time" in your example is a column with type DATE or TIMESTAMP, I think you just need to change this:

to_char(date_and_time + (.000694 * 31))

to this:

to_char(date_and_time + (.000694 * 31), 'DD-MON-YYYY HH24:MI')

It sounds like your default date format uses the "HH" code for the hour, not "HH24".

Also, I think your constant term is both confusing and imprecise. I guess what you did is calculate that (.000694) is about the value of a minute, and you are multiplying it by the number of minutes you want to add (31 in the example, although you said 30 in the text).

I would also start with a day and divide it into the units you want within your code. In this case, (1/48) would be 30 minutes; or if you wanted to break it up for clarity, you could write ( (1/24) * (1/2) ).

This would avoid rounding errors (except for those inherent in floating point which should be meaningless here) and is clearer, at least to me.

How to insert a timestamp in Oracle?

insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));

if you want the current time stamp to be inserted then:

insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);

Convert EPOCH date to timestamp in oracle SQL

You can add a number of days directly to a date:

select date '1970-01-01' + 19072 from dual;

21-MAR-22

or add an interval to a timestamp; which is probably more appropriate as you need to end up with a timestamp with time zone anyway, so this starts with a UTC value:

select timestamp '1970-01-01 00:00:00 UTC' + (19072 * interval '1' day) from dual;

21-MAR-22 00.00.00.000000000 PM UTC

Then you can add hours, minutes and seconds (or go ahead another day and subtract one second):

select 
  timestamp '1970-01-01 00:00:00 UTC'
    + (19072 * interval '1' day)
    + (23 * interval '1' hour)
    + (59 * interval '1' minute)
    + (59 * interval '1' second)
from dual;

21-MAR-22 11.59.59.000000000 PM UTC

and convert to your target time zone with at time zone:

select
  (
    timestamp '1970-01-01 00:00:00 UTC'
      + (19072 * interval '1' day)
      + (23 * interval '1' hour)
      + (59 * interval '1' minute)
      + (59 * interval '1' second)
  )
  at time zone 'Asia/Jakarta'
from dual;

22-MAR-22 06.59.59.000000000 AM ASIA/JAKARTA

and then for display purposes, convert to a string in the format you want:

select
  to_char(
    (
      timestamp '1970-01-01 00:00:00 UTC'
        + (19072 * interval '1' day)
        + (23 * interval '1' hour)
        + (59 * interval '1' minute)
        + (59 * interval '1' second)
    )
    at time zone 'Asia/Jakarta',
    'DD/MM/YYYY HH24:MI:SS'
  ) as result
from dual;

22/03/2022 06:59:59

You can simplify a bit by modifying your epoch, though it looks a bit odd:

select
  to_char(
    (
      timestamp '1970-01-01 23:59:59 UTC'
        + (19072 * interval '1' day)
    )
    at time zone 'Asia/Jakarta',
    'DD/MM/YYYY HH24:MI:SS'
  ) as result
from dual;

22/03/2022 06:59:59

(I'd probably prefer to keep the usual epoch and do the extra explicit interval additions...)

db<>fiddle

Only format as a string to display it. If you need to pass the value somewhere else then leave it as a timestamp with time zone, or if necessary cast that to a plain timestamp or even a date.

---

Related Topics