clickhouse学习笔记-9-问题集

从mysql同步数据到ck，有时间字段为null，同步不成功

方案一：

修改ck的字段，允许为null

 CK
 `collect_local_date` Nullable(DateTime) COMMENT '采集的当地时间',

方案二：

数据同步的过程中，如果mysql的字段为null，则设置默认时间

mysql
ifNull(`collect_local_date`, toDateTime('0000-00-00 00:00:00')),

时间转换

SELECT 
  'UK' as country,
  toDateTime('2020-02-28 05:11:31', 'UTC') AS origin_time,
  toDateTime(CASE
        WHEN country='US' THEN toString(origin_time, 'PST8PDT')
        WHEN country='UK' THEN toString(origin_time, 'Europe/London')
        WHEN country='FR' THEN toString(origin_time, 'Europe/Paris')
        WHEN country='JP' THEN toString(origin_time, 'Asia/Tokyo')
        WHEN country='ES' THEN toString(origin_time, 'Europe/Madrid')
        WHEN country='IT' THEN toString(origin_time, 'Europe/Rome')
        WHEN country='DE' THEN toString(origin_time, 'Europe/Berlin')
        WHEN country='MX' THEN toString(origin_time, 'America/Mexico_City')
        WHEN country='CA' THEN toString(origin_time, 'PST8PDT')
        WHEN country='IN' THEN toString(origin_time, 'Indian/Cocos')
        WHEN country='CN' THEN toString(origin_time, 'Asia/Shanghai')
        WHEN country='AU' THEN toString(origin_time, 'Australia/Canberra')
        WHEN country='AE' THEN toString(origin_time, 'Asia/Dubai')
        ELSE toString(origin_time, 'UTC')
    END) as local_date_time;

ps:
case里做toDateTime转换，存在问题，换成toString