PostgreSQL在两个时间戳之间找到中点 [英] PostgreSQL to find midpoint between two timestamps

问题描述

我正在将某些内容从SQL Server转换为PostgreSQL。在BeginTime和EndTime之间有一个带有
计算字段的表，称为MidTime。时间是从视频剪辑的开始偏移的，永远不会超过6分钟。在SQL Server中，BeginTime，EndTime和MidTime均为TimeSpans。您可以将其用作函数：

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)

将两个时间跨度之差（以毫秒为单位）除以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

我查看了PostgreSQL中所有可用的东西，但没有看到类似的东西。我将BeginTime和EndTime存储为无时区的时间 time（6）值，它们在数据库中看起来正确。我可以将它们彼此相减，但是我无法获得以毫秒为单位的值减半（不允许进行分频），因此没有明显的方法将毫秒重新添加到BeginTime中。

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.

我看过EXTRACT，当您要求毫秒时，它会为您提供秒和毫秒的值，但只是那部分时间。我似乎无法获得可以减去，除以然后再将结果加回到另一个时间的时间的表示形式。

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.

我正在使用Postgres 9.4而且我看不到任何简单的方法可以将日期分解成各个组成部分并获得总体毫秒数（似乎可以正常工作，但如果不需要的话，我不想做这种丑陋的事情），或将所有内容转换为unix日期时间，然后进行计算，然后如何将其恢复为无时区的时间并不明显。

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."

我希望我只是缺少一些优雅的东西？还是一种更好的方式来存储这些内容，从而使工作更容易？我只对时间部分感兴趣，因此time（6）似乎最接近Sql Server的TimeSpan。

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.

推荐答案

只需从中减去一个其他除以2，然后将其添加到开始时间：

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

begintime + (endtime - begintime)/2

正确的是，您不能除以 time 值。但是结束时间-开始时间的结果不是时间，而是间隔。并且您可以将间隔除以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.

这篇关于PostgreSQL在两个时间戳之间找到中点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！