如何在日期和时间之间使用mysql，如果日期和时间有不同的字段在表中?

I have table reservasi with structure data like this:

我有像这样的结构数据表预订:

id_reservasi
id_reservasi
package_name <- varchar
package_name < - varchar
date_check_in <- varchar format (d-m-Y)
<- varchar格式(d-m-Y)
time_check_in <- varchar format (h:i:s)
<- varchar格式(h:i:s)
price <- varchar
价格< - varchar

I want select data with filter between from date and time from previous data reservasi to Until the time specified.

我想要使用过滤器从日期到时间从以前的数据预留到指定的时间之间选择数据。

For now, I use this sql query:

现在，我使用这个sql查询:

SELECT SUM(price) AS total
FROM reservasi 
WHERE date(str_to_date(date_check_in,'%d-%m-%Y')) BETWEEN date(str_to_date('$date_check_in_rekap','%d-%m-%Y')) AND date(str_to_date('$date_now','%d-%m-%Y') 
AND STR_TO_DATE(time_check_in, '%H:%i:%s') BETWEEN STR_TO_DATE('$time_check_in_rekap', '%H:%i:%s') AND STR_TO_DATE('$time_now', '%H:%i:%s')

But it don't show any data... How to use BETWEEN correctly?

但它没有显示任何数据……如何正确使用?

2 个解决方案

#1

edit, PDO version :

编辑,PDO版本:

$cek_pembelian=$this->pdo->prepare("
    SELECT 
        SUM(netto_bersih) AS netto_beli, 
        SUM(jumlah_dibayar) AS rupiah_beli 
    FROM p_penerimaan_tbs 
    WHERE str_to_date(CONCAT(tanggal,' ',jam_keluar) ,'%d-%m-%Y %H:%i:%s') 
        BETWEEN str_to_date(:datetime_start ,'%d-%m-%Y %H:%i:%s') AND now()
        ");
$cek_pembelian->execute(array(
    ':datetime_start'=>$tanggal_rekap .' '. $jam_rekap
    ));

This is what you want I think ?

这就是你想要的，我想?

SQL Fiddle

SQL小提琴

MySQL 5.6 Schema Setup:

MySQL 5.6模式设置:

Query 1:

查询1:

SELECT SUM(price) AS total
FROM t 
WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s')
BETWEEN '2017-01-24 18:20:33' AND now()

Results:

结果:

| total |
|-------|
|   300 |

#2

Thanks for query from @Blag

感谢@Blag的查询

Work Perfect...

完美的工作……

Edited Mysql Query :

编辑Mysql查询:

SELECT SUM(price) AS total FROM t WHERE str_to_date(CONCAT(date_check_in,' ',time_check_in) ,'%d-%m-%Y %H:%i:%s') BETWEEN str_to_date('$date_check_in_previous $time_check_in_previous') AND now()

从str_to_date(CONCAT(date_check_in，'，time_check_in)、'%d-%m-%Y %H:%i:%s')和now()之间的t中选择SUM(price)

#1