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
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'"
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)
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?
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?
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” ???
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
