click house时区

click house ttl不生效

1.修改设置
sudo vim /etc/clickhouse-server/config.xml
 
<timezone>Asia/Shanghai</timezone>
 由于clickhouse是俄罗斯人主导开发的，默认设置为Europe/Moscow
2.重启服务器：
sudo service clickhouse-server restart

Clickhouse> select formatDateTime(now(),'%F %T') as dt,toString(toDateTime(dt),'Asia/Shanghai') as BJ_time,toString(toDateTime(dt),'America/New_York') as NY_time;
 
SELECT 
    formatDateTime(now(), '%F %T') AS dt,
    toString(toDateTime(dt), 'Asia/Shanghai') AS BJ_time,
    toString(toDateTime(dt), 'America/New_York') AS NY_time
 
┌─dt──────────────────┬─BJ_time─────────────┬─NY_time─────────────┐
│ 2020-07-12 00:13:29 │ 2020-07-12 00:13:29 │ 2020-07-11 12:13:29 │
└─────────────────────┴─────────────────────┴─────────────────────┘
 
1 rows in set. Elapsed: 0.006 sec. 
 
 
可以看到Clickhouse默认采用的系统的UTC
 
 
Clickhouse> select formatDateTime(now(),'%F %T') as dt,toString(toDateTime(dt,'UTC'),'Asia/Shanghai') as BJ_time,toTimeZone(toDateTime(dt,'UTC'),'Asia/Shanghai') as random_time,toString(toDateTime(dt),'Asia/Shanghai') SH_TIME,toTimeZone(toDateTime(dt),'Asia/Shanghai') SH_time,toTimeZone(toDateTime(dt,'America/New_York'), 'Asia/Hong_Kong') HK_time;
 
SELECT 
    formatDateTime(now(), '%F %T') AS dt,
    toString(toDateTime(dt, 'UTC'), 'Asia/Shanghai') AS BJ_time,
    toTimeZone(toDateTime(dt, 'UTC'), 'Asia/Shanghai') AS random_time,
    toString(toDateTime(dt), 'Asia/Shanghai') AS SH_TIME,
    toTimeZone(toDateTime(dt), 'Asia/Shanghai') AS SH_time,
    toTimeZone(toDateTime(dt, 'America/New_York'), 'Asia/Hong_Kong') AS HK_time
 
┌─dt──────────────────┬─BJ_time─────────────┬─────────random_time─┬─SH_TIME─────────────┬─────────────SH_time─┬─────────────HK_time─┐
│ 2020-07-12 00:27:25 │ 2020-07-12 08:27:25 │ 2020-07-12 08:27:25 │ 2020-07-12 00:27:25 │ 2020-07-12 00:27:25 │ 2020-07-12 12:27:25 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘
 
1 rows in set. Elapsed: 0.007 sec. 
 
可以看到toTimeZone,与toString 的功能很像
 
 
Clickhouse> select formatDateTime(now(),'%F %T') as dt,toTypeName(toString(toDateTime(dt),'Asia/Shanghai')) SH_TIME,toTypeName(toTimeZone(toDateTime(dt),'Asia/Shanghai')) SH_time,toTypeName(toTimeZone(toDateTime(dt,'America/New_York'), 'Asia/Hong_Kong')) HK_time;
 
SELECT 
    formatDateTime(now(), '%F %T') AS dt,
    toTypeName(toString(toDateTime(dt), 'Asia/Shanghai')) AS SH_TIME,
    toTypeName(toTimeZone(toDateTime(dt), 'Asia/Shanghai')) AS SH_time,
    toTypeName(toTimeZone(toDateTime(dt, 'America/New_York'), 'Asia/Hong_Kong')) AS HK_time
 
┌─dt──────────────────┬─SH_TIME─┬─SH_time───────────────────┬─HK_time────────────────────┐
│ 2020-07-12 00:29:43 │ String  │ DateTime('Asia/Shanghai') │ DateTime('Asia/Hong_Kong') │
└─────────────────────┴─────────┴───────────────────────────┴────────────────────────────┘
 
1 rows in set. Elapsed: 0.002 sec. 
toTimeZone函数可以实现时区转换，通过toTypeName还可以获知字段类型，以及该字段对应的时区。