1. PostgreSQL根据动态表名查询近24小时数据

一、 查询近24小时数据

1. 调用方法

public void findData(String tableName) {
	// tableName 需要进行 双引号的拼接 否则会出现转义
	tableName = "\"" + "\"";
	List<Map<String, String>> list = testMapper.checkData(tableName);
}

2. 需要执行的sql

<!--statementType="STATEMENT" (非预编译) -->
<select id="checkData" resultType="java.util.Map" statementType="STATEMENT">
        select *
        # 因为开启了非预编译所以使用${} 表名的拼接
        from ${tableName}
        where "time" :: TIMESTAMP >= ( to_char( now( ) :: TIMESTAMP, 'yyyy-MM-dd HH24:00:00' ) :: TIMESTAMP - INTERVAL '24 hour' )
        AND "time" :: TIMESTAMP &lt; ( to_char( now( ) :: TIMESTAMP, 'yyyy-MM-dd HH24:00:00' ) :: TIMESTAMP )
    </select>

二、根据动态表白删除24小时之前的数据

1. 调用函数

public void deleteByName(String name) {
	test.deledeByName("\"" + name + "\"");
}

2. sql

 <!--删除24小时之前的数据-->
    <delete id="deledeByName" statementType="STATEMENT">
        delete from ${name}
        where "time" :: TIMESTAMP &lt; ( to_char( now( ) :: TIMESTAMP, 'yyyy-MM-dd HH24:00:00' ) :: TIMESTAMP - INTERVAL '24 hour')
    </delete>