数据库字符串类型RaceTime求和结果转时间格式的实现问询

First, let's break down the problem: your RaceTime field is stored as a string (representing total seconds or milliseconds), and summing it gives a numeric string. We need to convert this total to a human-readable HH:mm:ss format and compute the average race time.

Step 1: Update the SQL Query #

To calculate the average efficiently, we'll retrieve both the total sum of RaceTime and the number of records in one query:

SELECT SUM(RaceTime) AS total_race_time, COUNT(*) AS record_count FROM registrationevent;

Step 2: Java Code Implementation #

Here's the modified code with conversion logic and average calculation:

try {
    // Updated query to get total sum and record count
    String sql = "SELECT SUM(RaceTime) AS total_race_time, COUNT(*) AS record_count FROM registrationevent;";
    ps = con.prepareStatement(sql);
    rs = ps.executeQuery();
    
    if (rs.next()) {
        String totalStr = rs.getString("total_race_time");
        int recordCount = rs.getInt("record_count");
        
        // Handle case where no records exist
        if (totalStr == null || recordCount == 0) {
            avg.setText("No race time data available.");
            return;
        }
        
        // Parse the total sum string to a long (supports large values)
        long totalRaceTime;
        try {
            totalRaceTime = Long.parseLong(totalStr);
        } catch (NumberFormatException e) {
            avg.setText("Error: Invalid race time format in database.");
            e.printStackTrace();
            return;
        }
        
        // Convert total seconds to HH:mm:ss format
        String totalTime = convertToTimeFormat(totalRaceTime);
        // Calculate and convert average time
        String avgTime = convertToTimeFormat(Math.round((double) totalRaceTime / recordCount));
        
        // Display results
        avg.setText(String.format("Total Race Time: %s\nAverage Race Time: %s", totalTime, avgTime));
    }
} catch (Exception e) {
    JOptionPane.showMessageDialog(null, "Database Error: " + e.getMessage());
    e.printStackTrace();
}

// Helper method to convert total seconds to HH:mm:ss
private static String convertToTimeFormat(long totalSeconds) {
    long hours = totalSeconds / 3600;
    long remainingSeconds = totalSeconds % 3600;
    long minutes = remainingSeconds / 60;
    long seconds = remainingSeconds % 60;
    
    // Format to ensure two digits for each component
    return String.format("%02d:%02d:%02d", hours, minutes, seconds);
}

Key Notes: #

• Assumption: This code assumes RaceTime is stored as a string representing total seconds. If your RaceTime is in milliseconds, modify the conversion step by dividing totalRaceTime by 1000 first:

  long totalSeconds = totalRaceTime / 1000;
  String totalTime = convertToTimeFormat(totalSeconds);
  

• Error Handling: Added checks for missing data and invalid numeric strings to prevent crashes.
• Average Calculation: Uses double for division to preserve precision, then rounds to the nearest second for the final average format.

Alternative: If RaceTime is Stored as HH:mm:ss String #

If your RaceTime was originally stored in HH:mm:ss format (instead of numeric seconds), adjust the SQL query to convert each entry to seconds first (example for MySQL):

SELECT SUM(TIME_TO_SEC(RaceTime)) AS total_race_time, COUNT(*) AS record_count FROM registrationevent;

The rest of the Java code remains the same.

内容的提问来源于stack exchange，提问作者jjap