OraFAQ Forum: SQL & PL/SQL » Adding seconds to a date

Home » SQL & PL/SQL » SQL & PL/SQL » Adding seconds to a date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Adding seconds to a date [message #183311]

Thu, 20 July 2006 06:03

rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England

Junior Member

Hi there I have two columns - event_date which is a date and event_duration which is a number.

I wish to add the number of seconds in event_duartion to the event_date

So if the event_date is 20/07/2006 and the duration is 60 I would like to see in event_date

20/07/2006 00:01:00

How do I do this as I am stuck.

regards
bob...

Report message to a moderator

Re: Adding seconds to a date [message #183313 is a reply to message #183311]

Thu, 20 July 2006 06:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You could do it the simple way:

event_date := event_date + event_duration/(24*60*60);

or the complex way

SQL> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')  original
  2        ,to_char(sysdate+ numtodsinterval(61,'second'),'dd-mon-yyyy hh24:mi:ss') new
  3  from dual;

ORIGINAL             NEW
-------------------- --------------------
20-jul-2006 11:56:50 20-jul-2006 11:57:51

Report message to a moderator

Re: Adding seconds to a date [message #183314 is a reply to message #183311]

Thu, 20 July 2006 06:16

Maaher
Messages: 7065
Registered: December 2001

Senior Member

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select thedate
  2       , thesecs
  3       , thedate+60/86400 newdate
  4  from   ( select to_date('20/07/2006','dd/mm/yyyy') thedate
  5                , 60 thesecs
  6           from   dual
  7         ) yourtable
  8  /

THEDATE                THESECS NEWDATE
------------------- ---------- -------------------
20/07/2006 00:00:00         60 20/07/2006 00:01:00

Explanation: "thedate" is a date with no time information so the time defaults to '00:00:00'. Date calculations take place in units of days. There are 86400 seconds in a day (60*60*24). 1 second is 1/86400 days. So all we need to do is add the second information, divided by 86400, to the date.

MHE

[edit]Came in second:

[Updated on: Thu, 20 July 2006 06:17]

Report message to a moderator

Re: Adding seconds to a date [message #183335 is a reply to message #183311]

Thu, 20 July 2006 07:37

rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England

Junior Member

Hi guys,
They both work - thanks for all your help. Duh I knew it was somthing like that I missed.

Report message to a moderator

Previous Topic:	calculate (date+time - date+time) in format Days:Hours:Mins
Next Topic:	using BULK COLLECT INTO

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 17 10:19:24 CDT 2025