php - mysql代码中的时间变化

Question

我想更改 mysql 查询中的时间格式:

e_date_s = %m/%d/%Y
e_date_e = %m/%d/%Y

对此:

e_date_s = %Y/%m/%d
e_date_e = %Y/%m/%d

我的查询是:

$q3 = "SELECT c_m_vin, e_date_s,e_date_e FROM core 
    JOIN event_1 ON core.c_e_token = event_1.e_1_uniek WHERE ((e_date_s <= '$date') 
    AND (e_date_e >= '$date') AND (e_date_e LIKE '%$year%') 
    AND (c_m_vin = '$res1->m_vin'))";

我通过 UNIX_TIMESTAMP 看到了选项。有谁明白吗？

更新

$q3 = "
    SELECT c_m_vin, 
    DATE_FORMAT('e_date_s','%Y-%m-%d') AS e_date_s_new,
    DATE_FORMAT('e_date_e','%Y-%m-%d') AS e_date_e_new
    FROM core 
    JOIN event_1 
    ON core.c_e_token = event_1.e_1_uniek WHERE ((e_date_s_new <= '$date') AND (e_date_e_new >= '$date') AND (e_date_e_new LIKE '%$year%') AND (c_m_vin = '$res1->m_vin'))";        

Answer 1

如果您的列是实际的日期类型，您可以在 select 语句中使用 MySQL DATE_FORMAT 函数直接在查询中格式化日期。

SELECT DATE_FORMAT(e_date_s, "%Y/%m/%d") AS formatted_date
from...