mysql - 计算两个数据库字段之间的差异后获取小于特定 "date time"的记录

Question

我需要获取特定日期或特定日期持续时间内小于/大于/等于给定时间(行程时间)(小时:分钟:秒)的所有记录。为此，我编写了一个查询，但它不起作用。我的查询和数据库结构如下，请帮助。

`SELECT vt.*, vt.id AS tripid,voi.vehicle_id,voi.fuelissue_id,voi.date, voi.driver_id,
voi.driver_name,voi.time_out,voi.time_in,voi.meter_reading_out,
voi.meter_reading_in ,voi.`departure_from`,voi.location_comments,
voi.reason,voi.date_in 
FROM vehicles_out_in voi 
INNER JOIN vehicle_trips vt 
ON voi.id=vt.voi_id 
WHERE 1=1 
AND TIMESTAMP(voi.date,voi.time_out)>=TIMESTAMP('2013-10-20','01:00:00') 
AND TIMESTAMP(voi.date,voi.time_out)<=TIMESTAMP('2013-10-25','06:00:00')
AND (TIMESTAMP(voi.date_in,voi.time_in)-TIMESTAMP(voi.date,voi.time_out)) <= '02:00:00') 
ORDER BY voi.id DESC`

数据库结构:

`tbl1 : vehicles_out_in
driver_id           
driver_name         
category_id         
vehicle_id          
date                    
leavefor_location   
am                  
zo                  
time_out            
meter_reading_out   
reason                  
date_in                 
time_in                 
meter_reading_in    
departure_from          
returned_in         
fuelissue_id            
expected_trips          
reasonfor_lesstrips 
actual_trips            
vehicle_status          
location_comments`

   `tbl2: vehicle_trips
    id                     
    voi_id                 
    time_trip          
    date_trip          
    dumping_site           
    start_reading          
    meter_reading_site     
    total_weight           
    empty_weight           
    net_weight         
    receipt                
    uc_id`

Answer 1

您构建 TIMESTAMP 的方式不太正确。

假设您想要使用 voi.time_out 获取特定时间点之间或特定持续时间内的所有记录。尝试用此替换查询的最后 4 行(假设 time_out 在 time_in 之后):

AND 
(
 TIMESTAMP(concat(voi.date,,' ',voi.time_out)) 
  between TIMESTAMP('2013-10-20 01:00:00') 
  and     TIMESTAMP('2013-10-25 06:00:00')
 OR
 TIMESTAMPDIFF(SECOND,concat(voi.date_in,' ',voi.time_in),concat(voi.date,' ',voi.time_out)) <= 7200
)
ORDER BY voi.id DESC