www.webdeveloper.com
Results 1 to 7 of 7

Thread: subtracting TIMESTAMPS

  1. #1
    Join Date
    Mar 2006
    Posts
    10

    subtracting TIMESTAMPS

    I'm trying to perform a Query that takes the current TIMESTAMP and subtracts an older TIMESTAMP from it, leaving a duration between the two.

    So far, my effort of

    Code:
    UPDATE FlightBooking SET Duration = CURRENT_TIME - (SELECT BookingTime FROM FlightBooking WHERE BookingID=1) WHERE BookingID=1;
    where CURRENT_TIME is todays date, and BookingTime is the old date.

    doesnt seem to work, returning the error

    'The data type of an operand of an arithmetic funciton or operation "-" is not numeric'

    Simply put, I want to subtract the the old timestamp from the current timestamp.

    many thanks,

  2. #2
    Join Date
    Mar 2006
    Posts
    1
    Try converting the time to seconds measured from the Unix Epoch (January 1 1970 00:00:00 GMT). Then subtract the two then convert it back to a standard date. I don't know how to do this just using SQL but if you're using php use the time () function and the date() function.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,922
    What DBMS are you using (MySQL, Oracle, Access, etc.)?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Mar 2006
    Posts
    10
    its DB2.

    Sorry i meant to say I used CURRENT_TIMESTAMP.

    individually the statements work...

    UPDATE FlightBooking SET Duration = CURRENT_TIMESTAMP;

    and...

    UPDATE FlightBooking SET Duration = (SELECT BookingTime FROM FlightBooking WHERE BookingID=1);

    but i cant seem to combine them....
    Last edited by Konnor; 03-13-2006 at 04:19 PM.

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,922
    Quote Originally Posted by Konnor
    its DB2
    Afraid I don't know DB2. But from this page you might try:
    Code:
    UPDATE FlightBooking SET Duration = TIMESTAMPDIFF( 1, CURRENT TIMESTAMP - 
    (SELECT BookingTime FROM FlightBooking WHERE BookingID=1) WHERE BookingID=1;
    Change the first arg ("1") of timestampdiff for the desired unit of time.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Mar 2006
    Posts
    10
    seems to have done the trick. Many thanks.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,922
    Quote Originally Posted by Konnor
    seems to have done the trick. Many thanks.
    Glad it worked. I wasn't too sure I had all the details right.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles