业务场景:
对时间进行操作,比如获取5分钟之前的时间,是 now() + '-5 minute',但是有时后面的间隔值是动态的,需要动态拼装,这是就需要把拼装好的字符串转变为时间间隔类型interval,如下:
SELECT
(now() +
(-(CASE
WHEN configItem.Struct_Pick_Freq IS NULL OR configItem.Struct_Pick_Freq = ''
THEN 15 * 2
ELSE CAST (configItem.Struct_Pick_Freq AS NUMERIC ) * 2
END) || ' MINUTE')::INTERVAL
) COLLECT_DATE,
T.DATA_TIME,
record.ALARM_START_TIME,
configItem.ID,
configItem.Struct_Site
FROM
M_MONI_SITE_CONFIG_ITEM configItem,
M_MONI_DATA T
LEFT JOIN
MONI_UNNORMAL_RECORD record
ON
T.site_item = record.CONFIG_ITEM_ID
AND
record.ALARM_END_TIME IS NULL
WHERE
T.site_item = configItem.ID
参考链接: