Column has [object Object] values instead of dates

Looker Q&A Looker
1

Hello! I’m trying to convert a string column to a date. (We use Trino for querying the data in Looker)

The date column named “date_str” has dates like

2024-04-24-233510.065730

Here’s how I’m extracting the date, as a custom dimension, in the lkml file:

dimension: conv_date {
    type: time
    sql: parse_datetime(${TABLE}.date_str, 'yyyy-mm-dd-HHmmss.SSSSSS') ;;
}

However, in looker studio, the column is filled with values [object Object]

Screenshot 2024-04-25 at 1.29.09 AM.png
Screenshot 2024-04-25 at 1.29.09 AM.png1738×174 23.1 KB

Any pointers would be helpful!

2

Looks like you’ve done most of the heavy lifting already by writing the proper SQL!

I’m confused where the problem is. Your screenshot shows two columns, one with [object Object] and one with the correct value. Which one is “conv_date”?

3

date_str is on the right-hand column, the custom dimension titled conv_date is on the left.

4

Hey @ksure ! We have run into several issues with dates over the years, one of which I encountered earlier this year that gave me the result of [Object object] in my explores as well. Below are a couple of potential solutions that might work for you as well. Hopefully one does the trick!

I think you just need to cast the value from a string to a date:

dimension_group: date_str {
type: time
timeframes: [ raw, time, date, day_of_month, week, month, month_name, quarter, year ]
sql: cast(${TABLE}."DATE_STR" as date);;

You may also consider adding a “try_to” function to a dimension_group (I’d suggest creating a totally different dimension as a test):

dimension_group: string_dim {
type: time
sql: try_to_date(${string_dim}) ;;
timeframes: [raw, date, day_of_month, month, month_name, quarter, quarter_of_year]
}

Best of luck!