TIMESTAMPADD function problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: TIMESTAMPADD function problems

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    26
    Hi All,

    I'm trying to add 10 seconds to a table field using the TIMESTAMPADD function. But I'm getting all types of syntax errors. I tried all types of combinations, nothing seems to work. Oracle's web site is not too helpful.

    SELECT TIMESTAMPADD(SQL_TSI_SECOND, 10, SYSDATE) FROM DUAL;

    Any help would be greatly appreciated.

    Thanks,
    - G

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Are you sure it's an Oracle function and not a user defined function?

    We can be more helpful if you post error codes and messages.

    If you have a date MY_DATE you can add 1 day with +1:
    SELECT my_date+1 FROM dual;
    You can add one hour with +1/24:
    SELECT my_date+1/24 FROM dual;
    You can add one second with +1(60*60*24):
    SELECT my_date+1/(60*60*24) FROM dual;

    To add 10 seconds:
    SELECT my_date+10/(60*60*24) FROM dual;

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    1 day = 86400 seconds, thus one second = 1/86400 days. Adding 1 to a date adds one day. Thus adding one second by adding 1/86400. To add 10 seconds, just add (1/86400)*10 = 1/8640.

    select sysdate + 1/8640 from dual; // this returns sysdate + 10 seconds.

    I found TIMESTAMPADD in a SQL reference, but I couldn't get it to work and couldn't find it in the data dictionary.

    Good luck!


  4. #4
    Join Date
    Mar 2001
    Posts
    26
    Thank you very much! It seemed to have worked...

    Thanks,
    - G

  5. #5
    Join Date
    Mar 2001
    Posts
    26
    Hi All,

    Why am I having a problem adding a minute to a certain date?

    I'm trying to execute the following statement:

    UPDATE my_table
    SET my_date = my_date + 1/(60*24)

    When I query the table, my_date column doesn't get updated.

    Any help would greatly be appreciated.

    Thanks.
    - Gary


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It gets updated, however your defoult date format is probably showing you just the day portion of the date, without the time part.

    Select from your table like this:

    SELECT TO_CHAR(my_date,'MM/DD/YYY HH24:MI:SS') FROM my_table;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Mar 2001
    Posts
    26
    Thanks, it worked.

    - Gary

Posting Permissions

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


Click Here to Expand Forum to Full Width