CODEWITHSUNDEEP
sqlsqlite
Evil Dr Coconut
Evil Dr Coconut

Reputation: 45

Turning Milliseconds in Hours without DATETIME

I am running into trouble with SQL code for SQLite. It is a small homework problem I have gotten mostly figured out, but can't find the correct answer for converting an INT to something I can use ROUND with.

{sql connection=dbcon}
SELECT 
    genres.Name, 
    ROUND(SUM(CAST(Milliseconds AS DECIMAL(5, 2))) / 3600000, 2) AS 'Hours' 
FROM 
    genres
LEFT JOIN 
    tracks ON genres.GenreId = tracks.GenreId
GROUP BY 
    genres.GenreId

This code returns the answer of genres and their total summed track time. But it is required to get the time in hours rounded to 2 decimal points. My use of CAST is not flagged but ignored. Any help in understanding why would be appreciated. Thank you!

Edit: here are the query results

query results

Upvotes: 1

Views: 206

Answers (1)

forpas
forpas

Reputation: 164089

Actually, CAST(Milliseconds AS DECIMAL(5, 2)) does not cast Milliseconds to a floating point number (there is no DECIMAL data type in SQLite), so the result is an integer and the division performed afterwards will be an integer division which will truncate the result to an integer and ROUND() will do nothing.
Instead you should cast to REAL.
You can see this behavior here.

So, change to this:

ROUND(SUM(CAST(Milliseconds AS REAL)) / 3600000, 2) AS Hours

or:

ROUND(CAST(SUM(Milliseconds) AS REAL) / 3600000, 2) AS Hours

or, with an implicit conversion:

ROUND(SUM(Milliseconds) / 3600000.0, 2) AS Hours

Upvotes: 1

Related Questions