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?
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?
A bit late, but its better than never :)
You can use library.
For example your video length is 1 hour. And your subtitles at the beginning are in sync. And at the end of video subtitles are 10 seconds in front of video. So you need to subtract -10 seconds from the end. -5 seconds from the middle... and so on...
$subtitles = Subtitles::load('subtitles.srt'); // you can also manually add subtitles from DB using ->add(...) method
$subtitles->shiftTimeGradually(-10);
$subtitles->save('synced-subtitles.srt');