How do I change the TIMESTAMPDIFF date format in MySQL?

Question

I'm using the value of one date table, minus the other date table to create a total time date table. However, I don't like the format.

CONCAT(
        TIMESTAMPDIFF(day,TIME_END,TIME_START), 'd',
        MOD(TIMESTAMPDIFF(hour,TIME_END,TIME_START), 24), 'h',
        MOD(TIMESTAMPDIFF(minute,TIME_END,TIME_START), 60), 'm'
      ) AS Total_Times

Results appear like this:

• 0d0h-48m
• -2d-1h-7m
• 0d0h-33m
• 0d0h0m
• -1d-21h-47m

It seems that if the number is higher than zero, it'll add a hyphen next to it, otherwise it'll group whatever is next to it without a space. How can I get it to create a space between zero's and and remove hyphens to appear like this:

• 0d 0h 48m
• 2d 1h 7m
• 0d 0h 33m
• 0d 0h 0m
• 1d 21h 47m

Note, in the actual date columns (TIME_END,TIME_START) their values are written like 2020-11-02 15:32:28.

Answer 1

I think the real issue here is that sometimes you are placing TIME_START and TIME_END in the wrong positions. If you want to diff an earlier start time against a later end time, then the former should appear before the latter in the call to TIMESTAMPDIFF. Use this version:

CONCAT(
    TIMESTAMPDIFF(day, TIME_START, TIME_END), 'd ',
    MOD(TIMESTAMPDIFF(hour, TIME_START, TIME_END), 24), 'h ',
    MOD(TIMESTAMPDIFF(minute, TIME_START, TIME_END), 60), 'm'
) AS Total_Times

To comment further on your current output, e.g.

-2d-1h-7m

The dashes you see here are actually minus signs, not dashes. You should have swapped the order of TIME_START and TIME_END to get positive numbers.