Date fields throwing error

[mohiit_jain3]

My dimension group of date is apparently throwing errors when included in the dashboard. Can someone help me resolve this issue?

the field looks like (using bigquery db):

inserted_date{

type: time

timeframe: [

raw,

time,

date,

week,

month,

quarter,

year

]

sql: ${TABLE}.inserted_date;;

error this throws when included in the dashboard or filter:

when included in the filter--

The Google BigQuery Standard SQL database encountered an error while running this query.
Query execution failed: - No matching signature for operator >= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY >= ANY at [11:7]

when included as a field--

The Google BigQuery Standard SQL database encountered an error while running this query.
Query execution failed: - No matching signature for function FORMAT_TIMESTAMP for argument types: STRING, DATETIME, STRING. Supported signatures: FORMAT_TIMESTAMP(STRING, TIMESTAMP, [STRING]); FORMAT_TIMESTAMP(STRING, DATETIME) at [2:6]

Also, the raw and time field doesn’t show up in the dashboard even after everything is merged.

[mohiit_jain3]

@fabio1 @vfx1 @izzymiller ? anyone else maybe?

[vfx1]

Hi Mohit,

Your field is of type “DATETIME”. Looker doesn’t really like it. You have a few potential fixes - all force looker to convert it to TIMESTAMP:

• if you don’t care about the timezone, declare: “datatype: date” within the dimension group
• if you do care about the TZ, say you are in India, declare “sql: TIMESTAMP (${TABLE}.inserted_date, “+5:30”) ;;”
• and if you want to interpret the DATETIME in the timezone of the user, declare: “sql: TIMESTAMP (${TABLE}.inserted_date,  “{{ _query._query_timezone }}") ;;”

And you will be fine… Looker doesn’t still consider this a bug, don’t know why.

[mohiit_jain3]

huh, but this was not an issue when we were using Postgres as our database and the code was exactly the same and work. what changed now? just trying to understand the working behind it.

Also, isnt there anything like “datetime” type only which will give me whatever timestamp is in the db? @vfx1 

[mohiit_jain3]

Also, looker is not accepting  “datatype: date” here

[brzaziski]

Did you ever find an answer to this issue?  It is driving me crazy