java - 时间差作为结果集

Question

我遇到了麻烦，正在寻找解决方案。

情况如下:我执行查询以从 sql 数据库中的 View 获取信息，并将其显示在 xls 文件中。

问题是我在最后一列中，必须显示“开始日期(开始日期)”和“结束日期(结束日期)”之间的小时差。我已经尝试了很多事情，但没有找到解决方案。有人可以帮助我吗？

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.mavenproject2;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 *
 * @author Stagiaire
 */
public class DataExportService {
    ConnectDB newConnectionDB = new ConnectDB();

    public void getXls() throws Exception  {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            ConnectDB con = new ConnectDB();
            PreparedStatement pst = ConnectDB.getConnection().prepareStatement("");
            ResultSet rs = pst.executeQuery("SELECT * FROM v_facture_infos WHERE real_begin_date >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ) AND real_end_date < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )");
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("test");
            HSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell((short) 0).setCellValue("Date commencement");
            rowhead.createCell((short) 1).setCellValue("Date fin");
            rowhead.createCell((short) 2).setCellValue("Nom");
            rowhead.createCell((short) 3).setCellValue("Prénom");
            rowhead.createCell((short) 4).setCellValue("Heures");
            int i = 1;
            while (rs.next()){
                HSSFRow row = sheet.createRow((short) i);
                row.createCell((short) 0).setCellValue(rs.getDate("real_begin_date"));
                row.createCell((short) 1).setCellValue(rs.getDate("real_end_date"));
                //query total heure avec la différence de real begin date et real end date : SELECT TIMEDIFF(@real_begin_date, @real_end_date)
                row.createCell((short) 2).setCellValue(rs.getString("last_name"));
                row.createCell((short) 3).setCellValue(rs.getString("first_name"));               
                row.createCell((short) 4).setCellValue(rs.getString("SELECT TIMEDIFF(@real_begin_date, @real_end_date)"));
                i++;
            }
            String yemi = "C:\\Users\\Stagiaire\\Desktop\\test.xls";
            FileOutputStream fileOut = new FileOutputStream(yemi);
            workbook.write(fileOut);
            fileOut.close();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e1) {
            e1.printStackTrace();
        }
    }
}

Answer 1

尝试将其计算为 SQL 的一部分

ResultSet rs = pst.executeQuery("SELECT real_begin_date, real_end_date, first_name, last_name, TIMEDIFF(real_begin_date, real_end_date) FROM...");