Clickhouse 时间日期函数实战总结

最新推荐文章于 2024-02-05 13:21:51 发布

扫地增

最新推荐文章于 2024-02-05 13:21:51 发布

阅读量2.8w

点赞数 13

分类专栏： sql # clickhouse OLAP引擎文章标签： sql 大数据 clickhouse OLAP ROLAP

本文链接：https://blog.csdn.net/qq_41018861/article/details/110950360

版权

sql 同时被 3 个专栏收录

29 篇文章 12 订阅

订阅专栏

OLAP引擎

26 篇文章 14 订阅

订阅专栏

clickhouse

15 篇文章 7 订阅

订阅专栏

注：所有的时间日期函数都可以在第二个可选参数中接受时区参数。示例：Asia / Yekaterinburg。在这种情况下，它们使用指定的时区而不是本地（默认）时区。仅支持与UTC相差一整小时的时区。

localhost :) select toDateTime(146600280) AS time,toDateTime(146600280,'Asia/Yekaterinburg') AS time_asia,toDateTime(146600280,'US/Samoa') AS time_us,toDate(146600280) AS date_local,toDate(146600280,'Asia/Yekaterinburg') AS date_asia,toDate(146600280,'US/Samoa') AS date_us;

SELECT
    toDateTime(146600280) AS time,
    toDateTime(146600280, 'Asia/Yekaterinburg') AS time_asia,
    toDateTime(146600280, 'US/Samoa') AS time_us,
    toDate(146600280) AS date_local,
    toDate(146600280, 'Asia/Yekaterinburg') AS date_asia,
    toDate(146600280, 'US/Samoa') AS date_us

Query id: 72b99150-a308-4495-afdd-2f123e0e877c

┌────────────────time─┬───────────time_asia─┬─────────────time_us─┬─date_local─┬──date_asia─┬────date_us─┐
│ 1974-08-25 02:18:00 │ 1974-08-24 23:18:00 │ 1974-08-24 07:18:00 │ 1974-08-25 │ 1974-08-24 │ 1974-08-24 │
└─────────────────────┴─────────────────────┴─────────────────────┴────────────┴────────────┴────────────┘

1 rows in set. Elapsed: 0.004 sec.

toDateTime() yyyy-MM-dd HH:mm:ss时间函数

toDateTime()时间函数返回的时间格式为yyyy-MM-dd HH:mm:ss

toDateTime(x)

toDateTime(x)参数x可以是字符串类型也可以是数字类型。

localhost :) select toDateTime('1466002800') AS time;

SELECT toDateTime('1466002800') AS time

Query id: 8b7e7816-d382-4140-be1b-a83b2741b0d2

┌────────────────time─┐
│ 2016-06-15 23:00:00 │
└─────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

localhost :) select toDateTime(1466002800) AS time;

SELECT toDateTime(1466002800) AS time

Query id: 56718b03-3796-4c82-8705-9c8d05404be3

┌────────────────time─┐
│ 2016-06-15 23:00:00 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

同样，toDateTime(x)x的格式可以是时间戳也可以是时间格式的字符串。

localhost :) select toDateTime('2016-06-15 23:00:00') AS time, toUnixTimestamp(time) as unixTimestamp;

SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toUnixTimestamp(time) AS unixTimestamp

Query id: c3870bf8-2b1c-4754-bcf8-e86a5c4a410c

┌────────────────time─┬─unixTimestamp─┐
│ 2016-06-15 23:00:00 │    1466002800 │
└─────────────────────┴───────────────┘

1 rows in set. Elapsed: 0.003 sec.

toUnixTimestamp()

toUnixTimestamp()时间戳函数中的参数类型可以是String类型可以是DateTime类型

localhost :) select toDateTime('2016-06-15 23:00:00') AS time, toUnixTimestamp('2016-06-15 23:00:00') as unixTimestamp,toUnixTimestamp(time) as unixTimestamp1;

SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toUnixTimestamp('2016-06-15 23:00:00') AS unixTimestamp,
    toUnixTimestamp(time) AS unixTimestamp1

Query id: c947aed8-e63c-45d7-bd5d-e2c9fbd5c4cb

┌────────────────time─┬─unixTimestamp─┬─unixTimestamp1─┐
│ 2016-06-15 23:00:00 │    1466002800 │     1466002800 │
└─────────────────────┴───────────────┴────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toDate() yyyy-MM-dd日期函数

toDate()日期函数返回的日期格式yyyy-MM-dd。

localhost :) select toDate(146600280) AS date_local,toDate(146600280,'Asia/Yekaterinburg') AS date_asia,toDate(146600280,'US/Samoa') AS date_us;

SELECT
    toDate(146600280) AS date_local,
    toDate(146600280, 'Asia/Yekaterinburg') AS date_asia,
    toDate(146600280, 'US/Samoa') AS date_us

Query id: 1704f699-fb96-484b-9b87-d5cdf4215fdc

┌─date_local─┬──date_asia─┬────date_us─┐
│ 1974-08-25 │ 1974-08-24 │ 1974-08-24 │
└────────────┴────────────┴────────────┘

1 rows in set. Elapsed: 0.003 sec.

toDate()日期函数参数类型为String类型或时间戳类型或DateTime类型。

localhost :) select toDateTime('2016-06-15 23:00:00') AS time, toDate(time) AS date,toDate('2016-06-15 23:00:00') AS date1,toDate(1466002800) AS date2;

SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date,
    toDate('2016-06-15 23:00:00') AS date1,
    toDate(1466002800) AS date2

Query id: ac06c8d0-94bf-4ac0-8efb-b722ec70cb15

┌────────────────time─┬───────date─┬──────date1─┬──────date2─┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 │ 2016-06-15 │
└─────────────────────┴────────────┴────────────┴────────────┘

1 rows in set. Elapsed: 0.003 sec.

toTime() 返回时间格式yyyy-MM-dd HH:mm:ss

toTime()虽然返回的时间格式是yyyy-MM-dd HH:mm:ss，但是只有HH:mm:ss部分是准确的，但是yyyy-MM-dd虽然不准确但是是固定的日期。

localhost :) SELECT toDateTime('2019-07-30 10:10:10') AS time,toTime(time) AS date_time,now() AS now,toTime(now) AS date_time1;

SELECT
    toDateTime('2019-07-30 10:10:10') AS time,
    toTime(time) AS date_time,
    now() AS now,
    toTime(now) AS date_time1

Query id: 9f3472dc-acda-4922-ad2f-29fd61309337

┌────────────────time─┬───────────date_time─┬─────────────────now─┬──────────date_time1─┐
│ 2019-07-30 10:10:10 │ 1970-01-02 10:10:10 │ 2020-12-10 15:39:53 │ 1970-01-02 15:39:53 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

toTimeZone()将时间或日期和时间转换为指定的时区的时间

将时间或日期和时间转换为指定的时区。时区是Date / DateTime类型的属性。表字段或结果集的列的内部值（秒数）不会更改，列的类型会更改，并且其字符串表示形式也会相应更改。

localhost :) SELECT
:-]     toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
:-]     toTypeName(time_utc) AS type_utc,
:-]     toInt32(time_utc) AS int32utc,
:-]     toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
:-]     toTypeName(time_yekat) AS type_yekat,
:-]     toInt32(time_yekat) AS int32yekat,
:-]     toTimeZone(time_utc, 'US/Samoa') AS time_samoa,
:-]     toTypeName(time_samoa) AS type_samoa,
:-]     toInt32(time_samoa) AS int32samoa
:-] FORMAT Vertical;

SELECT
    toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
    toTypeName(time_utc) AS type_utc,
    toInt32(time_utc) AS int32utc,
    toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
    toTypeName(time_yekat) AS type_yekat,
    toInt32(time_yekat) AS int32yekat,
    toTimeZone(time_utc, 'US/Samoa') AS time_samoa,
    toTypeName(time_samoa) AS type_samoa,
    toInt32(time_samoa) AS int32samoa
FORMAT Vertical

Query id: 50109e98-f402-4b12-b155-8ff3097d601e

Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

1 rows in set. Elapsed: 0.012 sec.

toTimeZone(time_utc, 'Asia/Yekaterinburg')将DateTime('UTC')类型更改为DateTime('Asia/Yekaterinburg')。值（Unixtimestamp）1546300800保持不变，但是字符串表示形式（toString（）函数的结果）从更改time_utc: 2019-01-01 00:00:00为time_yekat: 2019-01-01 05:00:00。

取当前日期和时间

today()取当前日期，等价于CURRENT_DATE()，时间格式yyyy-MM-dd。
now()取当前时间，等价于CURRENT_TIMSTAMP()，时间格式yyyy-MM-dd HH:mm:ss。

localhost :) select toDateTime(now()) AS time, toDate(time) AS date,toDate(now()) AS date1,toDate(now()) AS date2,toTime(now()) AS time1, toDateTime(today()) AS time2,today() AS today,now() AS now;

SELECT
    toDateTime(now()) AS time,
    toDate(time) AS date,
    toDate(now()) AS date1,
    toDate(now()) AS date2,
    toTime(now()) AS time1,
    toDateTime(today()) AS time2,
    today() AS today,
    now() AS now

Query id: 861cecaa-f7b1-4895-936a-88939bf0ad0c

┌────────────────time─┬───────date─┬──────date1─┬──────date2─┬───────────────time1─┬───────────────time2─┬──────today─┬─────────────────now─┐
│ 2020-12-10 15:30:25 │ 2020-12-10 │ 2020-12-10 │ 2020-12-10 │ 1970-01-02 15:30:25 │ 2020-12-10 00:00:00 │ 2020-12-10 │ 2020-12-10 15:30:25 │
└─────────────────────┴────────────┴────────────┴────────────┴─────────────────────┴─────────────────────┴────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

取昨日日期

yesterday()返回昨天的日期，日期格式：yyyy-MM-dd。

localhost :) select yesterday() ;

SELECT yesterday()

Query id: 0c8e1d69-4414-4604-92e7-7f112f6fed6e

┌─yesterday()─┐
│  2020-12-09 │
└─────────────┘

1 rows in set. Elapsed: 0.002 sec.

获取当前时间中的年、季度、月、日、时、分、秒。

toYear(now())获取当前时间所属的年份，
toMonth(now())获取当前时间所属的月份，
toQuarter(now())获取当前时间的月份所属的季度，
toHour(now())获取当前时间的小时部分，
toMinute(now())获取当前时间的分钟部分，
toSecond(now())获取当前时间的秒钟部分。

localhost :) SELECT toDateTime(now()) AS time,toYear(time) AS get_year,toMonth(time) AS get_month,toQuarter(time) AS get_quarter,toHour(time) AS get_hour,toMinute(time) AS get_minute,toSecond(time) AS get_second;

SELECT
    toDateTime(now()) AS time,
    toYear(time) AS get_year,
    toMonth(time) AS get_month,
    toQuarter(time) AS get_quarter,
    toHour(time) AS get_hour,
    toMinute(time) AS get_minute,
    toSecond(time) AS get_second

Query id: 20112499-1352-40b8-8137-12f57377aff3

┌────────────────time─┬─year─┬─month─┬─quarter─┬─hour─┬─minute─┬─second─┐
│ 2020-12-10 15:51:15 │ 2020 │    12 │       4 │   15 │     51 │     15 │
└─────────────────────┴──────┴───────┴─────────┴──────┴────────┴────────┘

1 rows in set. Elapsed: 0.003 sec.

toMonday() 获取当周周一

toMonday()参数类型可以为Date类型或者DateTime类型

localhost :) select toMonday(today())
:-] ;

SELECT toMonday(today())

Query id: 880b17e1-0385-4153-a7be-fdaed8590854

┌─toMonday(today())─┐
│        2020-12-07 │
└───────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toMonday(now()) ;

SELECT toMonday(now())

Query id: cea12cda-412f-4544-a47b-98480f9d7cd2

┌─toMonday(now())─┐
│      2020-12-07 │
└─────────────────┘

1 rows in set. Elapsed: 0.008 sec.

toDayOfYear()取一年中的第几天

toDayOfYear()参数类型可以是Date类型或者DateTime类型。

localhost :) select toDayOfYear(now());

SELECT toDayOfYear(now())

Query id: d25747ba-3064-40aa-856b-37b375a46346

┌─toDayOfYear(now())─┐
│                345 │
└────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDayOfYear(today());

SELECT toDayOfYear(today())

Query id: 5ca6244e-6d72-4ee1-8cb0-8fcb4172d639

┌─toDayOfYear(today())─┐
│                  345 │
└──────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDayOfYear(toDate('2020-12-10'));

SELECT toDayOfYear(toDate('2020-12-10'))

Query id: 9ce38274-727d-4f5a-acc6-8640751bd372

┌─toDayOfYear(toDate('2020-12-10'))─┐
│                               345 │
└───────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.


localhost :) select toDayOfYear(toDateTime('2020-12-10 16:54:00'));

SELECT toDayOfYear(toDateTime('2020-12-10 16:54:00'))

Query id: ff4a5e09-2d4b-412a-afbb-8b87831ff6bf

┌─toDayOfYear(toDateTime('2020-12-10 16:54:00'))─┐
│                                            345 │
└────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toDayOfMonth()取一月中的第几天

toDayOfYear()参数类型可以是Date类型或者DateTime类型。

localhost :) select toDayOfMonth(now()) ;

SELECT toDayOfMonth(now())

Query id: c36a2f95-b6a1-498b-b5cd-efaeadcefdcc

┌─toDayOfMonth(now())─┐
│                  10 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toDayOfMonth(today()) ;

SELECT toDayOfMonth(today())

Query id: 53bad0fb-0ccc-4093-bfae-c91e53147757

┌─toDayOfMonth(today())─┐
│                    10 │
└───────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toDayOfMonth(toDate('2020-12-10')) ;

SELECT toDayOfMonth(toDate('2020-12-10'))

Query id: e9491fa8-991d-4456-bba6-97228b84315f

┌─toDayOfMonth(toDate('2020-12-10'))─┐
│                                 10 │
└────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDayOfMonth(toDate('2020-12-10 17:09:33')) ;

SELECT toDayOfMonth(toDate('2020-12-10 17:09:33'))

Query id: d699c562-43dc-442b-932d-79dc4236d4b2

┌─toDayOfMonth(toDate('2020-12-10 17:09:33'))─┐
│                                          10 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toDayOfWeek()取一周中第几天

toDayOfWeek()参数类型可以是Date类型或者DateTime类型。

localhost :) select toDayOfWeek(now());

SELECT toDayOfWeek(now())

Query id: e1250f95-66b7-415f-a6b5-e2cd57932c0f

┌─toDayOfWeek(now())─┐
│                  4 │
└────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toDayOfWeek(today());

SELECT toDayOfWeek(today())

Query id: ff6f8235-ff0d-40c4-a8e7-2307a440dfb6

┌─toDayOfWeek(today())─┐
│                    4 │
└──────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toDayOfWeek(toDate('2020-12-10'));

SELECT toDayOfWeek(toDate('2020-12-10'))

Query id: 3574b27b-6a7b-4cc7-b95f-f355df90f8a5

┌─toDayOfWeek(toDate('2020-12-10'))─┐
│                                 4 │
└───────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDayOfWeek(toDateTime('2020-12-10 16:56:00'));

SELECT toDayOfWeek(toDateTime('2020-12-10 16:56:00'))

Query id: 9298bd31-e662-42a5-b7c9-824ee2feee65

┌─toDayOfWeek(toDateTime('2020-12-10 16:56:00'))─┐
│                                              4 │
└────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDayOfWeek(toDateTime(toUnixTimestamp(now())));

SELECT toDayOfWeek(toDateTime(toUnixTimestamp(now())))

Query id: 31647de2-4d76-45dd-82c8-eadcc5b4ade4

┌─toDayOfWeek(toDateTime(toUnixTimestamp(now())))─┐
│                                               4 │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toStartOfYear()取一年中的第一天

toStartOfYear()参数类型可以是Date类型或者DateTime类型。

localhost :) select toStartOfYear(toDate('2020-12-10'));

SELECT toStartOfYear(toDate('2020-12-10'))

Query id: b401b5fd-d76f-4c0f-801a-05d99601aded

┌─toStartOfYear(toDate('2020-12-10'))─┐
│                          2020-01-01 │
└─────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfYear(toDateTime('2020-12-10 17:01:00'));

SELECT toStartOfYear(toDateTime('2020-12-10 17:01:00'))

Query id: 9225d0c8-8daf-4f4b-83b0-547ff42b6319

┌─toStartOfYear(toDateTime('2020-12-10 17:01:00'))─┐
│                                       2020-01-01 │
└──────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toStartOfYear(now());

SELECT toStartOfYear(now())

Query id: d3f8fdad-e85c-4a59-803f-ba4c1d290dcc

┌─toStartOfYear(now())─┐
│           2020-01-01 │
└──────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toStartOfYear(today());

SELECT toStartOfYear(today())

Query id: 4194635a-c926-4bce-86e1-e58c7df240a7

┌─toStartOfYear(today())─┐
│             2020-01-01 │
└────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

toStartOfMonth() 取某月中的第一天

toStartOfMonth()参数类型可以是Date类型或者DateTime类型。

localhost :) select toStartOfMonth(now());

SELECT toStartOfMonth(now())

Query id: 39f6a74a-4525-4c91-aaf1-f5d9741026b7

┌─toStartOfMonth(now())─┐
│            2020-12-01 │
└───────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfMonth(today());

SELECT toStartOfMonth(today())

Query id: 93746a67-28ec-46de-8a12-341ad91c3a44

┌─toStartOfMonth(today())─┐
│              2020-12-01 │
└─────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toStartOfMonth(toDate('2020-12-10'));

SELECT toStartOfMonth(toDate('2020-12-10'))

Query id: efa05914-e669-4e2e-842b-c65d86f9e13d

┌─toStartOfMonth(toDate('2020-12-10'))─┐
│                           2020-12-01 │
└──────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toStartOfMonth(toDateTime('2020-12-10 17:07:33'));

SELECT toStartOfMonth(toDateTime('2020-12-10 17:07:33'))

Query id: 895199c5-61bb-4257-be33-f4656e1d4a75

┌─toStartOfMonth(toDateTime('2020-12-10 17:07:33'))─┐
│                                        2020-12-01 │
└───────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toStartOfQuarter() 取某季度的第一天

toStartOfQuarter()参数类型可以是Date类型或者DateTime类型。

localhost :) select toStartOfQuarter(now()) ;

SELECT toStartOfQuarter(now())

Query id: 2f75e2d2-cc10-4591-af8d-bf5af9eab2d3

┌─toStartOfQuarter(now())─┐
│              2020-10-01 │
└─────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfQuarter(today()) ;

SELECT toStartOfQuarter(today())

Query id: 3dc8b254-98ea-4335-8127-d95713656254

┌─toStartOfQuarter(today())─┐
│                2020-10-01 │
└───────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

localhost :) select toStartOfQuarter(toDateTime('2020-12-10 17:09:33')) ;

SELECT toStartOfQuarter(toDateTime('2020-12-10 17:09:33'))

Query id: f3a3479a-e40d-4fd6-b8f9-2116b6b8da5c

┌─toStartOfQuarter(toDateTime('2020-12-10 17:09:33'))─┐
│                                          2020-10-01 │
└─────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfQuarter(toDate('2020-12-10')) ;

SELECT toStartOfQuarter(toDate('2020-12-10'))

Query id: 97f09e5d-32fa-4679-919c-b1513925deb8

┌─toStartOfQuarter(toDate('2020-12-10'))─┐
│                             2020-10-01 │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

toStartOfDay()当前日期中的开始时间

toStartOfDay()参数类型可以是Date类型或者DateTime类型。

localhost :) select  toStartOfDay(now());

SELECT toStartOfDay(now())

Query id: dce51410-e3da-4c1d-8d68-a601c5db51e4

┌─toStartOfDay(now())─┐
│ 2020-12-10 00:00:00 │
└─────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

localhost :) select  toStartOfDay(today());

SELECT toStartOfDay(today())

Query id: 77787394-530e-443e-aad9-271c18d291fc

┌─toStartOfDay(today())─┐
│   2020-12-10 00:00:00 │
└───────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select  toStartOfDay(toDate('2020-12-10'));

SELECT toStartOfDay(toDate('2020-12-10'))

Query id: 3ec981f6-20c0-47cd-8e4e-fd78ea073915

┌─toStartOfDay(toDate('2020-12-10'))─┐
│                2020-12-10 00:00:00 │
└────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select  toStartOfDay(toDate('2020-12-10 17:20:00'));

SELECT toStartOfDay(toDate('2020-12-10 17:20:00'))

Query id: 68c3b65f-e404-4122-acb1-5545a4ff3edc

┌─toStartOfDay(toDate('2020-12-10 17:20:00'))─┐
│                         2020-12-10 00:00:00 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toStartOfHour()当前日期中的开始小时

toStartOfHour()参数的类型是DateTime类型，返回的时间格式yyyy-MM-dd HH:mm:ss,其中yyyy-MM-dd为准确值，HH:mm:ss中HH为准确值，其他的都为整点值。

localhost :) select toStartOfHour(now());

SELECT toStartOfHour(now())

Query id: af4a5fab-a325-4e29-b225-e6ec20f16fc4

┌─toStartOfHour(now())─┐
│  2020-12-10 17:00:00 │
└──────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfHour(toDateTime(now()));

SELECT toStartOfHour(toDateTime(now()))

Query id: 74259daf-eb96-44a0-a31d-58101eda2989

┌─toStartOfHour(toDateTime(now()))─┐
│              2020-12-10 17:00:00 │
└──────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

toStartOfMinute()当前日期中的开始分钟

toStartOfMinute()参数的类型是DateTime类型，返回的时间格式yyyy-MM-dd HH:mm:ss,其中yyyy-MM-dd为准确值，HH:mm:ss中HH:mm为准确值，其他的都为整点值。

localhost :) select toStartOfMinute(now());

SELECT toStartOfMinute(now())

Query id: ccc34b47-e09f-48d3-aafa-e56fa5ec862a

┌─toStartOfMinute(now())─┐
│    2020-12-10 17:31:00 │
└────────────────────────┘

1 rows in set. Elapsed: 0.090 sec.

localhost :) select toStartOfMinute(toDateTime(now()));

SELECT toStartOfMinute(toDateTime(now()))

Query id: 5becf923-6e6b-497a-aa55-a6e88d82030f

┌─toStartOfMinute(toDateTime(now()))─┐
│                2020-12-10 17:32:00 │
└────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

其他类似函数也是如此：
toStartOfFiveMinute()当前时间的上一个五分钟开始时间
toStartOfTenMinutes()当前时间的上一个十分钟开始时间
toStartOfFifteenMinutes()当前时间的上一个十五分钟开始时间
toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])自定义函数，参数类型为Date类型或者DateTime类型

localhost :) select toStartOfInterval(today(),INTERVAL 1 month);

SELECT toStartOfInterval(today(), toIntervalMonth(1))

Query id: f8b38ca7-9a5e-4eed-b8aa-c447437ba6e2

┌─toStartOfInterval(today(), toIntervalMonth(1))─┐
│                                     2020-12-01 │
└────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toStartOfInterval(now(),INTERVAL 1 month);

SELECT toStartOfInterval(now(), toIntervalMonth(1))

Query id: f0533908-1e47-4821-9a67-d90a191a8eb4

┌─toStartOfInterval(now(), toIntervalMonth(1))─┐
│                                   2020-12-01 │
└──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toRelativeYearNum() 从0000-00-00 00:00:00开始计算相差多少个年头

toRelativeYearNum()参数类型可以是Date类型或者DateTime类型。返回当前年份，格式yyyy。

localhost :) select toRelativeYearNum(toDate('2020-12-10'));

SELECT toRelativeYearNum(toDate('2020-12-10'))

Query id: 7238e8b0-f6db-4317-9afc-70ebb141d448

┌─toRelativeYearNum(toDate('2020-12-10'))─┐
│                                    2020 │
└─────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toRelativeYearNum(toDateTime('2020-12-10 17:40:45'));

SELECT toRelativeYearNum(toDateTime('2020-12-10 17:40:45'))

Query id: 4e69f7bc-442e-4354-b22d-e6ec8a9e3bed

┌─toRelativeYearNum(toDateTime('2020-12-10 17:40:45'))─┐
│                                                 2020 │
└──────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toRelativeYearNum(now());

SELECT toRelativeYearNum(now())

Query id: 63f3026d-9938-411c-90b7-d882cd792262

┌─toRelativeYearNum(now())─┐
│                     2020 │
└──────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toRelativeYearNum(today());

SELECT toRelativeYearNum(today())

Query id: 86fe845d-bbaa-478a-9ee2-57c38bb46b46

┌─toRelativeYearNum(today())─┐
│                       2020 │
└────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toRelativeQuarterNum() 从0000-00-00 00:00:00 开始计算相差多少个季度

toRelativeQuarterNum()参数类型可以是Date类型或者DateTime类型。返回当前第几季度。

localhost :) select toRelativeQuarterNum(today());

SELECT toRelativeQuarterNum(today())

Query id: eadbf9c1-0ee8-42e1-9c94-1b092b88be39

┌─toRelativeQuarterNum(today())─┐
│                          8083 │
└───────────────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

localhost :) select toRelativeQuarterNum(now());

SELECT toRelativeQuarterNum(now())

Query id: 3ca3f4b1-18a5-4893-84f0-7f4e39b97db8

┌─toRelativeQuarterNum(now())─┐
│                        8083 │
└─────────────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

localhost :) select toRelativeQuarterNum(toDate('2020-09-01'));

SELECT toRelativeQuarterNum(toDate('2020-09-01'))

Query id: 6a5ab5c4-3364-4316-85f9-628be11cf661

┌─toRelativeQuarterNum(toDate('2020-09-01'))─┐
│                                       8082 │
└────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

localhost :) select toRelativeQuarterNum(toDateTime('2020-09-01 00:00:00'));

SELECT toRelativeQuarterNum(toDateTime('2020-09-01 00:00:00'))

Query id: 846d15c4-8156-41e5-84cd-9575a4b9e701

┌─toRelativeQuarterNum(toDateTime('2020-09-01 00:00:00'))─┐
│                                                    8082 │
└─────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

其他的都类似：
toRelativeWeekNum从0000-00-00 00:00:00 开始计算相差多少个周
toRelativeDayNum 从0000-00-00 00:00:00 开始计算相差多少天
toRelativeHourNum 从0000-00-00 00:00:00 开始计算相差多少小时
toRelativeMinuteNum 从0000-00-00 00:00:00 开始计算相差多少个分钟
toRelativeSecondNum 从0000-00-00 00:00:00 开始计算相差多少个秒钟

formatDateTime(now(),’%Y-%m-%d’) 时间格式化函数

formatDateTime(now(),'%Y-%m-%d')时间格式化函数，参数有两个，第一参数类型可为DateTime类型或者Date类型。第二个参数为指定输出的时间格式。时间格式参数参见表1-1.

localhost :) select formatDateTime(now(),'%Y-%m-%d %H:%M:%S') ;

SELECT formatDateTime(now(), '%Y-%m-%d %H:%M:%S')

Query id: 799167d1-c092-46ce-976e-d93c991fae25

┌─formatDateTime(now(), '%Y-%m-%d %H:%M:%S')─┐
│ 2020-12-10 17:51:00                        │
└────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select formatDateTime(today(),'%Y-%m-%d %H:%M:%S') ;

SELECT formatDateTime(today(), '%Y-%m-%d %H:%M:%S')

Query id: 5194a2a0-2c1f-407f-97ad-d72c12038afd

┌─formatDateTime(today(), '%Y-%m-%d %H:%M:%S')─┐
│ 2020-12-10 00:00:00                          │
└──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select formatDateTime(toDate('2020-12-10'),'%Y-%m-%d %H:%M:%S') ;

SELECT formatDateTime(toDate('2020-12-10'), '%Y-%m-%d %H:%M:%S')

Query id: 3df938d9-e368-4853-bb62-2677a7b07ee7

┌─formatDateTime(toDate('2020-12-10'), '%Y-%m-%d %H:%M:%S')─┐
│ 2020-12-10 00:00:00                                       │
└───────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select formatDateTime(toDateTime('2020-12-10 17:32:02'),'%Y-%m-%d %H:%M:%S') ;

SELECT formatDateTime(toDateTime('2020-12-10 17:32:02'), '%Y-%m-%d %H:%M:%S')

Query id: dae037aa-39b1-4136-9068-69647e3cf014

┌─formatDateTime(toDateTime('2020-12-10 17:32:02'), '%Y-%m-%d %H:%M:%S')─┐
│ 2020-12-10 17:32:02                                                    │
└────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

表 1-1：时间符号含义

修饰符	描述	示例
%C	年除以100并截断为整数(00-99)	20
%d	月中的一天，零填充（01-31)	02
%D	短MM/DD/YY日期，相当于%m/%d/%y	01/02/2018
%e	月中的一天，空格填充（1-31)	2
%F	短YYYY-MM-DD日期，相当于%Y-%m-%d	2018-01-02
%H	24小时格式（00-23)	22
%I	小时12h格式（01-12)	10
%j	一年(001-366)	002
%m	月份为十进制数（01-12)	01
%M	分钟(00-59)	33
%n	换行符(")
%p	AM或PM指定	PM
%R	24小时HH:MM时间，相当于%H:%M	22:33
%S	第二(00-59)	44
%t	水平制表符(’)
%T	ISO8601时间格式(HH:MM:SS)，相当于%H:%M:%S	22:33:44
%u	ISO8601平日as编号，星期一为1(1-7)	2
%V	ISO8601周编号(01-53)	01
%w	周日为十进制数，周日为0(0-6)	2
%y	年份，最后两位数字（00-99)	18
%Y	年	2018
%%	%符号	%

toYYYYMM()、toYYYYMMDD()、toYYYYMMDDhhmmss()

toYYYYMM()、toYYYYMMDD()、toYYYYMMDDhhmmss()参数的类型必须为Date类型和DateTime类型,分区别返回时间格式为对应函数名格式的字符串。

localhost :) select toYYYYMM(now());

SELECT toYYYYMM(now())

Query id: ab08587f-80ba-4320-aba4-2e60410c2fab

┌─toYYYYMM(now())─┐
│          202012 │
└─────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toYYYYMM(today());

SELECT toYYYYMM(today())

Query id: 193978a2-bc06-459f-b6ee-b5215820f38f

┌─toYYYYMM(today())─┐
│            202012 │
└───────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toYYYYMM(toDateTime('2020-12-10 11:11:11'));

SELECT toYYYYMM(toDateTime('2020-12-10 11:11:11'))

Query id: cad39080-1ae7-467f-9f86-c89723614602

┌─toYYYYMM(toDateTime('2020-12-10 11:11:11'))─┐
│                                      202012 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select toYYYYMM(toDate('2020-12-10'));

SELECT toYYYYMM(toDate('2020-12-10'))

Query id: 2a36e8e2-aaf0-4d38-b7aa-9b779f6390a1

┌─toYYYYMM(toDate('2020-12-10'))─┐
│                         202012 │
└────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toWeek(date,mode)返回当年的星期数

toWeek(date,mode)此函数返回日期或日期时间的星期数。toWeek（）的两个参数形式使您可以指定星期是从星期日还是星期一开始，以及返回值应在0到53还是从1到53的范围内。如果省略了mode参数，则默认mode为0,toISOWeek()是等效于的兼容性函数toWeek(date,3)。

Mode	First day of week	范围	第一周是***第一周
0	Sunday	0-53	第一周是今年第一个有星期天的星期
1	Monday	0-53	第一周是今年第一个有4天或更多天数的星期
2	Sunday	1-53	第一周是今年第一个有星期天的星期
3	Monday	1-53	第一周是今年第一个有4天或更多天数的星期
4	Sunday	0-53	第一周是今年第一个有4天或更多天数的星期
5	Monday	0-53	第一周是今年第一个有星期一的星期
6	Sunday	1-53	第一周是今年第一个有4天或更多天数的星期
7	Monday	1-53	第一周是今年第一个有星期一的星期
8	Sunday	1-53	第一周是包含1月1日的第一周
9	Monday	1-53	第一周是包含1月1日的第一周

localhost :) select toWeek(today(),1);

SELECT toWeek(today(), 1)

Query id: c7ce3824-a44c-48ba-a2e1-9c949707a1ae

┌─toWeek(today(), 1)─┐
│                 50 │
└────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toISOYear() 将日期转换为ISO年号

toISOYear()将带有时间的日期或日期转换为包含ISO年号的UInt16号。
参数类型为Date类型和DateTime类型

localhost :) select toISOYear(now()) ;

SELECT toISOYear(now())

Query id: c41ab220-8562-4af5-9877-921dd859b627

┌─toISOYear(now())─┐
│             2020 │
└──────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toISOYear(today()) ;

SELECT toISOYear(today())

Query id: 49a603df-430d-4b87-af5d-2b8294655aa9

┌─toISOYear(today())─┐
│               2020 │
└────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

toISOWeek() 将日期转化为ISO周号

toISOWeek()将带有时间的日期转换为包含ISO周号的UInt8数字,参数类型为Date类型和DateTime类型，获取周数最好使用这个函数。

localhost :) select toISOWeek(today());

SELECT toISOWeek(today())

Query id: dd0cf006-971f-48df-af2a-87f6d11c40ae

┌─toISOWeek(today())─┐
│                 50 │
└────────────────────┘

1 rows in set. Elapsed: 0.011 sec.

localhost :) select toISOWeek(now());

SELECT toISOWeek(now())

Query id: 4cab3cc2-f353-4ef2-bd85-3cae09534385

┌─toISOWeek(now())─┐
│               50 │
└──────────────────┘

1 rows in set. Elapsed: 0.002 sec.

对于表示“今年有4天或更多天”的模式值，周的编号按照ISO 8601:1988:
如果包含1月1日的那一周在新年有4天或更多的日子，它就是第1周。
否则，它是前一年的最后一周，下一周是第一周。对于含义为“contains January 1”的模式值，包含January 1的星期就是第1周。新年一周有多少天并不重要，即使只有一天也没关系。

toYearWeek（date [，mode]）

第二个参数可以忽略,返回日期的年和周。结果中的年份可能与该年份的第一周和最后一周的date参数中的年份不同。模式参数的工作方式与toWeek（）的模式参数完全相同。对于单参数语法，使用模式值0。toISOYear()是等效于的兼容性函数intDiv(toYearWeek(date,3),100)。参数类型必须为Date类型或者DateTime类型。

localhost :) select toDate('2020-12-28') AS date,toYearWeek(date,1) AS week1,toYearWeek(date,2) AS week2,toYearWeek(date,3) AS week3,toYearWeek(date,4) AS week4,toYearWeek(date,5) AS week5,toYearWeek(date,6) AS week6,toYearWeek(date,7) AS week7,toYearWeek(date,8) AS week8,toYearWeek(date,9) AS week9 FORMAT Vertical;;

SELECT
    toDate('2020-12-28') AS date,
    toYearWeek(date, 1) AS week1,
    toYearWeek(date, 2) AS week2,
    toYearWeek(date, 3) AS week3,
    toYearWeek(date, 4) AS week4,
    toYearWeek(date, 5) AS week5,
    toYearWeek(date, 6) AS week6,
    toYearWeek(date, 7) AS week7,
    toYearWeek(date, 8) AS week8,
    toYearWeek(date, 9) AS week9
FORMAT Vertical

Query id: 81e405bd-e399-452e-a225-96ab85a528cc

Row 1:
──────
date:  2020-12-28
week1: 202053
week2: 202052
week3: 202053
week4: 202053
week5: 202052
week6: 202053
week7: 202052
week8: 202101
week9: 202101

1 rows in set. Elapsed: 0.003 sec.

localhost :) select toDate('2020-12-28') AS date,toYearWeek(date) AS week,toYearWeek(date,0) AS week0;

SELECT
    toDate('2020-12-28') AS date,
    toYearWeek(date) AS week,
    toYearWeek(date, 0) AS week0

Query id: 0b813c84-d55a-4c9e-9493-9d93a463ca9f

┌───────date─┬───week─┬──week0─┐
│ 2020-12-28 │ 202052 │ 202052 │
└────────────┴────────┴────────┘

1 rows in set. Elapsed: 0.003 sec.

date_trunc(unit, value[, timezone])

date_trunc(unit, value[, timezone])将日期和时间数据截断为日期的指定部分。

句法

date_trunc(unit, value[, timezone])

别名：

dateTrunc。

参量

unit—日期的一部分。字串。
可能的值：
	second
	minute
	hour
	day
	week
	month
	quarter
	year
value- 日期和时间。参数类型必须为DateTime或DateTime64。
timezone—返回值的时区名称（可选）。如果未指定，则该函数使用value参数的时区。字串。

返回值

值，被截断为日期的指定部分。
类型：Datetime。

localhost :) select dateTrunc('second',now()) ;

SELECT dateTrunc('second', now())

Query id: 7a3365e7-4692-46aa-900d-7de5f19f2a30

┌─dateTrunc('second', now())─┐
│        2020-12-11 12:17:18 │
└────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select dateTrunc('minute',now()) ;

SELECT dateTrunc('minute', now())

Query id: 81ddb7d9-d85d-4bab-aabb-6ca3aaa52462

┌─dateTrunc('minute', now())─┐
│        2020-12-11 12:17:00 │
└────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select dateTrunc('hour',now()) ;

SELECT dateTrunc('hour', now())

Query id: 73711b09-7900-46d9-99a6-8e26cc62a678

┌─dateTrunc('hour', now())─┐
│      2020-12-11 12:00:00 │
└──────────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

localhost :) select dateTrunc('day',now()) ;

SELECT dateTrunc('day', now())

Query id: 02da6ac2-bc52-49a3-9f3f-e53f3f728a3b

┌─dateTrunc('day', now())─┐
│     2020-12-11 00:00:00 │
└─────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select dateTrunc('month',now()) ;

SELECT dateTrunc('month', now())

Query id: c302abe5-2eae-4bea-95e2-5e6b103390cd

┌─dateTrunc('month', now())─┐
│                2020-12-01 │
└───────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select dateTrunc('year',now()) ;

SELECT dateTrunc('year', now())

Query id: 03da5a68-ce27-4561-ae6d-09fa159f3853

┌─dateTrunc('year', now())─┐
│               2020-01-01 │
└──────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

使用第三个参数timezone指定分区：

localhost :) select dateTrunc('hour',now()) ;

SELECT dateTrunc('hour', now())

Query id: 68d4c07d-647a-45bb-82c9-36d2f8093b8a

┌─dateTrunc('hour', now())─┐
│      2020-12-11 12:00:00 │
└──────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select dateTrunc('hour',now(),'Asia/Shanghai') ;

SELECT dateTrunc('hour', now(), 'Asia/Shanghai')

Query id: 31c06b35-dd25-4b7d-b61f-1706a2889c07

┌─dateTrunc('hour', now(), 'Asia/Shanghai')─┐
│                       2020-12-11 12:00:00 │
└───────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select dateTrunc('hour',now(),'Asia/Yekaterinburg') ;

SELECT dateTrunc('hour', now(), 'Asia/Yekaterinburg')

Query id: 4adf4ae3-ba80-4f96-9412-1c0e59aac338

┌─dateTrunc('hour', now(), 'Asia/Yekaterinburg')─┐
│                            2020-12-11 09:00:00 │
└────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select dateTrunc('hour',now(),'US/Samoa') ;

SELECT dateTrunc('hour', now(), 'US/Samoa')

Query id: ee2a5294-84a8-4516-9164-9f231d408db3

┌─dateTrunc('hour', now(), 'US/Samoa')─┐
│                  2020-12-10 17:00:00 │
└──────────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

localhost :) select dateTrunc('hour',now(),'Asia/Tokyo') ;

SELECT dateTrunc('hour', now(), 'Asia/Tokyo')

Query id: 58e30d6b-a2e0-45b1-811a-09b3f9757de9

┌─dateTrunc('hour', now(), 'Asia/Tokyo')─┐
│                    2020-12-11 13:00:00 │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.036 sec.

localhost :) select dateTrunc('hour',now(),'Europe/Moscow') ;

SELECT dateTrunc('hour', now(), 'Europe/Moscow')

Query id: 366b978a-e746-4dc5-823f-8484c448cb7f

┌─dateTrunc('hour', now(), 'Europe/Moscow')─┐
│                       2020-12-11 07:00:00 │
└───────────────────────────────────────────┘

1 rows in set. Elapsed: 0.009 sec.

localhost :) select date_trunc('hour',now(),'Asia/Shanghai') AS date_trunc;

SELECT date_trunc('hour', now(), 'Asia/Shanghai') AS date_trunc

Query id: 7b1f0474-fba6-4a47-8f7d-c884814f4365

┌──────────date_trunc─┐
│ 2020-12-11 14:00:00 │
└─────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

dateDiff()

dateDiff()返回两个Date或DateTime值之间的差。

句法

dateDiff('unit', startdate, enddate, [timezone])

参量

unit—时间单位，表示返回值。字串。
Supported values: second, minute, hour, day, week, month, quarter, year.
startdate—要比较的第一个时间值。Date或DateTime。
enddate—要比较的第二时间值。Date或DateTime。
timezone—可选参数。如果指定，则同时应用于startdate和enddate。如果未指定，则使用startdate和的时区enddate。如果它们不相同，则结果不确定。该参数同时作用于startdate和enddate，使用该参数时应该保证此2值为同时区时间。

返回值

之间的差异startdate和enddate所表达unit。
类型：int。

localhost :) SELECT now();

SELECT now()

Query id: 7252750a-ab5c-4a2a-b72e-1c241734dab2

┌───────────────now()─┐
│ 2020-12-11 14:15:08 │
└─────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

localhost :) SELECT dateDiff('hour',yesterday(), today());

SELECT dateDiff('hour', yesterday(), today())

Query id: 6a007b5a-7bed-4100-b595-af19267e4fc4

┌─dateDiff('hour', yesterday(), today())─┐
│                                     24 │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.010 sec.

localhost :) SELECT dateDiff('hour',yesterday(), now());

SELECT dateDiff('hour', yesterday(), now())

Query id: 739fb39e-e543-4838-aaab-a0638b123fc5

┌─dateDiff('hour', yesterday(), now())─┐
│                                   38 │
└──────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

计算两个时刻在不同时间单位下的差值

localhost :) SELECT
:-]     toDateTime('2019-11-09 15:08:57', 'Asia/Shanghai') as datetime1,
:-]     toDateTime(now(), 'Asia/Shanghai') as datetime2, 
:-]     dateDiff('year', datetime1, datetime2) as diffYears,--返回两个时间相差多少年
:-]     dateDiff('month', datetime1, datetime2) as diffMonths,--返回两个时间相差多少月
:-]     dateDiff('week', datetime1, datetime2) as diffWeek,--返回两个时间相差多少周
:-]     dateDiff('day', datetime1, datetime2) as diffDays,--返回两个时间相差多少天
:-]     dateDiff('hour', datetime1, datetime2) as diffHours,--返回两个时间相差多少小时
:-]     dateDiff('minute', datetime1, datetime2) as diffMinutes,--返回两个时间相差多少分钟
:-]     dateDiff('second', datetime1, datetime2) as diffSeconds;--返回两个时间相差多少秒钟

SELECT
    toDateTime('2019-11-09 15:08:57', 'Asia/Shanghai') AS datetime1,
    toDateTime(now(), 'Asia/Shanghai') AS datetime2,
    dateDiff('year', datetime1, datetime2) AS diffYears,
    dateDiff('month', datetime1, datetime2) AS diffMonths,
    dateDiff('week', datetime1, datetime2) AS diffWeek,
    dateDiff('day', datetime1, datetime2) AS diffDays,
    dateDiff('hour', datetime1, datetime2) AS diffHours,
    dateDiff('minute', datetime1, datetime2) AS diffMinutes,
    dateDiff('second', datetime1, datetime2) AS diffSeconds

Query id: 7281dfa9-8813-46d0-a1bb-1b52b6b29d1b

┌───────────datetime1─┬───────────datetime2─┬─diffYears─┬─diffMonths─┬─diffWeek─┬─diffDays─┬─diffHours─┬─diffMinutes─┬─diffSeconds─┐
│ 2019-11-09 15:08:57 │ 2020-12-11 14:23:40 │         1 │         13 │       57 │      398 │      9551 │      573075 │    34384483 │
└─────────────────────┴─────────────────────┴───────────┴────────────┴──────────┴──────────┴───────────┴─────────────┴─────────────┘

1 rows in set. Elapsed: 0.011 sec.

localhost :) SELECT
:-]     dateDiff('year', now(), addYears(now(), 1)) as diffYears,--返回两个时间相差多少年份
:-]     dateDiff('month', now(), addMonths(now(), 2)) as diffMonths,--返回两个时间相差多少月份
:-]     dateDiff('week', now(), addWeeks(now(), 3)) as diffWeek,--返回两个时间相差多少周
:-]     dateDiff('day', now(), addDays(now(), 3)) as diffDays,--返回两个时间相差多少天
:-]     dateDiff('hour', now(), addHours(now(), 3)) as diffHours,--返回两个时间相差多少小时
:-]     dateDiff('minute', now(), addMinutes(now(), 30)) as diffMinutes,--返回两个时间相差多少分钟
:-]     dateDiff('second', now(), addSeconds(now(), 35)) as diffSeconds;--返回两个时间相差多少秒钟

SELECT
    dateDiff('year', now(), addYears(now(), 1)) AS diffYears,
    dateDiff('month', now(), addMonths(now(), 2)) AS diffMonths,
    dateDiff('week', now(), addWeeks(now(), 3)) AS diffWeek,
    dateDiff('day', now(), addDays(now(), 3)) AS diffDays,
    dateDiff('hour', now(), addHours(now(), 3)) AS diffHours,
    dateDiff('minute', now(), addMinutes(now(), 30)) AS diffMinutes,
    dateDiff('second', now(), addSeconds(now(), 35)) AS diffSeconds

Query id: bec02fef-a81d-4665-a17b-2d600b3703b5

┌─diffYears─┬─diffMonths─┬─diffWeek─┬─diffDays─┬─diffHours─┬─diffMinutes─┬─diffSeconds─┐
│         1 │          2 │        3 │        3 │         3 │          30 │          35 │
└───────────┴────────────┴──────────┴──────────┴───────────┴─────────────┴─────────────┘

1 rows in set. Elapsed: 0.004 sec.

timeSlot(datetime[,timezone])将时间向前取整半小时。

第一个参数类型为DateTime类型，第二个参数可以不写为时区

localhost :) select timeSlot(now());

SELECT timeSlot(now())

Query id: ba5de766-8bf0-42ed-9057-ce92e50fe4eb

┌─────timeSlot(now())─┐
│ 2020-12-11 14:30:00 │
└─────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

localhost :) select timeSlot(toDateTime('2020-12-10 12:59:59'));

SELECT timeSlot(toDateTime('2020-12-10 12:59:59'))

Query id: fef691c3-bf7b-479b-88de-909aa4553111

┌─timeSlot(toDateTime('2020-12-10 12:59:59'))─┐
│                         2020-12-10 12:30:00 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select timeSlot(toDateTime('2020-12-10 12:29:59'));

SELECT timeSlot(toDateTime('2020-12-10 12:29:59'))

Query id: fbb9f68e-9e96-421a-954c-da5edd9402c9

┌─timeSlot(toDateTime('2020-12-10 12:29:59'))─┐
│                         2020-12-10 12:00:00 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select timeSlot(toDateTime('2020-12-10 12:29:59'),'Asia/Shanghai');

SELECT timeSlot(toDateTime('2020-12-10 12:29:59'), 'Asia/Shanghai')

Query id: c22487b2-21f0-4d86-ac87-b92a7456b947

┌─timeSlot(toDateTime('2020-12-10 12:29:59'), 'Asia/Shanghai')─┐
│                                          2020-12-10 12:00:00 │
└──────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

addYears(date/datetime,+/-number), addMonths(date/datetime,+/-number), addWeeks(date/datetime,+/-number), addDays(date/datetime,+/-number), addHours(date/datetime,+/-number), addMinutes(date/datetime,+/-number), addSeconds(date/datetime,+/-number), addQuarters(date/datetime,+/-number)

addYears(date/datetime,+/-number)加（+）、减（-）number年,返回date/datetime类型数据由第一个参数date/datetime决定。
addMonths(date/datetime,+/-number)加（+）、减（-）number月,返回date/datetime类型数据由第一个参数date/datetime决定。
addWeeks(date/datetime,+/-number)加（+）、减（-）number周,返回date/datetime类型数据由第一个参数date/datetime决定。
addDays(date/datetime,+/-number)加（+）、减（-）number天,返回date/datetime类型数据由第一个参数date/datetime决定。等价于date_add()
addHours(date/datetime,+/-number)加（+）、减（-）number小时,返回date/datetime类型数据由第一个参数date/datetime决定。
addMinutes(date/datetime,+/-number)加（+）、减（-）number分钟,返回date/datetime类型数据由第一个参数date/datetime决定。
addSeconds(date/datetime,+/-number)加（+）、减（-）number秒钟,返回date/datetime类型数据由第一个参数date/datetime决定。
addQuarters(date/datetime,+/-number)加（+）、减（-）number季度,返回date/datetime类型数据由第一个参数date/datetime决定。

localhost :) select
:-] addYears(now(),1) AS addYears_datetime,
:-] addMonths(now(),1) AS addMonths_datetime,
:-] addWeeks(now(),1) AS addWeeks_datetime,
:-] addDays(now(),1) AS addDays_datetime,
:-] addHours(now(),1) AS addHours_datetime,
:-] addMinutes(now(),1) AS addMinutes_datetime,
:-] addSeconds(now(),1) AS addSeconds_datetime,
:-] addQuarters(now(),1) AS addQuarters_datetime,
:-] addYears(today(),-1) AS subYears_date,
:-] addMonths(today(),-1) AS subMonths_date,
:-] addWeeks(today(),-1) AS subWeeks_date,
:-] addDays(today(),-1) AS subDays_date,
:-] addHours(today(),-1) AS subHours_date,
:-] addMinutes(today(),-1) AS subMinutes_date,
:-] addSeconds(today(),-1) AS subSeconds_date,
:-] addQuarters(today(),-1) AS subQuarters_date
:-] FORMAT Vertical;

SELECT
    addYears(now(), 1) AS addYears_datetime,
    addMonths(now(), 1) AS addMonths_datetime,
    addWeeks(now(), 1) AS addWeeks_datetime,
    addDays(now(), 1) AS addDays_datetime,
    addHours(now(), 1) AS addHours_datetime,
    addMinutes(now(), 1) AS addMinutes_datetime,
    addSeconds(now(), 1) AS addSeconds_datetime,
    addQuarters(now(), 1) AS addQuarters_datetime,
    addYears(today(), -1) AS subYears_date,
    addMonths(today(), -1) AS subMonths_date,
    addWeeks(today(), -1) AS subWeeks_date,
    addDays(today(), -1) AS subDays_date,
    addHours(today(), -1) AS subHours_date,
    addMinutes(today(), -1) AS subMinutes_date,
    addSeconds(today(), -1) AS subSeconds_date,
    addQuarters(today(), -1) AS subQuarters_date
FORMAT Vertical

Query id: 48f61674-916f-4c9a-9171-f52e447a7d2c

Row 1:
──────
addYears_datetime:    2021-12-11 14:57:35
addMonths_datetime:   2021-01-11 14:57:35
addWeeks_datetime:    2020-12-18 14:57:35
addDays_datetime:     2020-12-12 14:57:35
addHours_datetime:    2020-12-11 15:57:35
addMinutes_datetime:  2020-12-11 14:58:35
addSeconds_datetime:  2020-12-11 14:57:36
addQuarters_datetime: 2021-03-11 14:57:35
subYears_date:        2019-12-11
subMonths_date:       2020-11-11
subWeeks_date:        2020-12-04
subDays_date:         2020-12-10
subHours_date:        2020-12-10 23:00:00
subMinutes_date:      2020-12-10 23:59:00
subSeconds_date:      2020-12-10 23:59:59
subQuarters_date:     2020-09-11

1 rows in set. Elapsed: 0.013 sec.

subtractYears(date/datetime,+/-number),subtractMonths(date/datetime,+/-number),subtractWeeks(date/datetime,+/-number),subtractDays(date/datetime,+/-number),subtractHours(date/datetime,+/-number),subtractMinutes(date/datetime,+/-number),subtractSeconds(date/datetime,+/-number),subtractQuarters (date/datetime,+/-number)

函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime。
subtractYears(date/datetime,+/-number)加（-）、减（+）number年,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractMonths(date/datetime,+/-number)加（-）、减（+）number月,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractWeeks(date/datetime,+/-number)加（-）、减（+）number周,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractDays(date/datetime,+/-number)加（-）、减（+）number天,返回date/datetime类型数据由第一个参数date/datetime决定。等价于date_add()
subtractHours(date/datetime,+/-number)加（-）、减（+）number小时,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractMinutes(date/datetime,+/-number)加（-）、减（+）number分钟,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractSeconds(date/datetime,+/-number)加（-）、减（+）number秒钟,返回date/datetime类型数据由第一个参数date/datetime决定。
subtractQuarters (date/datetime,+/-number)加（-）、减（+）number季度,返回date/datetime类型数据由第一个参数date/datetime决定。

localhost :) select
:-] subtractYears(now(),1) AS subtractYears_datetime,
:-] subtractMonths(now(),1) AS subtractMonths_datetime,
:-] subtractWeeks(now(),1) AS subtractWeeks_datetime,
:-] subtractDays(now(),1) AS subtractDays_datetime,
:-] subtractHours(now(),1) AS subtractHours_datetime,
:-] subtractMinutes(now(),1) AS subtractMinutes_datetime,
:-] subtractSeconds(now(),1) AS subtractSeconds_datetime,
:-] subtractQuarters (now(),1) AS subtractQuarters_datetime,
:-] subtractYears(today(),-1) AS subtractYears_date,
:-] subtractMonths(today(),-1) AS subtractMonths_date,
:-] subtractWeeks(today(),-1) AS subtractWeeks_date,
:-] subtractDays(today(),-1) AS subtractDays_date,
:-] subtractHours(today(),-1) AS subtractHours_date,
:-] subtractMinutes(today(),-1) AS subtractMinutes_date,
:-] subtractSeconds(today(),-1) AS subtractSeconds_date,
:-] subtractQuarters(today(),-1) AS subtractQuarters_date
:-] FORMAT Vertical;

SELECT
    subtractYears(now(), 1) AS subtractYears_datetime,
    subtractMonths(now(), 1) AS subtractMonths_datetime,
    subtractWeeks(now(), 1) AS subtractWeeks_datetime,
    subtractDays(now(), 1) AS subtractDays_datetime,
    subtractHours(now(), 1) AS subtractHours_datetime,
    subtractMinutes(now(), 1) AS subtractMinutes_datetime,
    subtractSeconds(now(), 1) AS subtractSeconds_datetime,
    subtractQuarters(now(), 1) AS subtractQuarters_datetime,
    subtractYears(today(), -1) AS subtractYears_date,
    subtractMonths(today(), -1) AS subtractMonths_date,
    subtractWeeks(today(), -1) AS subtractWeeks_date,
    subtractDays(today(), -1) AS subtractDays_date,
    subtractHours(today(), -1) AS subtractHours_date,
    subtractMinutes(today(), -1) AS subtractMinutes_date,
    subtractSeconds(today(), -1) AS subtractSeconds_date,
    subtractQuarters(today(), -1) AS subtractQuarters_date
FORMAT Vertical

Query id: b12d1080-0af5-4b4f-9210-5b745614d265

Row 1:
──────
subtractYears_datetime:    2019-12-11 15:12:24
subtractMonths_datetime:   2020-11-11 15:12:24
subtractWeeks_datetime:    2020-12-04 15:12:24
subtractDays_datetime:     2020-12-10 15:12:24
subtractHours_datetime:    2020-12-11 14:12:24
subtractMinutes_datetime:  2020-12-11 15:11:24
subtractSeconds_datetime:  2020-12-11 15:12:23
subtractQuarters_datetime: 2020-09-11 15:12:24
subtractYears_date:        2021-12-11
subtractMonths_date:       2021-01-11
subtractWeeks_date:        2020-12-18
subtractDays_date:         2020-12-12
subtractHours_date:        2020-12-11 01:00:00
subtractMinutes_date:      2020-12-11 00:01:00
subtractSeconds_date:      2020-12-11 00:00:01
subtractQuarters_date:     2021-03-11

1 rows in set. Elapsed: 0.007 sec.

timeSlots（StartTime,Duration[,Size]）

对于从StartTime开始并持续Duration秒的时间间隔，它将返回一个时间间隔数组，其中包括该时间间隔中的点，四舍五入到以秒为单位的Size。"大小"是一个可选参数：常数UInt32，默认设置为1800。
Duration时间间隔，参数类型必须为UInt32。
size每一分钟的大小，参数类型必须为UInt32。
示例如下规律自己体会：

localhost :) select timeSlots(now(),cast(360 AS UInt32),cast(60 AS UInt32));

SELECT timeSlots(now(), CAST(360, 'UInt32'), CAST(60, 'UInt32'))

Query id: 99814a48-a84f-41f0-a184-eb4983e714c1

┌─timeSlots(now(), CAST(360, 'UInt32'), CAST(60, 'UInt32'))───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ['2020-12-11 15:56:00','2020-12-11 15:57:00','2020-12-11 15:58:00','2020-12-11 15:59:00','2020-12-11 16:00:00','2020-12-11 16:01:00','2020-12-11 16:02:00'] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select timeSlots(now(),cast(180 AS UInt32),cast(60 AS UInt32));

SELECT timeSlots(now(), CAST(180, 'UInt32'), CAST(60, 'UInt32'))

Query id: 8411ee34-67b3-4bbc-b884-a795c58c53ad

┌─timeSlots(now(), CAST(180, 'UInt32'), CAST(60, 'UInt32'))─────────────────────────────────┐
│ ['2020-12-11 15:56:00','2020-12-11 15:57:00','2020-12-11 15:58:00','2020-12-11 15:59:00'] │
└───────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select timeSlots(now(),cast(120 AS UInt32),cast(60 AS UInt32));

SELECT timeSlots(now(), CAST(120, 'UInt32'), CAST(60, 'UInt32'))

Query id: 95cceab8-728a-4b30-841b-add385101c62

┌─timeSlots(now(), CAST(120, 'UInt32'), CAST(60, 'UInt32'))───────────┐
│ ['2020-12-11 15:57:00','2020-12-11 15:58:00','2020-12-11 15:59:00'] │
└─────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) select timeSlots(now(),cast(60 AS UInt32),cast(60 AS UInt32));

SELECT timeSlots(now(), CAST(60, 'UInt32'), CAST(60, 'UInt32'))

Query id: 7732a379-d10b-4626-817e-f9b86deb502b

┌─timeSlots(now(), CAST(60, 'UInt32'), CAST(60, 'UInt32'))─┐
│ ['2020-12-11 15:57:00','2020-12-11 15:58:00']            │
└──────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) select timeSlots(now(),cast(1 AS UInt32),cast(60 AS UInt32));

SELECT timeSlots(now(), CAST(1, 'UInt32'), CAST(60, 'UInt32'))

Query id: 93eb12a4-2f3c-453e-a28d-4648179328bf

┌─timeSlots(now(), CAST(1, 'UInt32'), CAST(60, 'UInt32'))─┐
│ ['2020-12-11 15:57:00']                                 │
└─────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

FROM_UNIXTIME()

参数类型可以为整型和DateTime类型。
当FROM_UNIXTIME()只有整数类型的单个参数时，它的作用toDateTime()与返回DateTime的方法相同。

localhost :) select toUnixTimestamp(now());

SELECT toUnixTimestamp(now())

Query id: 9b7738e5-b98c-4842-9673-9568cf9fbc28

┌─toUnixTimestamp(now())─┐
│             1607674077 │
└────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) SELECT FROM_UNIXTIME(1607674077) AS DateTime ;

SELECT FROM_UNIXTIME(1607674077) AS DateTime

Query id: f9e9e336-d8ab-4074-a3d6-bf535248f7a0

┌────────────DateTime─┐
│ 2020-12-11 16:07:57 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

localhost :) SELECT FROM_UNIXTIME(toUnixTimestamp(now())) AS DateTime ;

SELECT FROM_UNIXTIME(toUnixTimestamp(now())) AS DateTime

Query id: 877b6e58-5358-47c4-8a58-60ddf3aa6891

┌────────────DateTime─┐
│ 2020-12-11 16:08:50 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

当FROM_UNIXTIME()有两个参数时，第一个是整数或DateTime，第二个是常量格式字符串，它的作用formatDateTime与返回String类型相同。

localhost :) SELECT FROM_UNIXTIME(1607674077, '%Y-%m-%d %R:%S') AS DateTime ;

SELECT FROM_UNIXTIME(1607674077, '%Y-%m-%d %R:%S') AS DateTime

Query id: 2b272dec-be90-4138-a808-f5c3677aa17c

┌─DateTime────────────┐
│ 2020-12-11 16:07:57 │
└─────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

localhost :) SELECT FROM_UNIXTIME(toUnixTimestamp(now()), '%Y-%m-%d %R:%S') AS DateTime ;

SELECT FROM_UNIXTIME(toUnixTimestamp(now()), '%Y-%m-%d %R:%S') AS DateTime

Query id: f662c4ac-360f-4572-8898-48646f8d124e

┌─DateTime────────────┐
│ 2020-12-11 16:08:41 │
└─────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

总结：
到此，clickhouse基本时间日期函数都在此说明。

扫地增

关注

13
点赞
踩
61

收藏

觉得还不错? 一键收藏
打赏
2
评论
Clickhouse 时间日期函数实战总结

注：所有的时间日期函数都可以在第二个可选参数中接受时区参数。示例：Asia / Yekaterinburg。在这种情况下，它们使用指定的时区而不是本地（默认）时区。仅支持与UTC相差一整小时的时区。localhost :) select toDateTime(146600280) AS time,toDateTime(146600280,'Asia/Yekaterinburg') AS time_asia,toDateTime(146600280,'US/Samoa') AS time_us,toDa.
复制链接

扫一扫