How to convert number to time in looker

[APrabhu26]

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.

[dsimeonova]

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

[aprabhu]

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.