How to take 3 hours out of a date

[Juliana_Martin1]

How to take 3 hours out of a date using LookeML?

[Eric_Lyons]

Hi @Juliana_Martin1,

We can use a sql function for subtract or add hours in a LookML dimension.

E.G.

  dimension: minus_three {
    sql: DATE_SUB(${created_raw}, INTERVAL 3 HOUR) ;;
  }

(you would want to add a type parameter and make sure the sql function works with the datatype you are passing into the function (TIMESTAMPADD() etc). 

 Or we can use a table calculation similar to add_hours(-3,${look.created_time}).

Please let me know if you have any questions.

Thanks,

Eric

[shan383]

This is great! I'm trying to use this to convert army time to a 12 hour standard but would like to add a case when to a dimension type:time. How would I write this to correspond with the timeframe interval time_of_day

[Juliana_Martin1]

Thank you very much Eric, you helped me a lot. It was what I needed.