SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with AddTime

    Hi there everybody, I hope your help.

    My table:
    Code:
    myDateMySql	myTimeMySql	myTotMySql_min	
    01/01/2010	16.25		30
    I need this output:
    Code:
    01/01/2010	16.55
    Try this code, but I have error:
    Code:
    ADDTIME(CONCAT(STR_TO_DATE(`myDateMySql`,'%d/%m/%Y'), ' ', `myTimeMySql`), `myTotMySql_min`)
    [Err] 1292 - Truncated incorrect time value: '92'

    Can someone help me?
    Thanks in advance.

    Chevy

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    http://dev.mysql.com/doc/refman/5.1/...ction_date-add

    Code:
    DATE_ADD(myDateMySql, INTERVAL myTimeMySql MINUTE)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why use a function? just do the date math like this --
    Code:
    SELECT myDateMySql
         , myTimeMySql + INTERVAL myTotMySql_min MINUTE AS newtime
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Hmm, so the 30 in the example data is the myTotMySql_min column value?
    Does that mean date and time are stored in separate columns?
    That might give some problems when going past midnight, wouldn't it?

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hi there, thanks for replying.

    I try two suggestions but:

    50 = myTotMySql_min
    16.25 = myTimeMySql
    01/01/2010 = myDateMySql

    I have this output:
    2010-01-01 00:50:00

    and not this:
    2010-01-01 17:15:00



    PHP Code:
    SELECT myDateMySql
         
    myTimeMySql INTERVAL myTotMySql_min MINUTE AS newtime
      FROM daTable

    DATE_ADD
    (myDateMySqlINTERVAL myTimeMySql MINUTE
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My table:
    myTotMySql_min = 50 (int 10)
    myTimeMySql = 16.25 (varchar 255)
    myDateMySql = 01/01/2010 (date)

    I try this code:

    PHP Code:
     SELECT myDateMySql      
          
    myTimeMySql INTERVAL myTotMySql_min 
            MINUTE 
    AS newtime   
     FROM daTable

     SELECT myDateMySql      
          
    TIME_FORMAT(myTimeMySql,'%H:%i') + INTERVAL myTotMySql_min MINUTE 
            
    AS newtime   
     FROM daTable 
    Output:
    Code:
    myDateMySql	newtime
    2010-01-01	(Null)
    And this code:

    PHP Code:
    SELECT
    DATE_ADD
    (myDateMySqlINTERVAL 
            myTimeMySql MINUTE
    ) AS newtime FROM daTable


    SELECT
    DATE_ADD
    (myDateMySqlINTERVAL 
           TIME_FORMAT
    (myTimeMySql,'%H:%i'MINUTE) AS newtime 
    FROM daTable 
    Output:
    Code:
    newtime
    2010-01-01 00:16:00
    Output:
    Code:
    newtime
    2010-01-01 00:00:00


  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT myDateMySql
                      , myTimeMySql
                      , myTotMySql_min
                      , ADDDATE(CONCAT(`myDateMySql`, ' '
                      , `myTimeMySql`)
                      , INTERVAL myTotMySql_min MINUTE) as t
    FROM datable
    Output:
    t= '2010-01-01 17:15:00'

    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •