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.

You get a minus sign before negative numbers. That's pretty much the standard. — Álvaro González, Nov 02 '20 at 15:52

Tim Biegeleisen · Accepted Answer · 2020-11-02T15:49:15.050

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.

Ah that makes sense now. Thank you very much for taking the time to change it for me and explaining why it was displaying like that! — Tayler, Nov 02 '20 at 15:50

How do I change the TIMESTAMPDIFF date format in MySQL?

1 Answers1