Substract datetime to get days and hour in MySQL

Question

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

Refer https://stackoverflow.com/questions/2546053/mysql-difference-between-two-timestamps-in-days?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Sumesh TG, Apr 19 '18 at 10:33

Nishant Gupta · Accepted Answer · 2018-04-19T10:48:36.520

3

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

edited Apr 19 '18 at 10:48

answered Apr 19 '18 at 10:37

Nishant Gupta

3,533
1
11
18

`+` as concatenation operator this works in SQL-server.. In MySQL you need to use `CONCAT()` function – Raymond Nijland Apr 19 '18 at 10:41
@RaymondNijland have just edited the answer to reflect that. – Nick Apr 19 '18 at 10:42
@RaymondNijland ya I forget that I am writing the query in MYSQL thanks for the help. – Nishant Gupta Apr 19 '18 at 10:45
@Nick Thanks for the help. – Nishant Gupta Apr 19 '18 at 10:46

Substract datetime to get days and hour in MySQL

1 Answers1