SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL cell formula possible? Possibly a dumb question...

    hello all!

    I have two cols in a MySQL database date1 and date2.

    I need a third col that needs to be: time = date2 - date1

    Rather than having to perform this operation with a select calculate and insert statement I was wondering if there was a way to put a formula of some sort in the time col that would automatically calculate the difference in the dates?

    I guess the closest real-world example I can provide is something similar to what you could do in Excel with formulas and equations.

    *Disclaimer: this is my first venture into MySQL math

    Thanks in advance for any guidance!

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Figured it might be handy to post my SQL statement for date2 insert (date1 would already exist at this point) Maybe this solution is as simple as modifying this insert statement to do the calculation and insert the timeOpen col

    Code:
    "UPDATE myTable SET date2 = NOW() WHERE id = '$id'"

  3. #3
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how wrong is this? For some reason this makes sense in my mind (lol). Am I completely off base? Is there a better way?

    Code:
     
    UPDATE myTable 
       SET 
             date2 = NOW()
          ,  timeOpen = SUM(NOW() - date1)
    
    WHERE id = '$id'
    Or something like this?
    Code:
     
    UPDATE myTable 
       SET 
             date2 = NOW()
          ,  timeOpen = (SELECT SUM(NOW() - date1) FROM myTable WHERE id = '$id')
    
    WHERE id = '$id'

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My date format in the db is as such: 2009-09-10 19:32:49

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for the actual table and column names and definitions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, here's a condensed version of the table (all non-relavant cols removed).

    I've changed the time_to_open data type a few times trying to get this to work. I just need it to be a time value I can work with in php. Seconds, miliseconds...etc would all be ok. I doubt 'time' is a good choice. Maybe INT?

    BTW, the calculation doesn't have to be exact but would like it to be accurate to a few seconds at least.

    Code:
    CREATE TABLE IF NOT EXISTS `myTable` (
      `id` int(11) NOT NULL auto_increment,
      `dateTime` datetime NOT NULL default '0000-00-00 00:00:00',
      `updated` datetime NOT NULL default '0000-00-00 00:00:00',
      `time_to_open` time NOT NULL,
    
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=86 ;

    `dateTime` = when the record was added (I know, not the best choice for col name)
    `updated` = when the record was opened (again, I know, not the best choice for col name)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, that's fine, but now comes the next question: why do you want to ~store~ the time difference between dateTime and updated? you can grab it in any SELECT, so why bother?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I guess my train of thought on this was.....

    The value date_to_open will be used quite a bit, on almost every query. So... rather than having the calculation occur for X records every time the table is queried I thought it would be more efficient to run the calculation once, and then store it in a col so that when the table is queried it will just grab the col rather than having to run calculations. Once the time_to_open is calculated once, it will remain that number for the life of the record.

    Maybe this isn't necessary, I was just thinking that by storing it I would cut down on an infinite number of calculations in the future?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the cpu cycles you save for an in-memory calculation are more than offset by the cycles you waste trying to update the extra column, to say nothing of the extra space required by the extra column, which only makes your table bigger and hence just a bit slower to read through

    and anyway, you have the time_to_open as NOT NULL -- what were you planning to store for rows where the updated value is the default "zero date" ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I guess it's not necessary to store the value then. I added the SUM to my select statement but it seems to be calculating the value of the entire col rather than for just the one record. Because of this it's only returning one record.

    Code:
    SELECT 
         myTable.id
       , myTable.email
       , myTable.phone
       , myTable.address
       , SUM(myTable.updated - myTable.added) AS time_to_open
    FROM myTable
    LIMIT 15
    Any ideas where I'm going wrong here?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    SUM() is an aggregate function, intended to operate on a column of values, not on separate column values across a single row

    because you are dealing with datetime values, you might not be able to just subtract them as you would with integer values...
    Code:
    SELECT updated - added AS time_to_open ...
    i would use the TIMEDIFF function instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Allright, used the TIMEDIFF function and all is well! Working like a charm.

    So you still agree that running the calculations on every query is a better solution than running it once and storing it in the table?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, i do

    one alternative strategy is not to store the updated datetime, but instead, store the time difference

    i guess it depends on which you referenced more often -- updated or time_to_open
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... interesting idea. I'll have to let that marinate...


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
  •