字符串转日期

--字符串转日期,这个重要
select toDateTime('2020-07-30 10:10:10') as x,toTypeName(x);
+-------------------+---------------------------------------------+
|x                  |toTypeName(toDateTime('2020-07-30 10:10:10'))|
+-------------------+---------------------------------------------+
|2020-07-30 02:10:10|DateTime                                     |
+-------------------+---------------------------------------------+
-- 字符串转时间戳对应的那个数字
select toUnixTimestamp('2020-07-30 10:10:10') as x,toTypeName(x) as y;
+----------+------+
|x         |y     |
+----------+------+
|1596075010|UInt32|
+----------+------+
--同样的,字符串转date
select toDate('2020-07-30 10:10:10') as x,toTypeName(x);
+----------+-----------------------------------------+
|x         |toTypeName(toDate('2020-07-30 10:10:10'))|
+----------+-----------------------------------------+
|2020-07-30|Date                                     |
+----------+-----------------------------------------+

提取单独的年月日等等

--提取年份
select toYear(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+----+------+
|x   |y     |
+----+------+
|2020|UInt16|
+----+------+
--提取月份
select toMonth(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+-+-----+
|x|y    |
+-+-----+
|7|UInt8|
+-+-----+
--提取小时
select toHour(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+--+-----+
|x |y    |
+--+-----+
|10|UInt8|
+--+-----+
--提取分钟
select toMinute(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+--+-----+
|x |y    |
+--+-----+
|10|UInt8|
+--+-----+
--提取秒数
select toSecond(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;

当年的第几天等等

--当月的第几天
select toDayOfMonth(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+--+-----+
|x |y    |
+--+-----+
|30|UInt8|
+--+-----+
--本周第几天
select toDayOfWeek(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+-+-----+
|x|y    |
+-+-----+
|4|UInt8|
+-+-----+
--本年的第几天
select toDayOfYear(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+---+------+
|x  |y     |
+---+------+
|212|UInt16|
+---+------+

指定维度的开始

--当年的第一天
select toStartOfYear(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+----------+----+
|x         |y   |
+----------+----+
|2020-01-01|Date|
+----------+----+
--当月的第一天
select toStartOfMonth(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+----------+----+
|x         |y   |
+----------+----+
|2020-07-01|Date|
+----------+----+
--当天的第一个小时
select toStartOfDay(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+-------------------+-------------------------+
|x                  |y                        |
+-------------------+-------------------------+
|2020-07-30 00:00:00|DateTime('Asia/Shanghai')|
+-------------------+-------------------------+
--当前的开始小时
select toStartOfHour(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+-------------------+-------------------------+
|x                  |y                        |
+-------------------+-------------------------+
|2020-07-30 10:00:00|DateTime('Asia/Shanghai')|
+-------------------+-------------------------+
select toStartOfMinute(toDateTime('2020-07-30 10:10:10')) as x,toTypeName(x) as y;
+-------------------+-------------------------+
|x                  |y                        |
+-------------------+-------------------------+
|2020-07-30 10:10:00|DateTime('Asia/Shanghai')|
+-------------------+-------------------------+

活在当下

select now() as x,toTypeName(x);
+-------------------+-----------------+
|x                  |toTypeName(now())|
+-------------------+-----------------+
|2021-01-09 01:23:25|DateTime         |
+-------------------+-----------------+
-- 今天,返回的是date类型
select today() as x,toTypeName(x);
+----------+-------------------+
|x         |toTypeName(today())|
+----------+-------------------+
|2021-01-09|Date               |
+----------+-------------------+

格式化

--格式化后,类型当然是string了
select formatDateTime(toDateTime('2020-07-31 18:30:20'),'%Y') as x, toTypeName(x) as y;
+----+------+
|x   |y     |
+----+------+
|2020|String|
+----+------+

select formatDateTime(toDateTime('2020-07-31 18:30:20'),'%Y%m%d') as x, toTypeName(x) as y;
+--------+------+
|x       |y     |
+--------+------+
|20200731|String|
+--------+------+

select formatDateTime(toDateTime('2020-07-31 18:30:20'),'%Y%m%d %H:%M:%S') as x, toTypeName(x) as y;
+-----------------+------+
|x                |y     |
+-----------------+------+
|20200731 18:30:20|String|
+-----------------+------+

总结

• 最基本的,要掌握toDateTime和formatDateTime这两个时间函数