TimestampRange

TimestampRange.java

package org.nemesis;

import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.eclipse.jetty.server.Server;
import org.eclipse.jetty.servlet.ServletContextHandler;
import org.eclipse.jetty.servlet.ServletHolder;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import java.io.IOException;

public class Main {
    private static final String URL = "jdbc:postgresql://localhost:5432/demo_db";
    private static final String USER = "demo";
    private static final String PASSWORD = "demo";

    // SQL with ? placeholders, 2 per parameter to handle "IS NULL OR ..."
    private static final String QUERY_TRANSACTIONS =
            "SELECT * FROM transactions " +
            "WHERE (?::timestamp IS NULL OR created_at >= ?) " +   // from_ts
            "AND (?::timestamp IS NULL OR created_at <= ?) " +     // to_ts
            "ORDER BY created_at ASC";

    private static final String QUERY_COUNT =
            "SELECT COUNT(*) AS total_transactions " +
            "FROM transactions " +
            "WHERE (?::timestamp IS NULL OR created_at >= ?) " +   // from_ts
            "AND (?::timestamp IS NULL OR created_at <= ?)";       // to_ts

    public static void main(String[] args) {
        LocalDateTime fromTs = LocalDateTime.of(2026, 2, 26, 16, 50); // example: 1 hour ago
        LocalDateTime toTs = fromTs.plusMinutes(5);                 // example: now

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {

            // --- Run transaction count query ---
            long count = getTransactionCount(conn, fromTs, toTs);
            System.out.println("Total transactions: " + count);

            // --- Run transaction list query ---
            List<Transaction> transactions = getTransactions(conn, fromTs, toTs);
            transactions.forEach(System.out::println);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static long getTransactionCount(Connection conn, LocalDateTime fromTs, LocalDateTime toTs) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(QUERY_COUNT)) {
            bindTimestamps(stmt, fromTs, toTs);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) return rs.getLong("total_transactions");
            }
        }
        return 0;
    }

    private static List<Transaction> getTransactions(Connection conn, LocalDateTime fromTs, LocalDateTime toTs) throws SQLException {
        List<Transaction> list = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(QUERY_TRANSACTIONS)) {
            bindTimestamps(stmt, fromTs, toTs);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Transaction t = new Transaction(
                            rs.getLong("id"),
                            rs.getObject("transaction_id", java.util.UUID.class),
                            rs.getBigDecimal("amount"),
                            rs.getString("currency"),
                            rs.getString("description"),
                            rs.getString("status"),
                            rs.getTimestamp("created_at").toLocalDateTime()
                    );
                    list.add(t);
                }
            }
        }
        return list;
    }

    // Helper method to bind the 4 timestamp parameters
    private static void bindTimestamps(PreparedStatement stmt, LocalDateTime fromTs, LocalDateTime toTs) throws SQLException {
        // from_ts: (? IS NULL OR created_at >= ?)
        if (fromTs != null) {
            stmt.setTimestamp(1, Timestamp.valueOf(fromTs));
            stmt.setTimestamp(2, Timestamp.valueOf(fromTs));
        } else {
            stmt.setNull(1, Types.TIMESTAMP);
            stmt.setNull(2, Types.TIMESTAMP);
        }

        // to_ts: (? IS NULL OR created_at < ?)
        if (toTs != null) {
            stmt.setTimestamp(3, Timestamp.valueOf(toTs));
            stmt.setTimestamp(4, Timestamp.valueOf(toTs));
        } else {
            stmt.setNull(3, Types.TIMESTAMP);
            stmt.setNull(4, Types.TIMESTAMP);
        }
    }

    public record Transaction(
            long id,
            java.util.UUID transactionId,
            java.math.BigDecimal amount,
            String currency,
            String description,
            String status,
            LocalDateTime createdAt
    ) {}
}