#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Timestamp calculation


    I have three columns on my table, "in", "out" and "totaltime" all of them are timestamp type. So I need to calculate the time a specific person have been logged in the system, and show this result at totaltime column...

    this is my code:
    String sql = "update school2 set totaltime = TIMESTAMPDIFF(in, out) where id = '"+Integer.parseInt(text_id.getText())+"' ";

    it does not work...
    I will appreciate any suggestion.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by darroite
    I have three columns on my table, "in", "out" and "totaltime" all of them are timestamp type.
    actually, you also have another column in the table, called "id"

    you should never store a value in a column like totaltime because it can easily be calculated, so you should remove the totaltime column from your table

    in other words, your entire UPDATE statement is not necessary

    instead, do the calculation in the SELECT statement when you retireve data for a person

    as to why your calculation "doesn't work" i'm afraid i'm not familiar with the "doesn't work" error message

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    First of all thank you for replay,
    I agree with you on the part when you say I should do all my calculation on my select statement, by I will need to keep one record of the total time each person has stood logged on the system, that is why I have created the "totaltime" column.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by darroite
    ...that is why I have created the "totaltime" column.
    that's not a good reason, but if you want it that way, go right ahead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    I am actually trying to insert the result of the sum of logged time into a new table.

IMN logo majestic logo threadwatch logo seochat tools logo