Dates and time formats and grouping into drilldown

Hey,
I’m new to Bipp and it looks like an awesome tool.
I’m creating my first project, and would like to have a couple of Columns in it. I have some experience with Looker.
I’m trying to display a date in a couple of formats - Full date, Month, Year, Timestamp.
I’ve tried using the format syntax without success.
Also would be cool if I could group these different columns under a drilldown. The documentation for that isn’t straightforward and there’s no example.

My current project looks like this:

table users_view
  data_source Cartloop BigQuery
  sql `cartloop-bigquery-321817.http_api`.`users_view`
  explore true
  column created_at_timestamp
    label Created At (Year)
    type datetime
    format yyyy```

But even this doesn't output the right format.
Any help would be appriciated.

Hi @benklinger

To extract full date, Month, year you can use EXTRACT functions from BigQuery
For example I have a date column in BigQuery database with “2020-05-15” value. Month and Year can be extracted from date column as shown in below example

column req_delivery_date
    sql req_delivery_date
    type datetime // DATE
  
  column month_column
    sql EXTRACT(MONTH FROM  req_delivery_date)
    type datetime 
    drilldown month_drildown    // Hierarchical relationship between month and date column
      table history_data
      column req_delivery_date
  
  column year_column
    sql EXTRACT(YEAR FROM  req_delivery_date)
    type datetime 
    drilldown yr_drildown  // Hierarchical relationship between year and month column
      table history_data
      column month_column

If you notice in above example, I have specified hierarchical relationship between Year, Month and Req_delivery_date columns. Once you save above datamodel and deploy it. You can switch to Gridchart visualization in dataviewer area. You will see + button on column header of Year column once selected. Clicking on + button will show Month column and so on.
Clicking on - button will collapse these columns

Currently Drilldown feature is available in Gridchart visualization. In next release it will be available in Table and rest of the visualizations too.

Drilldown

Thanks, I get an error when trying to implement.

error : Query datasource: googleapi: Error 400: No matching signature for function EXTRACT for argument types: DATE_TIME_PART FROM STRING. Supported signatures: EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); EXTRACT(DATE_TIME_PART FROM DATETIME); EXTRACT(DATE_TIME_PART FROM TIME); EXTRACT(DATE_TIME_PART FROM INTERVAL) at [1:8], invalidQuery

Hi @benklinger
It seems your underlying column is of type string. In my example req_delivery_date is of type date. You will need to covert it into datetime type

Hi Shubham,

I made sure that it is indeed a TIMEDATE type, but I still get the error:

Hi @benklinger format keyword which you were using earlier was supported only for MySQL, MSSQL dialect. After your feedback we have added support for BigQuery as well.
Now you don’t need to rely on BigQuery functions for extracting a specific date time formatted values.

In following example, I have one date column in BigQuery with type date and value 2011-05-21. I need to cast this column as TIMESTAMP to work with format keyword. Same could be done for any date /datetime column present in BigQuery. After this you can create new columns with formatting as mentioned here and new columns will have value in specified format. Please see attached screenshot for your reference.

1 Like