How to add the timestamp field in Date format in the Date Range Dimension of Looker studio

[Prasenjit]

I am trying to fetch the data from Bigquery to Looker Studio for making a dashboard. Now the issue is that the data is not getting changed after selecting a specific date range in the Looker Studio Dashboard. Basically, I am not getting the option to add the timestamp in the "Date Range Dimension" field in Looker Studio. In Bigquery Schema the "timestamp" field is in Integer format and I think, to add a field in the "Date Range Dimension" the specific field should be in Date format. So how can I fix this issue? I need the dashboard where I can get the data as per the date selected in the dashboard.

[TomLoizzo]

Are you able to convert the integer to to a timestamp prior to calling it in the dashboard using: 
SELECT TIMESTAMP_MILLIS() or TIMESTAMP_SECONDS()?

This should surface the field as a timestamp

[Prasenjit]

integer to timestamp conversion in BigQuery?

[TomLoizzo]

Correct if the Timestamp is stored as in integer you can convert it to a timestamp. Timestamp functions 

[Prasenjit]

conversion by using the function that you mentioned? and should I create a new field to store or the same field where the timestamp was previously stored?

[TomLoizzo]

If you still need/want to store and display the timestamp as an integer you should create a new field otherwise converting it in the same field and making a comment on the change should be sufficient 

[Prasenjit]

okay, thanks. will try it and let you know

[Prasenjit]

hi... basically there is a column named "timestamp" in the bigquery table from where I am fetching the data in Looker Studio,  and the type of that column is "Integer". this column holds the value in milliseconds.

screenshot. 

[Alango89]

Lo resolviste?

[rag]

hi @TomLoizzo , I am running into same issue. How do we resolve. Bigquery table itself has that field as Interger. In looker I believe we just use the calculated fields to convert something, but I don't see the TIMESTAMP commands. 

PARSE_DATE("%Y%m", cast(updated_timestamp_ms as string))

[rag]

Hi @TomLoizzo , my understanding is we only use calculated fields to do such things. How do we use sql statements in looker. I am running into same issue as @Prasenjit . We have a timestamp field that is created as "INT" and I am unable to use it as date control. I tried below calculated filed, but didn't work. @Prasenjit were you able to resolve this? Thanks

PARSE_DATE("%Y%m", cast(device_updated_timestamp_ms as string))

[Prasenjit]

hi @rag i was unable to resolve this. Could you?