在mysql中减去datetime得到days和hour

fwzugrvs 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(332)

我有一个联接表，在我想与主表联接的表上有一个datetime列名 delivery_date_to ，我想减去 delivery_date_to 到当前日期时间和减法结果将以天和小时格式（例如：2天23小时）作为记录。下面是我如何加入这张table，

SELECT marketplace_orders.entity_id as id, 
       sales_order.delivery_date_to as delivery_date_to,
       (deliver_date_to - current_time = xx days xx hour) as time_left
FROM marketplace_orders 
INNER JOIN sales_order
      ON sales_order.entity_id = marketplace_orders.order_id
      AND sales_order.status IN ("pending","processing")

mysql DateTime datetime-format left-join

来源：https://stackoverflow.com/questions/49918671/substract-datetime-to-get-days-and-hour-in-mysql

1条答案

按热度按时间

dly7yett1#

尝试此解决方案：

SELECT 
    marketplace_orders.entity_id as id, 
    sales_order.delivery_date_to as delivery_date_to,
    CONCAT(TIMESTAMPDIFF(Day,deliver_date_to,NOW()), ' days ',
    MOD(TIMESTAMPDIFF(HOUR,deliver_date_to,NOW()),24), ' hour')  as time_left
FROM marketplace_orders 
INNER JOIN sales_order
    ON sales_order.entity_id = marketplace_orders.order_id
       AND sales_order.status IN ("pending","processing")

演示：
http://sqlfiddle.com/#!9/9283a8/5号

赞(0）回复(0）举报 2021-06-26

我来回答

在mysql中减去datetime得到days和hour

1条答案

相关问题

热门标签

最新问答