Hello Team,
I am query regarding how to convert measure with number data type to time data type in the form of hh:mm::ss.
I have tried to achieve the desired format by using table calculation.
By using table calculation where here the filed is "number"
concat(floor(floor(${number}/60)/60),":", floor(${number}/60) - floor(floor(${number}/60)/60)*60, ":", ${number}-(floor(${number}/60)*60))
By using LookML code(sample code) also we can achieve this
dimension: number {
type: number
sql: 3600;;
}
measure: number_to_time{
sql:concat(floor(floor(${number}/60)/60),":", floor(${number}/60) - floor(floor(${number}/60)/60)*60, ":", ${number}-(floor(${number}/60)*60)) ;;
}
Here the output will be --> 1:0:0
May I know how to achieve the same in time data type, where the above data type is string data type as output.
Hey @APrabhu26 !
It depends on which database you are using. Let's say that you are using BigQuery:
dimension: seconds {
type: number
sql: 3600 ;;
}
dimension: hh_mm_ss {
sql: FORMAT_TIME('%H:%M:%E*S', TIME(TIMESTAMP_ADD(TIMESTAMP(current_date()), INTERVAL ${seconds} SECOND))) ;;
}
Every SQL dialect it's different but basically here the idea is to convert this number that we know that is in seconds to hh:mm:ss format.
Pay attention that is dimension the new field that i've created upon the number.
Not sure the purpose of the measure and if you need this as measure, but feel free to continue explaining the use case and if it's any other database, share it so I can give you a better response ☺️.
Hope it was helpful
dimension: number {
type: number
sql: 3600;;
}
dimension: hh_mm_ss {
sql: TIME_FORMAT(SEC_TO_TIME(${number}), '%H:%i:%s');;
}
I have tried in looker where my dialect MySQL and able to achieve the expected.