PostgreSQL：从列值将时间间隔添加到时间戳记

本文介绍了PostgreSQL：从列值将时间间隔添加到时间戳记的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要添加带有时间戳的整数列的分钟数才能与另一列进行比较。

I need to add minutes coming from an integer column with a timestamp to compare to another column.

这里是一个示例：

Here's an example:

SELECT t1.id_liame, t1.id_table, t1.periodicidade , t3.data_extracao, CASE WHEN(NOW() < (e.data_extracao + INTERVAL t1.periodicidade || ' MINUTES')) THEN 'yes' ELSE 'no' END FROM table1 as t1 LEFT JOIN liame_has_extracao as t2 USING(id_liame) LEFT JOIN extracao as t3 USING(id_extracao)

l.periodicidade 是整数（分钟） 我需要验证 data_extracao（timestamp）是否大于 NOW（）+ l.periodicidade（整数-分钟）。

l.periodicidade is integer (minutes) I need to verify if data_extracao(timestamp) is greater then NOW() + l.periodicidade(integer - minutes).

我该怎么做？

推荐答案

您可以这样编写查询：

SELECT t1.id_liame, t1.id_table, t1.periodicidade, t3.data_extracao, CASE WHEN(NOW() < (t3.data_extracao + (INTERVAL '1 min' * t1.periodicidade))) THEN 'yes' ELSE 'no' END FROM table1 AS t1 LEFT JOIN liame_has_extracao AS t2 USING(id_liame) LEFT JOIN extracao AS t3 USING(id_extracao)

可以看到，您可以将间隔与整数相乘，因此使用 INTERVAL'1分钟'定义基本单位并乘以实际时间间隔。

As you can see, you can multiply intervals with integers so with INTERVAL '1 minute' you define your base unit and multiply your actual time interval.

希望有帮助