How can I change hour to minutes in snowflake-CodePudding

I have a list of hours, showing like this 2.52 (meaning 2hours 52 minutes) 3 3.63
3.33
2.94
2.52 How can I convert this to # of minutes?

CodePudding user response：

I dont understand the value of "2.94" - why is it 2 hours and 94 minutes and not 3.34, which would be the same amount of overall minutes?

Based on above assumption, you can use POSITION and SUBSTRING to split the string into the hour- and minute-part and then do the maths. First part of below query is extracting the left side of the dot and is multiplying it with 60, second part is just putting the minutes on top.

WITH hours AS (SELECT 3.63 as hour_value UNION SELECT 3.33 UNION SELECT 2.94)
select substr(hour_value, 0, position('.' in hour_value))*60   substr(hour_value,position('.' in hour_value) 1) from hours;

In case the right side of the dot is NOT minutes, but the percentage of the whole hour, then you could just go with

select hour_value*60 from hours;

CodePudding user response：

Using TIME_FROM_PARTS:

SELECT col, TIME_FROM_PARTS(FLOOR(col), col % 1 * 60, 0)
FROM tab;

Sample:

3.33
FLOOR(3.33) -> 3h
3.33 % 1 * 60 -> 0.33 * 60 -> 20 min