SQL PostgreSQL 的时间戳差异（以小时为单位）

Timestamp Difference In Hours for PostgreSQL

提问by TheLizardKing

Is there a TIMESTAMPDIFF()equivalent for PostgreSQL?

有TIMESTAMPDIFF()PostgreSQL的等价物吗？

I know I can subtract two timestamps to get a postgresql INTERVAL. I just want the difference between the two timestamps in in hours represented by an INT.

我知道我可以减去两个时间戳以获得 postgresql INTERVAL。我只想要以 INT 表示的以小时为单位的两个时间戳之间的差异。

I can do this in MySQL like this:

我可以在 MySQL 中这样做：

TIMESTAMPDIFF(HOUR, links.created, NOW())

I just need the difference between two timestamps in hours represented as an integer.

我只需要以整数表示的两个时间戳之间的差异（以小时为单位）。

Solution works for me:

解决方案对我有用：

SELECT "links_link"."created",
"links_link"."title",
(EXTRACT(EPOCH FROM current_timestamp - "links_link"."created")/3600)::Integer AS "age" 
FROM "links_link"

回答by Michael Krelin - hacker

The first things popping up

出现的第一件事

EXTRACT(EPOCH FROM current_timestamp-somedate)/3600

May not be pretty, but unblocks the road. Could be prettier if division of interval by interval was defined.

可能不漂亮，但可以疏通道路。如果定义了间隔除法，可能会更漂亮。

Edit: if you want it greater than zero either use abs or greatest(...,0). Whichever suits your intention.

编辑：如果您希望它大于零，请使用 abs 或 best(...,0)。哪个适合你的意图。

Edit++: the reason why I didn't use ageis that agewith a single argument, to quote the documentation: Subtract from current_date (at midnight). Meaning you don't get an accurate "age" unless running at midnight. Right now it's almost 1am here:

Edit++：我没有使用的原因age是age用一个参数引用文档：Subtract from current_date (at午夜)。这意味着除非在午夜跑步，否则您无法获得准确的“年龄”。现在已经快凌晨1点了：

select age(current_timestamp);
       age        
------------------
 -00:52:40.826309
(1 row)

回答by Eric Leschinski

Get fields where a timestamp is greater than date in postgresql:

在 postgresql 中获取时间戳大于日期的字段：

SELECT * from yourtable 
WHERE your_timestamp_field > to_date('05 Dec 2000', 'DD Mon YYYY');

Subtract minutes from timestamp in postgresql:

从 postgresql 中的时间戳中减去分钟：

SELECT * from yourtable 
WHERE your_timestamp_field > current_timestamp - interval '5 minutes'

Subtract hours from timestamp in postgresql:

从 postgresql 中的时间戳中减去小时数：

SELECT * from yourtable 
WHERE your_timestamp_field > current_timestamp - interval '5 hours'

回答by ShawnFumo

Michael Krelin's answer is close is not entirely safe, since it can be wrong in rare situations. The problem is that intervals in PostgreSQL do not have context with regards to things like daylight savings. Intervals store things internally as months, days, and seconds. Months aren't an issue in this case since subtracting two timestamps just use days and seconds but 'days' can be a problem.

Michael Krelin 的回答很接近并不完全安全，因为在极少数情况下它可能是错误的。问题是 PostgreSQL 中的间隔没有关于夏令时等内容的上下文。间隔在内部以月、日和秒的形式存储内容。在这种情况下，月份不是问题，因为减去两个时间戳只使用天和秒，但“天”可能是一个问题。

If your subtraction involves daylight savings change-overs, a particular day might be considered 23 or 25 hours respectively. The interval will take that into account, which is useful for knowing the amount of days that passed in the symbolic sense but it would give an incorrect number of the actual hours that passed. Epoch on the interval will just multiply all days by 24 hours.

如果您的减法涉及夏令时转换，则特定一天可能分别被视为 23 或 25 小时。间隔将考虑到这一点，这对于了解象征意义上过去的天数很有用，但它会给出不正确的实际过去小时数。时间间隔上的纪元只会将所有天数乘以 24 小时。

For example, if a full 'short' day passes and an additional hour of the next day, the interval will be recorded as one day and one hour. Which converted to epoch/3600 is 25 hours. But in reality 23 hours + 1 hour should be a total of 24 hours.

例如，如果一个完整的“短”日过去并且第二天又增加了一个小时，则该间隔将被记录为一天零一小时。其中转换为 epoch/3600 是 25 小时。但实际上 23 小时 + 1 小时应该是总共 24 小时。

So the safer method is:

所以比较安全的方法是：

(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM somedate))/3600

As Michael mentioned in his follow-up comment, you'll also probably want to use floor() or round() to get the result as an integer value.

正如迈克尔在他的后续评论中提到的，您可能还想使用 floor() 或 round() 将结果作为整数值。

回答by araqnid

You can use the "extract" or "date_part" functions on intervals as well as timestamps, but I don't think that does what you want. For example, it gives 3 for an interval of '2 days, 3 hours'. However, you can convert an interval to a number of seconds by specifying 'epoch' as the time element you want: extract(epoch from '2 days, 3 hours'::interval)returns 183600 (which you then divide by 3600 to convert seconds to hours).

您可以在时间间隔和时间戳上使用“extract”或“date_part”函数，但我认为这不是您想要的。例如，它为“2 天 3 小时”的间隔给出 3。但是，您可以通过将 'epoch' 指定为所需的时间元素来将间隔转换为秒数：extract(epoch from '2 days, 3 hours'::interval)返回 183600（然后除以 3600 以将秒转换为小时）。

So, putting this all together, you get basically Michael's answer: extract(epoch from timestamp1 - timestamp2)/3600. Since you don't seem to care about which timestamp precedes which, you probably want to wrap that in abs:

所以，把这一切放在一起，你基本上得到了迈克尔的答案：extract(epoch from timestamp1 - timestamp2)/3600。由于您似乎并不关心哪个时间戳在哪个之前，您可能希望将其包装在abs：

SELECT abs(extract(epoch from timestamp1 - timestamp2)/3600)

回答by Eric Leschinski

postgresql get seconds difference between timestamps

postgresql 获取时间戳之间的秒差

SELECT (
    (extract (epoch from (
        '2012-01-01 18:25:00'::timestamp - '2012-01-01 18:25:02'::timestamp
                         )
             )
    )
)::integer

which prints:

打印：

-2

Because the timestamps are two seconds apart. Take the number and divide by 60 to get minutes, divide by 60 again to get hours.

因为时间戳相隔两秒。取数字除以 60 得到分钟，再除以 60 得到小时。

回答by Peter Sysko

This might sound crazy to a lot of developers who like to take advantage of database functions,

对于许多喜欢利用数据库功能的开发人员来说，这可能听起来很疯狂，

But after exhaustive problems thinking, creating and bugfixing applications for mysql and postgrsql with php comparing date functions, I've come to the conclusion (for myself), that the easiest way, that is the simplest with less SQL headaches is not to take advantage of any of them.

但是在使用php比较日期函数对mysql和postgrsql的应用程序进行了详尽的思考、创建和错误修复后，我得出了结论（对我自己而言），最简单的方法，即最简单的SQL问题较少的方法是不要利用其中任何一个。

Why? because if you are developing in a middleware language like PHP, PHP has all of these functions, and they are easier to implement in the application ode as comparing integers. PostgreSQL timestamp is NOT == UNIX TIMESTAMP and MySQL's UNIX TIMESTAMP is NOT PostgresQL's or Oracles timestamp.. it gets harder to port if you use database timestamps..

为什么？因为如果您使用 PHP 之类的中间件语言进行开发，PHP 具有所有这些功能，并且它们在应用程序 ode 中更容易实现，例如比较整数。PostgreSQL 时间戳不是 == UNIX TIMESTAMP，而 MySQL 的 UNIX TIMESTAMP 不是 PostgresQL 或 Oracles 的时间戳。如果使用数据库时间戳，移植会变得更加困难。

so just use an integer, not a timestamp, as the number of seconds since january 1st 1970 midnight. and never mind database timestamps. , and use gmdate() and store everything as gmt time to avoid timezone issues.

所以只需使用整数，而不是时间戳，作为自 1970 年 1 月 1 日午夜以来的秒数。不要介意数据库时间戳。，并使用 gmdate() 并将所有内容存储为 gmt 时间以避免时区问题。

if you need to search, sort or compare the day from other data, or the month or the year or the day of the week, or anything, in your application, and INTEGER datatype for time_day, time_hour, time_seconds.. or whatever you wnat to index to be searched will make for smoother and more portable databases. you can just use one field, in most instances: INTEGER time_created NOT NULL

如果您需要从其他数据中搜索、排序或比较日期，或者您的应用程序中的月份、年份或星期几，或任何其他数据，以及 time_day、time_hour、time_seconds 的 INTEGER 数据类型……或任何您想要的数据要搜索的索引将使数据库更流畅、更便携。在大多数情况下，您可以只使用一个字段：INTEGER time_created NOT NULL

(more fields in your database row is the only drawback to this solution that i have found, and that doesnt cause as many headaches, or cups of coffee :)

（数据库行中的更多字段是我发现的此解决方案的唯一缺点，并且不会引起那么多的头痛或咖啡杯:)

php's date functions are outstanding to compare dates, but in mysql or postgresql, comparing dates ? nah.. use integer sql comparisons

php 的日期函数非常适合比较日期，但是在 mysql 或 postgresql 中，比较日期？不.. 使用整数 sql 比较

i realize it may SEEM easier to use CURRENT_TIMESTAMP on an insert function. HA! don't be fooled.

我意识到在插入函数上使用 CURRENT_TIMESTAMP 似乎更容易。哈！不要被愚弄。

You cant do DELETE FROM SESSION_TABLE WHERE time-initialized < '2 days'if time-intitialized is a postgresql timestamp. but you CAN do:

DELETE FROM SESSION_TABLE WHERE time-initialized < '2 days'如果时间初始化是 postgresql 时间戳，你就不能这样做。但你可以这样做：

DELETE FROM SESSION_TABLE WHERE time_initialized < '$yesterday'

DELETE FROM SESSION_TABLE WHERE time_initialized < '$yesterday'

As long as you set $yesterday in php as the integer of seconds since 1970 that yesterday was.

只要您在 php 中将 $yesterday 设置为自昨天 1970 年以来的秒数。

This is easier housekeeping of session records than comparing timestamps in postgresql select statements.

这比在 postgresql 选择语句中比较时间戳更容易处理会话记录。

SELECT age(), SELECT extract(), and asbtime are headaches in an of themselves. this is just my opinion.

SELECT age()、SELECT extract() 和 asbtime 本身就令人头疼。这只是我的看法。

you can do addition, substraction, <, >, all with php date objects

您可以使用 php 日期对象进行加法、减法、<、>

_peter_sysko U4EA Networks, Inc.

_peter_sysko U4EA Networks, Inc.

回答by Danix D5

extract(hour from age(now(),links.created))gives you a floor-rounded count of the hour difference.

extract(hour from age(now(),links.created))为您提供时差的舍入计数。