基于秒的 Postregsql 日期差异 [英] Postregsql Date Difference on basis on seconds

问题描述

如何在 PostgreSQL 中以秒为单位找到两个日期之间的日期差异?

How can I find date difference between two dates in terms of seconds in PostgreSQL?

没有像 SQL-Server 那样以秒为单位给出日期差异的函数:

There is no function for giving date difference in terms of seconds like in SQL-Server:

DATE_DIFF(second, '2011-12-30 09:55:56', '2011-12-30 08:54:55')

请帮我在 PostgreSQL 中也有这个

Please help me to have this in PostgreSQL too

推荐答案

首先，日期需要是 timestamp 类型的值(所以附加 ::timestamp 如果你'只是将它们指定为字符串文字).

First, the dates need to be values of timestamp type (so append ::timestamp if you're just specifying them as string literals).

如果你减去两个时间戳，结果是 interval 类型，它描述了一个持续时间(以小时、分钟、秒等为单位)你可以使用 extract(epoch from interval_value) 将间隔转换为绝对秒数.

If you subtract two timestamps, the result is of interval type, which describes a duration of time (in hours, minutes, seconds etc.) You can use extract(epoch from interval_value) to convert the interval into an absolute number of seconds.

所以，把所有这些放在一起:

So, putting that all together:

select extract(epoch from ('2011-12-30 09:55:56'::timestamp - '2011-12-30 08:54:55'::timestamp));

请记住，::timestamp 仅用于将字符串文字转换为时间戳:例如，如果您使用时间戳列的值，则不需要它.

Remember that the ::timestamp is only needed to convert the string literal to a timestamp: you don't need it if you're using the value of a timestamp column, for example.

这篇关于基于秒的 Postregsql 日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！