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

[rustybuddy]

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!

[rustybuddy]

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

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

[rustybuddy]

how wrong is this? For some reason this makes sense in my mind (lol). Am I completely off base? Is there a better way?

 
UPDATE myTable 
   SET 
         date2 = NOW()
      ,  timeOpen = SUM(NOW() - date1)

WHERE id = '$id'

Or something like this?

 
UPDATE myTable 
   SET 
         date2 = NOW()
      ,  timeOpen = (SELECT SUM(NOW() - date1) FROM myTable WHERE id = '$id')

WHERE id = '$id'

[rustybuddy]

My date format in the db is as such: 2009-09-10 19:32:49

[r937]

please do a SHOW CREATE TABLE for the actual table and column names and definitions

[rustybuddy]

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.

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)

[r937]

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?

[rustybuddy]

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?

[r937]

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" ???

[rustybuddy]

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.

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?

[r937]

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...

SELECT updated - added AS time_to_open ...

i would use the TIMEDIFF function instead

[rustybuddy]

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?

[r937]

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

[rustybuddy]

Hmm... interesting idea. I'll have to let that marinate...