CODEWITHSUNDEEP
mysql
aleksikallio
aleksikallio

Reputation: 1932

How to return time from IF-clause

I'm having trouble outputting a time from a conditional select. The aim is to subtract 30min if the time is < 6h.

Here is a demo:

SELECT TIME(start_time) AS start,
    TIME(end_time) AS end,
    TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS durationSecs,
    IF(
        TIMEDIFF(end_time, start_time) >= "06:00:00",
        SUBTIME(TIMEDIFF(end_time, start_time), 30:0:0),
        TIMEDIFF(end_time, start_time)
    ) AS duration
FROM [...]

This code returns what I think is a string representation of the TIME object itself, for example 30303a30353a3030 .

How can I get this to output the actual times?

Upvotes: 0

Views: 60

Answers (1)

reko_t
reko_t

Reputation: 56430

SELECT TIME(start_time) AS start,
    TIME(end_time) AS end,
    TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS durationSecs,
    IF(
        TIMEDIFF(end_time, start_time) >= "06:00:00",
        TIMEDIFF(end_time - INTERVAL 30 MINUTE, start_time),
        TIMEDIFF(end_time, start_time)
    ) AS duration
FROM [...]

Upvotes: 2

Related Questions