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")
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With