前言

Clickhouse是一个面向联机分析处理(OLAP)的开源的面向列式存储的DBMS，简称CK, 与Hadoop, Spark相比，ClickHouse很轻量级,由俄罗斯第一大搜索引擎Yandex于2016年6月发布, 开发语言为C++。主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

时间在数据库中经常作为时间索引，在数据入库和出库以及更新的时候都需要变化。在一些指标计算或者是提取某段时间的数据时，都会根据数据库中的时间索引数据进行操作。因此很大一部分我们操作数据都得先从时间数据下手，但是想要真正提取到我们想要的时间作为索引，还需要我们掌握许多功能函数方便我们操作，这是一个比较复杂的运用过程。因此特地写下这篇文章，记录一些十分好用常用的处理Clickhouse数据库SQL时间数据的函数，以及实例运用这些函数完成一些复杂查询任务。希望能够帮助到正在看此博文的各位，如果还有什么问题解决不了尽请在评论区提出，博主会一一作答。

一、时间函数

和MySQL时间函数有些不同，但是时间函数的功能是一样的，这里把常用的时间函数给出，效果以实际代码运行结果为准：

函数效果描述

now() 2022-07-13 14:28:33 取当前时间

toUnixTimestamp(now()) 1657695998 获取当前时间戳

toYear(now()) 2022 取日期中的年份

toMonth(now()) 7 取日期中的月份

today() 2022-07-13 今天的日期

toDate(now()) 2022-07-13 取日期中的日期

yesterday() 2022-07-12 昨天的日期

toQuarter(now()) 3 获取当前日期季度

toDayOfMonth(now()) 13 当前月份的天数

toDayOfYear(now()) 194 日期化为天数

toDayOfWeek(now()) 3 获取星期几

toMonday(now()) 2022-07-11 当前周的第一天

toHour(now()) 14 取日期时间中的小时

toMinute(now()) 42 取日期时间中的分钟

toSecond(now()) 40 取日期时间中的秒

toStartOfQuarter(now()) 2022-07-01 当前季度的第一天

toStartOfMinute(now()) 2022-07-13 14:57:00 当前起始分钟时间

toStartOfHour(now()) 2022-07-13 14:00:00 当前起始小时时间

toStartOfDay(now()) 2022-07-13 00:00:00 当天起始时间

toStartOfYear(now()) 2022-01-01 当前年份的第一天

toStartOfMonth(now()) 2022-07-01 当前月份的第一天

1.取当前时间

now()

1. SELECT
2. now() AS time

today()

1. SELECT
2. today() AS time

获取当前时间戳

1. SELECT
2. toUnixTimestamp(now()) AS time

2.取年月日季度

toYear()

1. SELECT
2. toYeara(now()) AS time

toMonth()

1. SELECT
2. toMonth(now()) AS time

toDayOfMonth()

1. SELECT
2.     toDayOfMonth(now()) AS time

获取季度toQuarter()

1. SELECT
2. toQuarter(now()) AS time

out：3

3.日期转化

日期化为天数

1. SELECT
2.     toDayOfYear(now()) AS time

当天日期所在当月周数

1. SELECT
2. toDayOfWeek(now()) AS time

4.获取起始时间

获取当前时间的起始时间toStartOfDay(now())：

1. SELECT
2.     toStartOfDay(now()) AS time

格式就是这样的格式，大家跟着前面那种表来对就没问题。

二、时间格式转换函数

1.formatDateTime(<时间数据>,'format格式')

%r 时间，12-小时（hh:mm:ss AM 或 PM）

%S 秒(00-59)

%s 秒(00-59)

%T 时间, 24-小时 (hh:mm:ss)

%U 从年初首周开始计算 (00-53) 星期日是一周的第一天

%u 从年初首周开始计算 (00-53) 星期一是一周的第一天

%V 周 (01-53) 星期日是一周的第一天，与 %X 使用

%v 周 (01-53) 星期一是一周的第一天，与 %x 使用

%W 星期名

%w 当前周的天数，（0=星期日, 6=星期六）

%X 年，其中的星期日是周的第一天，4 位，与 %V 使用

%x 年，其中的星期一是周的第一天，4 位，与 %v 使用

%Y 年，4 位

%y 年，2 位

用法代码：

1. SELECT
2.     formatDateTime(now(),'%Y-%m-%d')AS time

可以根据format格式自由组合。

2.toYYYYMM()类型

该类型有三个：

格式	结果
toYYYYMM()	202207
toYYYYMMDD()	20220713
toYYYYMMDDhhmmss()	20220713154017

化为时间戳形式toUnixTimestamp():

1. SELECT
2. toUnixTimestamp(now()) AS time

三、时间数据类型转换

toDateTime()

1. SELECT
2.     toDateTime('2022-07-13 14:28:33') AS time

将可识别为日期时间的字符串转化为具体时间。

toDate()

1. 
SELECT
2.     toDate('2022-07-13 14:28:33') AS time

将可识别为日期的字符串转化为具体日期

四、时间运算函数

1.interval

interval关键字是和MySQL是一样的，大家不清楚的可以去看：

一文速学-玩转MySQL中INTERVAL关键字和INTERVAL()函数用法讲解

很简单就可以进行时间加减：

SELECT
    toDate('2022-07-13 14:28:33')-interval 3 day AS time

2.add增加时间

和MySQL的date_add是一样的，给出列表均已测试：

代码描述

addYears(<时间>,<数值>) 增加<数值>年份

addMonths(<时间>,<数值>) 增加<数值>月份

addWeeks(<时间>,<数值>) 增加<数值>周数

addDays(<时间>,<数值>) 增加<数值>天数

addHours(<时间>,<数值>) 增加<数值>小时

addMinutes(<时间>,<数值>) 增加<数值>分钟

addSeconds(<时间>,<数值>) 增加<数值>秒数

addQuarters(<时间>,<数值>) 增加<数值>季度

代码使用展示：

1. SELECT
2.     addYears(toDate('2022-07-13 14:28:33'),1) AS time

3.subtract减去时间

和add是一样的，故不作表格展示，展示代码用法：

1. SELECT
2.     subtractYears(toDate('2022-07-13 14:28:33'),1) AS time

4.时间差值 dateDiff()

也是很简单的一个函数看过一次会有就好了：

SELECT
   dateDiff('year',toDate('2022-07-13 14:28:33'),toDate('2023-07-13 14:28:33')) AS time

后面一个时间减去前面一个时间，前面的参数为时间类似：

有： 'year'，'month'，'week'，'day'，'hour'，'minute'，'second'。

【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据

如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算

日、月、年、时、分、秒之差及时间间隔计算。【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章介绍的关于时间的计算比较简单，主要是为了后面时间计算文章做铺垫！

【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？

如何一个SQL打印出当月日历或当年日历？？？如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期？？？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本例要求返回当月内第一个星期一与最后一个星期一,我们分别找上月末及当月末之前七天的下一周周一即可。

【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算

日期操作函数(INTERVAL、EXTRACT)的使用以及如何确定一年是否为闰年及周的计算两个小案例【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章主要介绍的是关于时间类型的一些常规操作。

【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据

本篇文章讲解的主要内容是：***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***

【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数

如何确定两个日期之间的工作日有多少天、计算—年中每周内各日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本章节的三个需求：确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数有些许难度，不过建议还是学会比较好。

【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的

本篇文章讲解的主要内容是：***目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。***

【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。

【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组

本篇文章讲解的主要内容是：***如何使用lag函数让结果集重复数据只显示一次、用行转列pivot写法优化部门之间计算工资差异类似需求、如何通过ceil函数对已有数据进行分组打印、放假安排团队分组值班，如何通过ntile()over(order by )快速进行人员分组***

【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表

本篇文章讲解的主要内容是：***通过执行计划看开窗函数开窗语法rows\range between preceding and current row以及rows\range between unbounded preceding and unbounded following对移动范围的值进行聚集的原理以及区别】、如何通过一个SQL打印九九乘法口表！！！***