I am converting something from SQL Server to PostgreSQL. There is a table with a calculated field between BeginTime and EndTime called MidTime. Time is the offset from the beginning of the video clip and will never be more than 6 minutes. In SQL Server, BeginTime, EndTime, and MidTime are all TimeSpans. You can use this as a function:
DATEADD(ms, DATEDIFF(ms,BeginTime, EndTime)/2, BeginTime)
Which takes the difference in the two time series in millseconds, dividing them by 2, and then adding them to BeginTime. Super easy. The result is as follows:
ID BeginTime EndTime MidTime 10137 00:00:05.0000000 00:00:07.0000000 00:00:06.0000000 10138 00:00:08.5000000 00:00:09.6660000 00:00:09.0830000 10139 00:00:12.1660000 00:00:13.4000000 00:00:12.7830000 10140 00:00:14.6000000 00:00:15.7660000 00:00:15.1830000 10141 00:00:17.1330000 00:00:18.3000000 00:00:17.7160000 10142 00:00:19.3330000 00:00:21.5000000 00:00:20.4160000 10143 00:00:23.4000000 00:00:25.4000000 00:00:24.4000000 10144 00:00:25.4330000 00:00:26.8330000 00:00:26.1330000
I looked at all the things available to me in PostgreSQL and I don’t see anything like it. I store BeginTime and EndTime as time values (6), and they look right in the database. I can subtract them from each other, but I cannot get the value in milliseconds twice (time sharing is not allowed), and then there is no obvious way to add milliseconds back to BeginTime.
I looked at EXTRACT, which when you ask for milliseconds gives you the value of a second and milliseconds, but only a fraction of the time. I can’t get an idea of the time that I can subtract, divide, and then add the result back to another time.
I use Postgres 9.4 and I don’t see an easy way to do this without breaking the date into its constituent parts and getting common milliseconds (it seems to work, but I don’t want to do such an ugly thing if I don’t need it) or converting everything to datetime unix, and then doing the calculations, and then it’s not clear how to return it to “time without a time zone”.
I hope there is something elegant that I'm just missing? Or maybe the best way to keep them where this job is easier? I'm only interested in part of the time, so time (6) seemed closest to Sql Server TimeSpan.