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

MySQL datetime field not recognized

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I have a datetime field in MySQL that I set as date range dimension in Report settings. Using this field on any table as a Date range dimension generates an error. I have logged the MySql queries and the field is not passed to the query:

 

SELECT MTH, SUM(PAYMNT_AMOUNT) AS _PAYMNT_AMOUNT_ FROM analytics AS t0 WHERE ( >= '20170101' AND <= '20171231') GROUP BY MTH ORDER BY MTH ASC

 

The field name in the database is TRAN_TS (if that might be a problem).

 

Thanks.

MySQL datetime field not recognized

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Data Studio doesn't like to have DATE or TRAN in column names. Renaming the column has resolved my problems.

MySQL datetime field not recognized

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Update: not really Smiley Sad

I have two fields. Transaction date (TS): datetime, Item date (ITEM_START_TIME): datetime

 

Setting transaction date (TS is the transaction date)  as Date range dimension generates this query:

SELECT TS, SUM(PAYMNT_AMOUNT) AS _PAYMNT_AMOUNT_ FROM analytics AS t0 GROUP BY TS ORDER BY _PAYMNT_AMOUNT_ ASC

 

Setting item date as Date range dimension generates this query:

SELECT SUM(PAYMNT_AMOUNT) AS _PAYMNT_AMOUNT_ FROM analytics AS t0 WHERE (ITEM_START_TIME >= '20170424' AND ITEM_START_TIME <= '20170523') ORDER BY _PAYMNT_AMOUNT_ ASC

 

The second one is correct. What am I doing wrong?

 

Thanks, A

MySQL datetime field not recognized

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hello,

 

It's like if your field was not defined as a date field. I'm also working with a MySQL data source, with a "day" field, which type is date. In GDS the field is defined as a Date (AAAAMMJJ). 

 

The generated queries by look like this : 

WHERE (DATE_FORMAT(t0.day, '%Y%m%d') >= '20170501' AND DATE_FORMAT(t0.day, '%Y%m%d') <= '20170531'

MySQL datetime field not recognized

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

I have a similar issue with one report of mine. I think it has to do with the fact that data range does not recognize YYYYMM dates as a data range dimension. I have posted a couple of times about this and I have not receive a solution. I used to trick the data range by making my query with a daily datetime field in YYYYMMDD format and once it was working, then editing my data source and change the query to the monthly datetime field I was interested initially in YYYYMM format and then reconnecting, but it doesn´t seems to work anymore. I strongly don´t recommend to use DS to aggregate, I have also tried that and it is messy. If you read something new let me know.

MySQL datetime field not recognized

[ Edited ]
Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

I found this on https://support.google.com/datastudio/answer/6401549 in DS help 

The dimension you use as the Date Range Dimension must formatted as Date YYYYMMDD.

Now I know there is no way around this I did this in my query

SELECT

*

FROM

(SELECT

   -- month and format yyyymm AS month,

   -- day and format yyyymmdd AS day,

   -- whatever else

FROM [someproject.sometable]

GROUP BY 1,2) AS result1

JOIN(

   -- month and format yyyymm AS month,

   -- fields you are interested in monthly values for example COUNT(DISTINCT user_id) to get totals or something like that

) AS result2 ON result1.month = result2.month

so I will have same monthly field value in every row for each month, and then I set DS to use Max or Min Aggregate. Then I use yyyymmdd for the data range and yyyymm for my charts