CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user2428207
user2428207

Reputation: 825

Add Seconds to Timestamp from another Timestamp

I got two timestamps:

say:

21.10.2013 00:00:23

and

21.10.2013 00:00:00

How can I add the 23 Seconds from timestamp 1 to the second.

My query is something like that at the moment:

select count(1) 
from tbl where timestamp1 >= NVL((select MAX(TIMESTAMP2) + ("Seconds from TS 1") 
from tbl2), timestamp - 1);

Upvotes: 0

Views: 4405

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

As an alternative, you could get the seconds (or minutes and seconds, or the whole time portion) from the first timestamp as an interval, and add that to the second timestamp.

The TRUNC(date) function takes a parameter that determines the precision; if you pass DD (the default) then it strips the whole time off, if you pass HH24 then it keeps the hours but strips the minutes and below off, etc. The result is a date, but if you then subtract that from a timestamp the result is an interval (as the docs say).

You can see the intervals that are created with this demo:

with t as (
  select to_timestamp('2013-11-06 09:10:11', 'YYYY-MM-DD HH24:MI:SS') as ts1
  from dual
)
select ts1,
  ts1 - trunc(ts1, 'MI') as second_interval,
  ts1 - trunc(ts1, 'HH24') as minute_interval,
  ts1 - trunc(ts1, 'DD') as hour_interval
from t;

TS1                     SECOND_INTERVAL MINUTE_INTERVAL HOUR_INTERVAL
----------------------- --------------- --------------- -------------
2013-11-06 09:10:11.000 0 0:0:11.0      0 0:10:11.0     0 9:10:11.0

You can then add the interval to your other timestamp:

with t as (
  select to_timestamp('2013-11-06 09:10:11', 'YYYY-MM-DD HH24:MI:SS') as ts1,
    to_timestamp('2013-10-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as ts2
  from dual
)
select ts2 + (ts1 - trunc(ts1, 'MI')) as ts2_second_adj,
  ts2 + (ts1 - trunc(ts1, 'HH24')) as ts2_minute_adj,
  ts2 + (ts1 - trunc(ts1, 'DD')) as ts2_hour_adj
from t;

TS2_SECOND_ADJ          TS2_MINUTE_ADJ          TS2_HOUR_ADJ          
----------------------- ----------------------- -----------------------
2013-10-31 00:00:11.000 2013-10-31 00:10:11.000 2013-10-31 09:10:11.000

SQL Fiddle.

If you're getting more than one part of the time then this might be a bit simpler than doing multiple extract calls and having to divide those by the right value (86400 for seconds, etc.). This also retains the fractional seconds from the timestamp, which may or may not be desirable.

This approach also works for DATE values, while the extract version doesn't.

Upvotes: 2

Joe
Joe

Reputation: 6827

Try:

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

Upvotes: 4

Related Questions