PostgreSQL:从列值将时间间隔添加到时间戳记 [英] PostgreSQL: Add Interval to Timestamp from column value
本文介绍了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.
希望有帮助
这篇关于PostgreSQL:从列值将时间间隔添加到时间戳记的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文