1

In MySQL database i've inserted data into 'leaves' table:

+--------+---------+---------+-------------------------------+-----
|Id_LEAVE|ID_WORKER|    BEGIN_DATE        | END_DATE              | 
+--------+---------+------------+--------------------+------------+
| 4      |   26    | 2019-03-19 07:00:00  |2019-03-19 15:00:00    |
+--------+---------+---+----------------------+-------------------+ 
| 5      |   25    | 2019-03-20 07:00:00  |2019-03-21 15:00:00    |
+--------+---------+---------+----------------------+-------------+
| 6      |   21    | 2019-03-22 07:00:00  |2020-03-22 15:00:00    |
+--------+---------+---------+----------------------+-------------+

Then i'd like to display all of them with calculated leave time but without TIME_FORMAT and SEC_TO_TIME because i know it has a value limit. When i execute that below code:

select
  ID_LEAVE, ID_WORKER, BEGIN_DATE, END_DATE,
  concat_ws(' ',
    concat((sec DIV 60*60), 'h'),
    concat((sec DIV 60) % 60, 'm'),
    concat(sec % 60, 's')
  ) AS 'LEAVE TIME'
from (
select
  SUM(
    to_seconds(
      TIMEDIFF(END_DATE, BEGIN_DATE)
    )
  ) sec,
    ID_LEAVE,
    ID_WORKER,
    BEGIN_DATE,
    END_DATE
from
  leaves group by ID_LEAVE
) s;

Then i display all of them but in LEAVE TIME column displays only empty values:

+--------+---------+---------+------------+-----------------------+-----------+
|Id_LEAVE|ID_WORKER|    BEGIN_DATE        | END_DATE              |LEAVE_TIME | 
+--------+---------+------------+--------------------+------------+-----------+
| 4      |   26    | 2019-03-19 07:00:00  |2019-03-19 15:00:00    |           |
+--------+---------+---+----------------------+-------------------+-----------+
| 5      |   25    | 2019-03-20 07:00:00  |2019-03-21 15:00:00    |           |
+--------+---------+---------+----------------------+-------------+-----------+
| 6      |   21    | 2019-03-22 07:00:00  |2020-03-22 15:00:00    |           |
+--------+---------+---------+----------------------+-------------+-----------+

I have the question: Where is the error which doesn't display the values of LEAVE_TIME. Which lines of Mysql code should i fix? Thx for any help.

Johnyblumer2012
  • 11
  • 2
  • the query written above worked on my side, can you try `show warnings` after you issue the query? it may be something related to sql_mode in your side or something similar. – Zeid Al-Rashwani Jul 04 '19 at 12:51

1 Answers1

0

Try it, May be it can help

SELECT *,FROM_UNIXTIME(
    UNIX_TIMESTAMP(
        TIMEDIFF(END_DATE,BEGIN_DATE)
    ),
    '%h Hours,%i Minutes,%s Seconds') 
    as 'LEAVE TIME' 
FROM `leaves`
Harshwardhan Sharma
  • 261
  • 1
  • 12