Question

How to get timestamp dimension in a MMDDYYY format?

  • 15 October 2015
  • 2 replies
  • 3280 views

Looker only seems to have these formats. Thanks!


2 replies

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)

)

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