Time Grain for BigQuery type: DATE & DATETIME is not available

[ranugoldan]

When using time grain for BigQuery table, it always uses timestamp_trunc though the field is DATE or DATETIME type.

Expected results

Able to use time grain for Bigquery Type: DATE & DATETIME

Actual results

it will occur syntax error.

Screenshots

How to reproduce the bug

1. Explore from BigQuery Table that has a DATE/DATETIME field
2. Select the field as the time grain
3. Check include time
4. Run Query

Environment

• superset version: 0.35.0
• python version: 3.6

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Related slack thread: https://apache-superset.slack.com/archives/C7G8CG0LR/p1581403019215900

[villebro]

Thanks @ranugoldan , I have a fix for this that I'm looking to test later today, will put in a PR once it's finished.

[ranugoldan]

Thanks @villebro will look forward for it!

[villebro]

@ranugoldan as a quick fix/check, would you mind adding the following to your superset_config.py file and see if it solves your problem:

TIME_GRAIN_ADDON_FUNCTIONS = {
    'bigquery': {
        "PT1S": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), SECOND)",
        "PT1M": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), MINUTE)",
        "PT1H": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), HOUR)",
        "P1D": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), DAY)",
        "P1W": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), WEEK)",
        "P1M": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), MONTH)",
        "P0.25Y": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), QUARTER)",
        "P1Y": "TIMESTAMP_TRUNC(CAST({col} AS TIMESTAMP), YEAR)",
    }
}

[ranugoldan]

@villebro noted, will try it today. Thanks

[ranugoldan]

@villebro sorry for late reply. It worked fine. But the NaNaNaN return value from timestamp_trunc that I mentioned from slack threads before is still there. Should I create another issue for that?

[villebro]

@ranugoldan no need to create a separate issue; if you can add a screenshot of the NaNaNaN issue to the main ticket (the one with the screenshot above), that should be enough. I'll try to make a PR for this in the coming days, will try to tackle the formatting problem, too, unless it turns out to be a deep rabbit hole.