Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: How to add/subtract seconds from a timestamp

    I have something like this I can do in SQL SERVER which subtracts 30 seconds from the current timestamp.

    select current_timestamp, DATEADD(second, -30, CURRENT_TIMESTAMP);

    which returns

    2005-10-19 10:11:32.443 2005-10-19 10:11:02.443

    What would be the equivalent for the above in Oracle? I have played around with with add_months together with to_char etc.. but haven't had much luck. Any help will be appreciated. Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:

    select current_timestamp, current_timestamp - interval '0 00:00:30' day to second
    from dual;

    There may be an easier way, I'm not terribly familiar with timestamps yet.

  3. #3
    Join Date
    Dec 2001
    Posts
    40
    Thanks, the syntax seems to work. Actually what I am trying is, Instead of the simple

    select current_timestamp, DATEADD(second, -30, CURRENT_TIMESTAMP);

    I have to use a column name within the formula. Example...

    Here is the SQL SERVER code I am using
    create table testt1 (ms integer, dob datetime);

    insert into testt1 values (30000,current_timestamp);

    select ms,dob,DATEADD(second, -a.ms/1000, CURRENT_TIMESTAMP) from testt1 a;

    I tried to do the above with in Oracle with the syntax you sent but oracle does not seem to like it.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What version of the database are you using and is the column defined as a "TIMESTAMP" or is it defined as a date. If you want to subtract 30 seconds from a date or timestamp, the easiest way is to do the following


    select ms,dob,(a.ms/1000)- (30/(24*60*60)) from testt1 a;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2001
    Posts
    40
    Thanks for your feekback.

    1. oracle 10g rel 2
    2. column is defined as a timestamp

    What I really want is to subtract 30 secs from the timestamp and print out the result as a timestamp. your query prints out an decimal value

    For example when I run this in SQL SERVER

    create table testt1 (ms integer, dob datetime);
    insert into testt1 values (30000,current_timestamp);
    select ms,dob,DATEADD(second, -a.ms/1000, CURRENT_TIMESTAMP) from testt1 a;

    I get
    30000 2005-10-19 11:24:06.510 2005-10-19 17:21:35.960

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This will do it:

    select ms, dob, dob - numtodsinterval (ms/1000, 'second')
    from tesst1;

    Normally I would use DATE datatype for a date of birth, when the SQL becomes:

    select ms, dob, dob - ms/1000
    from tesst1;

  7. #7
    Join Date
    Dec 2001
    Posts
    40
    That works perfectly. Thanks so much for all your help. It's greatly appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •