Issue working with time fields from BigQuery inside Dimension in LookML

[juan_landaeta-1]

I have a TIME field inside my source table in BigQuery that have the time (hh : mm :ss) of my transactions:

When created the view in LookML based on this table, automatically looker creates a Dimension like this:

  dimension: ID_HORA {
    type: date_time
    sql: ${TABLE}.ID_HORA ;;
    }
    

But when trying to use this dimension from the explore to build a New Tile I got this error in the underlying SQL query:

 Like Looker is using FORMAT_TIMESTAMP function on this dimension and getting syntax error for arguments types: TIME.

Is any workaround or a solution to avoid this SQL error on my underlying query to BigQuery? I’m kind of new at LookML and Looker in general.

Thanks community. 

[Andrea_Calfuman · Accepted solution]

 Hi Juan, 

Try with type “string” and in sql: case(id_hora as time).

Good luck

[juan_landaeta-1]

Thanks @Andrea_Calfuman this works fine!!

[Roderick]

Nice, @Andrea_Calfuman thanks for helping out in the community!