SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Copy fields with calculation in one statement

    Hi All,

    Tinkering with MySQL and I'm attempting to copy 3 fields from table A to table B. Simple enough, but while performing this copy, I'd like a 4th field to be calculated using the time_to_sec function.

    As a standalone statement the below works fine for doing a time_to_sec select:

    Code:
    select round(time_to_sec(timediff(clockon,clockoff))/60) as minutes_worked from tableA where employee = 'John';
    At the moment I have the below, which is an amalgamation of the above plus a straightforward field copy, but it doesn't work. If anyone can point me in the right direction (or even what to search for on the web since I haven't been able to stumble onto anything relevant) that'd be most welcome.

    Code:
    insert into tableB (employee, clockon, clockoff, minutes_worked) select employee, clockon, clockoff, minutes_worked as round (time_to_sec(timediff(clockon,clockoff))/60) from tableA where employee = 'John';
    Thanks

    MP

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are more likely to get a response if you present your query with a modicum of formatting, e.g. indents and line breaks, thus relieving us of the onerous task of first doing it ourselves before attempting to read and understand your query
    Code:
    INSERT 
      INTO tableB 
         ( employee
         , clockon
         , clockoff
         , minutes_worked) 
    SELECT employee
         , clockon
         , clockoff
         , ROUND(TIME_TO_SEC(TIMEDIFF(clockon,clockoff))/60) 
      FROM tableA 
     WHERE employee = 'John'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for not conducting research prior to posting. Thanks for the heads up.

  4. #4
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Obviously I need more sleep. I only just noticed you posted a solution along with the correction! Thanks so much for that r937!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

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