removing "acceleration" from subtitle files

Question

I have a video with subtitles (and corresponding timestamps) in a MySQL database. Sometimes the timestamp on the video and subtitle syncs, sometimes it doesn't.

The problem is that it's not a consistent offset within the video (i.e. the difference is increasing throughout the video so using a simple UPDATE didn't work: "UPDATE subtitles SET Timestamp=Timestamp+$time WHERE title=$video";')

How do I write a PHP script to calculate the "acceleration" of the offset and then update several hundred MySQL entries for that video?

Answer 1

It sounds like you have a bunch of rows in the subtitles table with Timestamp values that need to be individually adjusted. This is a guess, but it sounds like you need to change the Timestamp values in a way like this:

0.0   -->  0.0
1.0   -->  1.1
2.0   -->  2.2

For this example the formula is

newTimestamp[row n] = Timestamp[row 0] + 
                       (Timestamp[row n] - Timestamp[row 0]) * factor)

The factor in this example is 1.1, which will speed up your subtitles by 10%.

Your question asks for an algorithm to determine the correct factor to use. I am sorry that I can't suggest a way to do that without knowing a lot more about how you store your video.

You then ask for an algorithm to apply the factor to your Timestamp values in the rows of your table. What you had is very close. Something like this should work:

UPDATE `subtitles` 
   SET `Timestamp`=
          MIN(`Timestamp`) + ( $factor * (`Timestamp` - MIN(`Timestamp`) )
 WHERE title=$video

The trouble is, DATETIME values don't really work with arithmetic. So we need to muck about a bit more to get this to go. We need the time difference in as a number, like so.

          ROUND (
           ((       HOUR(TIMEDIFF(`Timestamp`,MIN(`Timestamp`))) * 3600.0) +
            (     MINUTE(TIMEDIFF(`Timestamp`,MIN(`Timestamp`))) * 60.0  ) +
            (     SECOND(TIMEDIFF(`Timestamp`,MIN(`Timestamp`)))         )
           ) * 1000000.0 +
           (MICROSECOND(TIMEDIFF(`Timestamp`,MIN(`Timestamp`)))          ) 
          )

This hairy expression turns the difference between the current time and the first time into an integer number of microseconds.

Then we need to apply the factor and add it back to the beginning time using the kind of time arithmetic that does work: timeval = othertimeval + INTERVAL ROUND(xx * factor) MICROSECOND.

UPDATE `subtitles` 
   SET `Timestamp`=
          MIN(`Timestamp`) + INTERVAL 
          ROUND ((
           ((       HOUR(TIMEDIFF(`Timestamp`,MIN(`Timestamp`))) * 3600.0) +
            (     MINUTE(TIMEDIFF(`Timestamp`,MIN(`Timestamp`))) * 60.0  ) +
            (     SECOND(TIMEDIFF(`Timestamp`,MIN(`Timestamp`)))         )
           ) * 1000000.0 +
           (MICROSECOND(TIMEDIFF(`Timestamp`,MIN(`Timestamp`)))          ) 
          ) * $factor) MICROSECOND
 WHERE title=$video

Again, there's some guesswork in this example, but this kind of thing should work.

Are we having fun yet? Are we? Are we?