package com.timescale.java;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
public class Main {
public static void main(String... args) {
final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
try (var conn = DriverManager.getConnection(connUrl)) {
createSchema(conn);
insertData(conn);
executeQueries(conn);
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
}
private static void createSchema(final Connection conn) throws SQLException {
try (var stmt = conn.createStatement()) {
stmt.execute("""
CREATE TABLE sensors (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL,
location TEXT NOT NULL
)
""");
}
try (var stmt = conn.createStatement()) {
stmt.execute("""
CREATE TABLE sensor_data (
time TIMESTAMPTZ NOT NULL,
sensor_id INTEGER REFERENCES sensors (id),
value DOUBLE PRECISION
)
""");
}
try (var stmt = conn.createStatement()) {
stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
}
}
private static void insertData(final Connection conn) throws SQLException {
final List<Sensor> sensors = List.of(
new Sensor("temperature", "bedroom"),
new Sensor("temperature", "living room"),
new Sensor("temperature", "outside"),
new Sensor("humidity", "kitchen"),
new Sensor("humidity", "outside"));
for (final var sensor : sensors) {
try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
stmt.setString(1, sensor.type());
stmt.setString(2, sensor.location());
stmt.executeUpdate();
}
}
final var sensorDataCount = 100;
final var insertBatchSize = 10;
try (var stmt = conn.prepareStatement("""
INSERT INTO sensor_data (time, sensor_id, value)
VALUES (
generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
floor(random() * 4 + 1)::INTEGER,
random()
)
""")) {
for (int i = 0; i < sensorDataCount; i++) {
stmt.addBatch();
if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
stmt.executeBatch();
}
}
}
}
private static void executeQueries(final Connection conn) throws SQLException {
try (var stmt = conn.prepareStatement("""
SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
FROM sensor_data
JOIN sensors ON sensors.id = sensor_data.sensor_id
WHERE sensors.type = ? AND sensors.location = ?
GROUP BY bucket
ORDER BY bucket DESC
""")) {
stmt.setString(1, "temperature");
stmt.setString(2, "living room");
try (var rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
}
}
}
}
private record Sensor(String type, String location) {
}
}
