Data Studio
4.5K members online now
4.5K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi, 

 

I am new to Data Studio and want to use it to graph data from BigQuery. Inside BigQuery, I have a column in epoch (in seconds). I am able to connect BigQuery as a Source, but Data Studio doesn't seem to interpret my column as a date dimension correctly. 

 

The native data of this BigQuery column is a long (i.e. 1476115712) In Data Studio, I pick that column to be type Date (YYYYMMDD). When I try to use a scorecard to count distinct id with a fixed date range, I see no count. Where as I pick all date range, I can get the expected count.

 

My question is do I need to transform my epoch in seconds to something else before using Data Studio?

 

Thanks,

Steven 

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Steven M
October 2016

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Google Employee
# 4
Google Employee

Hi Steven,

 

Correct: Data Studio visualizations expect dates in YYYYMMDD format, but you can create dates from various other formats using functions in your data source. Can you try using the TODATE() function to convert the BigQuery epoch to YYYYMMDD? 

 

Here's the function description:

 

Summary
Returns the datetime field in the specified format in UTC.
X
A datetime field or expression.
Input Format
Input Format is expressed in one of these datetime formats:
'BASIC': %Y/%m/%d-%H:%M:%S
'DEFAULT_DASH': %Y-%m-%d [%H:%M:%S]
'DEFAULT_SLASH': %Y/%m/%d [%H:%M:%S]
'DEFAULT_DECIMAL': %Y%m%d [%H:%M:%S
'RFC_1123': for example, Sat, 24 May 2008 20:09:47 GMT
'RFC_3339': for example, 2008-05-24T20:09:47Z
'SECONDS': seconds since epoch
'MILLIS': milliseconds since Epoch
'MICROS': microseconds since Epoch
'NANOS': nanoseconds since Epoch
'JULIAN_DATE': days since Epoch
'DECIMAL_DATE': same as 'DEFAULT_DECIMAL'
Any valid strptime format. Optional if the value is already a datetime
Output Format
The output format defined in strptime format.
Examples
TODATE(response_time, 'NANOS', '%Y-%m-%d')
TODATE(create_time, 'DECIMAL_DATE', '%Y')
 
And here's the Help Center article on calculated fields: 
 
Hope this helps.
 
Rick

 

View solution in original post

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Same issue here Data studio use to recognize my event_dim.timestamp_micros as a date when importing from Big Query but now it just doesn't seem to make sense of the epoch. It is breaking all my line graphs.

 

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

I am using custom query as a workaround for now, it would be super awesome if Data Studio will be fixing the ability to recognize epoch in seconds or microseconds

 

https://support.google.com/360suite/datastudio/answer/6370296?hl=en#custom_query

 

Below is my custom query with a lot of things REDACTED, Requested is my timestamp in epoch in seconds.

 

SELECT STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(Requested * 1000000), '%Y%m%d') as RequestedDate,

      SEC_TO_TIMESTAMP(Requested) as RequestedTime,

      <probably another column you care about>

 

FROM [<your table resources>]

 

 

 

Marked as Best Answer.
Solution
Accepted by topic author Steven M
October 2016

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Google Employee
# 4
Google Employee

Hi Steven,

 

Correct: Data Studio visualizations expect dates in YYYYMMDD format, but you can create dates from various other formats using functions in your data source. Can you try using the TODATE() function to convert the BigQuery epoch to YYYYMMDD? 

 

Here's the function description:

 

Summary
Returns the datetime field in the specified format in UTC.
X
A datetime field or expression.
Input Format
Input Format is expressed in one of these datetime formats:
'BASIC': %Y/%m/%d-%H:%M:%S
'DEFAULT_DASH': %Y-%m-%d [%H:%M:%S]
'DEFAULT_SLASH': %Y/%m/%d [%H:%M:%S]
'DEFAULT_DECIMAL': %Y%m%d [%H:%M:%S
'RFC_1123': for example, Sat, 24 May 2008 20:09:47 GMT
'RFC_3339': for example, 2008-05-24T20:09:47Z
'SECONDS': seconds since epoch
'MILLIS': milliseconds since Epoch
'MICROS': microseconds since Epoch
'NANOS': nanoseconds since Epoch
'JULIAN_DATE': days since Epoch
'DECIMAL_DATE': same as 'DEFAULT_DECIMAL'
Any valid strptime format. Optional if the value is already a datetime
Output Format
The output format defined in strptime format.
Examples
TODATE(response_time, 'NANOS', '%Y-%m-%d')
TODATE(create_time, 'DECIMAL_DATE', '%Y')
 
And here's the Help Center article on calculated fields: 
 
Hope this helps.
 
Rick

 

Can Data Studio Date Dimension use epoch (in seconds) from BigQuery

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

TODATE Function already return invalid formula error. Even the examples do not work.