Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to get timestamp dimension in a MMDDYYY format?

  • 15 October 2015
  • 2 replies
  • 3280 views
S

Looker only seems to have these formats. Thanks!

2 replies

lloydtabb
Rank
Userlevel 6
Badge

Most SQL dialects have a DATE_FORMAT function or equivalent. For example in mysql you might



- dimension: date_field_as_mmddyyy


  sql: DATE_FORMAT(${TABLE}.date_field, "%m%d%Y")


Some dialects don’t have a function like this (I just looked at Impala it doesn’t appear to have one) you might have to use some string parsing.



- dimension: date_field_as_mmddyyy


  sql: |


     CONCAT(


         SUBSTRING( to_date(${TABLE}.date_field), 5,2),


         SUBSTRING( to_date(${TABLE}.date_field), 7,2),


         SUBSTRING( to_date(${TABLE}.date_field), 1,4)


     )
lloydtabb
Rank
Userlevel 6
Badge

@Sam_Menza I just realized you might be looking to get data input parse data in this format and make it a normal date field. The problem with these fields will be that they won’t be very optimized when filtering on the, but they should work just fine in all other respects.



For MySQL



- dimension_group: funky_date


  type: time


  timeframes: [date, week, month, year]


  sql: STR_TO_DATE( ${TABLE}.date_field, '%m%d%Y')


  datatype: date


Or Impala



- dimension_group: funky_date


  type: time


  timeframes: [date, week, month, year]


  sql: |


     DATE( 


        CONCAT(


           SUBSTRING( to_date(${TABLE}.date_field), 5,4), '-',


           SUBSTRING( to_date(${TABLE}.date_field), 1,2), '-',


           SUBSTRING( to_date(${TABLE}.date_field), 3,2),


         )


       )


  datatype: date

Reply


Terms of Service