Data Studio无法识别MySQL日期和日期时间格式 [英] MySQL date and datetime formats unrecognised by Data Studio
问题描述
我已使用标准连接器将Google Data Studio与MySQL数据库链接在一起.除了 date 和 datetime 字段外，几乎所有内容都可以正常运行.
I've linked Google data studio with a MySQL database using the standard connector. Almost everything works fine except for date and datetime fields.
我在phpmyadmin中有这两个字段(字段名称， 字段类型 ，output):
I have these 2 fields in phpmyadmin (field name, field type, output):
- Validated_date datetime
2017-09-27 12:31:04 - 到期日期 日期
2017-12-24
- Validated_date datetime
2017-09-27 12:31:04 - Expiration_date date
2017-12-24
在Data Studio中，我已经设置了这些类型，但是没有一个能被识别:
In Data Studio I've set these types, but none of them are recognised:
- Validated_date 日期小时(YYYYMMDDHH)
- 有效日期 日期(YYYYMMDD)
我尝试在SELECT中使用date_format格式化字段:
I tried to format the field with date_format in my SELECT:
DATE_FORMAT(p.Expiration_date, '%Y%m%d') AS "Expiration Date"
我什至尝试了其他date_formats，但在Data Studio中它们从未被视为日期:
I even tried other date_formats but they're never recognised as dates in Data Studio:
DATE_FORMAT(p.Expiration_date, '%Y/%m/%d') AS "Expiration Date"
DATE_FORMAT(p.Expiration_date, '%Y-%m-%d') AS "Expiration Date"
有什么主意吗?
推荐答案
我遇到了同样的问题.解决此问题的方法是在Google Data Studio中修改日期格式，方法是创建一个新维度，然后将mySQL Datetime重新格式化为所需格式.
I had the same issue. My approach to solve this is to modify the date format within Google Data Studio by creating a new dimension, reformatting the mySQL Datetime into the desired format.
在您的示例中，您使用DATE_FORMAT.我想知道您是在Data Studio(不知道DATE_FORMAT)还是在mySQL中应用它?如果您在Data Studio中进行此操作，并且保持mySQL/phpmyadmin不变，则可以使用以下方法:
In your example you use DATE_FORMAT. I wonder if you apply this in Data Studio (which does not know DATE_FORMAT) or in mySQL?. If you do it in data studio and leave your mySQL/phpmyadmin untouched you can use this:
TODATE(your-date-field, 'DEFAULT_DASH', '%Y%m%d')
这将采用HH:ii:ss中的日期格式YYYY-MM-DD和可选时间，并将其重新格式化为与Data Studio兼容的YYYYMMDD.
This will take the date format YYYY-MM-DD with optional time in HH:ii:ss and reformat it into YYYYMMDD which works with data studio.
这篇关于Data Studio无法识别MySQL日期和日期时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
