Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

BigQuery Bug: Time expression is too long

  • 10 October 2016
  • 1 reply
  • 45 views
P

I’m using BigQuery and tried to use a “Last Quarter” date filter in the table_date_range LookML function and received the following error:


Failed to retrieve data - Time expression is too long: TIMESTAMP ( DATE_ADD ( DATE_ADD ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( DATE_ADD ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) , - 3 , 'MONTH' ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( DATE_ADD ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( STRING ( YEAR ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) ) , '-' , LPAD ( STRING ( ( ( QUARTER ( TIMESTAMP ( CONCAT ( CURRENT_DATE ( ) , ' 00:00:00' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) , - 3 , 'MONTH' ) ) - 1 ) * 3 ) + 1 ) , 2 , '0' ) , '-01' ) ) , 3 , 'MONTH' ) , - 1 , 'SECOND' ) )

Here’s my code snippet for the SQL and the filter:


derived_table:

    sql: SELECT * FROM {% table_date_range date<filter <dataset_name>.<table_name>_ %}

    

  fields:

  - filter: date_filter

    label: FILTER Report Quarter

    type: date

    suggestions: ['2016-01-01 for 1 quarter','2016-04-01 for 1 quarter','2016-07-01 for 1 quarter']

Here’s the code generated when using “Last Quarter”:


(SELECT * FROM (TABLE_DATE_RANGE([DATASET_NAME.TABLE_NAME],TIMESTAMP(DATE_ADD(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01'))) - 1) * 3) + 1), 2, '0'), '-01')), -3, 'MONTH')),TIMESTAMP(DATE_ADD(DATE_ADD(TIMESTAMP(CONCAT(STRING(YEAR(DATE_ADD(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01'))) - 1) * 3) + 1), 2, '0'), '-01')), -3, 'MONTH'))), '-', LPAD(STRING(((QUARTER(DATE_ADD(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(STRING(YEAR(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00')))), '-', LPAD(STRING(((QUARTER(TIMESTAMP(CONCAT(CURRENT_DATE(), ' 00:00:00'))) - 1) * 3) + 1), 2, '0'), '-01'))) - 1) * 3) + 1), 2, '0'), '-01')), -3, 'MONTH')) - 1) * 3) + 1), 2, '0'), '-01')), 3, 'MONTH'),-1, 'SECOND')))))`

1 reply

dion
Rank
Userlevel 1

Hi Peter, we will need more information to diagnose this issue. Would you, please, contacts us through help.looker.com or through Chat? Thanks!

Reply


Terms of Service