Clickhouse 时区

最新推荐文章于 2024-03-01 20:40:01 发布

vkingnew

最新推荐文章于 2024-03-01 20:40:01 发布

阅读量1.2w

点赞数 3

分类专栏： Clickhouse

原文链接：https://blog.csdn.net/qq_21383435/article/details/113753534

版权

Clickhouse 专栏收录该内容

102 篇文章 70 订阅

订阅专栏

默认设置

Clickhouse默认是读取操作系统的时区我们可以通过操作系统命令和clickhouse的命令查看验证：

Clickhouse> select now();

SELECT now()

┌───────────────now()─┐
│ 2020-07-11 23:47:56 │
└─────────────────────┘

1 rows in set. Elapsed: 0.003 sec. 

Clickhouse> exit;
Bye.
[root@hadoop ~]# date
Sat Jul 11 23:48:01 CST 2020



此时操作系统的时区和时间是：
# timedatectl
      Local time: Sat 2020-07-11 23:49:06 CST
  Universal time: Sat 2020-07-11 15:49:06 UTC
        RTC time: Sat 2020-07-11 15:49:05
       Time zone: Asia/Shanghai (CST, +0800)
     NTP enabled: n/a
NTP synchronized: no
 RTC in local TZ: no
      DST active: n/a

操作系统的命令：
# timedatectl list-timezones
ist-timezones 列出系统上支持的时区
set-timezone 设定时区
set-time 设置时间
set-btp 设置同步ntp

示例:设置时区示例：
timedatec修改时区
timedatectl set-timezone "America/New_York"

# timedatectl set-timezone Asia/Shanghai
ntp设置：
yum -y install ntp 
systemctl enable ntpd 
systemctl start ntpd
 同步时间
ntpdate -u cn.pool.ntp.org

clickhouse提供了配置的参数选型：

1.修改设置
sudo vim /etc/clickhouse-server/config.xml

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


我们可以看到选型的说明如下：
 <!-- Server time zone could be set here.

         Time zone is used when converting between String and DateTime types,
          when printing DateTime in text formats and parsing DateTime from text,
          it is used in date and time related functions, if specific time zone was not passed as an argument.

         Time zone is specified as identifier from IANA time zone database, like UTC or Africa/Abidjan.
         If not specified, system time zone at server startup is used.

         Please note, that server could display time zone alias instead of specified name.
         Example: W-SU is an alias for Europe/Moscow and Zulu is an alias for UTC.
    -->
    <!-- <timezone>Europe/Moscow</timezone> -->

时区在日期时间相关的函数，若指定时区作为参数。在Datetime和String类型之间进行转换。
时区的指定是按照IANA标准的时区库指定的，可以在Linux系统中通过命令查询
若不指定则使用系统启动的时区。

clickhouse相关的时区函数：

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还可以获知字段类型，以及该字段对应的时区。