0

Possible Duplicate:
Difference between two dates in MySQL

I have three fields in a table. Date1, Date2, and DiffinHours. In our PHP code, when Date2 is set, we calculate the difference in hours between Date2 and Date1 and then set DiffinHours to that value.

However, we have a database where the calculations were not being done for some period of time, and we need to calculate DiffinHours for that time frame. I can write a script to do it, but am hoping it can be done via a query.

Community
  • 1
  • 1
Amy Anuszewski
  • 1,843
  • 17
  • 30
  • 1
    A quick search through the MySQL date and time functions reveals the TIMESTAMPDIFF() function. Might that work for your situation? http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff – Thomas Aug 23 '12 at 13:32
  • @PeterSzymkowski I agree there's many threads on this, but she's looking how to do an `UPDATE` on the difference. – Kermit Aug 23 '12 at 13:45
  • Thank you Peter. That link helped as well. – Amy Anuszewski Aug 23 '12 at 13:52

1 Answers1

1

Run this query periodically. Keep track of the last time the row was evaluated, and check that value for 0 (meaning timediff for the row hasn't been calc'ed yet):

UPDATE database SET DiffInHours = HOUR(TIMEDIFF(date2, date1)), lastEval = NOW() WHERE date2 != 0 AND lastEval = 0;

Or maybe you mean a one time query to backfill the database, here the query is just run when Diffinhours is zero:

UPDATE database SET DiffInHours = HOUR(TIMEDIFF(date2, date1)) WHERE date2 != 0 AND DiffInHours = 0;
Scott Presnell
  • 1,528
  • 10
  • 23
  • I'm going to accept this one because it's closest to what I needed. I didn't need the lastEval part - just needed to know when date2 wasn't null. Since it's a one time query - the script will take over for future entries, this did the trick. Thank you. – Amy Anuszewski Aug 23 '12 at 13:52