Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Substract datetime to get days and hour in MySQL

I have a join table, on the table I want to join with the main table there is a datetime column name delivery_date_to, I want to substract delivery_date_to to current date time and the substraction result will be in days and hour format (example: 2 days 23 hour) as a record. Here's how I join the 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")
like image 565
mileven Avatar asked Apr 19 '18 10:04

mileven


1 Answers

Try this solution:

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")

Demo :

http://sqlfiddle.com/#!9/9283a8/5

like image 128
Nishant Gupta Avatar answered Oct 13 '22 09:10

Nishant Gupta