PostgreSQL to find midpoint between two timestamps

Question

I'm converting something from SQL Server to PostgreSQL. There's a table with a calculated field between a BeginTime and an EndTime called MidTime. The times are offsets from the beginning of a video clip and will never be more than about 6 minutes long. In SQL Server, BeginTime, EndTime, and MidTime are all TimeSpans. You can use this as the function:

DATEADD(ms, DATEDIFF(ms,BeginTime, EndTime)/2, BeginTime)

Which is taking the difference in the two timespans in millseconds, dividing it by 2, and then adding it to the BeginTime. Super straightforward. Result looks like this:

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've looked at all of the different things available to me in PostgreSQL and don't see anything like this. I'm storing BeginTime and EndTime as "time without time zone" time(6) values, and they look right in the database. I can subtract these from each other, but I can't get the value in milliseconds to halve (division of times is not allowed) and then there's no obvious way to add the milliseconds back into the BeginTime.

I've looked at EXTRACT which when you ask for milliseconds gives you the value of second and milliseconds, but just that part of the time. I can't seem to get a representation of the time that I can subtract, divide, and then add the result back into another time.

I'm using Postgres 9.4 and I don't see any simple way of doing this without breaking the date into component parts and getting overall milliseconds (seems like it would work but I don't want to do such an ugly thing if I don't need to), or converting everything to a unix datetime and then doing the calculations and then it's not obvious how to get it back into a "time without time zone."

I'm hoping there's something elegant that I'm just missing? Or maybe a better way to store these where this work is easier? I am only interested in the time part so time(6) seemed closest to Sql Server's TimeSpan.

Volvulus

There is no data type time without time zone in Postgres. Either time or timestamp without time zone. I'm not familiar with SQL Server's data types, but timespan sounds more like you want to store that as an interval not a time value – Lyndseylyndsie

Thank you. When I created the table I used "time without time zone," as I read about on this page linked below (in the table at the top), looks like [without time zone] is optional like the word inner is in an inner join, but I wanted to make sure there was no confusion about the datatype (and I'm inexperienced with postgres) so I was as specific as possible. postgresql.org/docs/9.4/static/datatype-datetime.html – Volvulus

That said, I think you're right now that I've looked at interval again, I probably want a BeginTime and Duration interval, and a calculated EndTime (which the app uses like one or two times). Doing a quick port so if the below answer doesn't work I'll refactor. – Volvulus

Ah. I stand corrected, sorry. I wasn't aware of time without time zone. – Lyndseylyndsie

Answer 1

Just subtract one from the other divide it by two and add it to begintime:

begintime + (endtime - begintime)/2

It is correct that you can't divide a time value. But the result of endtime - begintime is not a time but an interval. And you can divide an interval by 2.

The above expression works with time, timestamp or interval columns.

Lyndseylyndsie

Thank you. Of course this works perfectly and is even more elegant than the SQL Server DATEADD nightmare. I looked at that doc page like 6 times and I didn't catch it, the bit I read about not dividing just seemed so important and biased me. ;) Thanks horse. – Volvulus