ReaderMX.
ClickHouseInc

ClickHouse日期时间解析：5大函数让你事半功倍！

CMark Needham约 6 分钟
图片

本文字数：4286；估计阅读时间：11 分钟

作者：Mark Needham

Image

Meetup活动

ClickHouse 上海第3届 Meetup 讲师招募中，欢迎讲师在文末扫码报名！

图片

日期数据的形式多种多样——它们可能是事件流中的 Unix 时间戳，遗留数据库导出中格式奇异的数值日期，API 返回的 ISO 8601 字符串，等等。幸运的是，ClickHouse 提供了一套丰富的功能来处理所有这些情况，而这正是本文将要深入探讨的内容。

我们将首先介绍最直接的处理方法：使用 fromUnixTimestamp 转换 Unix 时间戳，使用 YYYYMMDDToDate 解析紧凑的数字日期格式，以及使用 parseDateTime 解析已知格式的字符串。接着，当日期格式未知或混合时，我们将探讨 parseDateTimeBestEffort 系列函数。

最后，我们将讨论在某些特定场景下，通过 cast_string_to_date_time_mode 设置来转换日期，可能比显式调用函数更为优越。

Image
Unix 时间戳

首先，我们来看 Unix 时间戳！Unix 时间戳表示自 1970 年 1 月 1 日以来的秒数。我们可以使用 fromUnixTimestamp 函数进行转换：

SELECT

    fromUnixTimestamp(1704067295) AS val1, toTypeName(val1);

这将返回一个 DateTime 类型。如果您持有自 1970 年 1 月 1 日以来的毫秒数，则应使用另一个函数 — fromUnixTimestamp64Milli — 此时返回的类型为 DateTime64(3)，其中 3 代表精度可达毫秒。

SELECT

    fromUnixTimestamp64Milli(1704067295123) AS val2, toTypeName(val2);

对于微秒，则使用 fromUnixTimestamp64Micro 函数，它会返回 DateTime64(6) 类型：

SELECT

    fromUnixTimestamp64Micro(1704067295123456) AS val3, toTypeName(val3);

Image
数字日期格式

有时，日期会以纯数字形式表示，其中直接编码了年、月、日，没有任何分隔符或特定格式。这种情况在遗留数据库导出或大型机 (mainframe) 的平面文件中很常见。YYYYMMDDToDate 函数专门用于处理此类格式：

SELECT

    YYYYMMDDToDate(20240115) AS val1, toTypeName(val1);

如果该数字还包含了时间信息，YYYYMMDDhhmmssToDateTime 函数也能胜任：

SELECT

    YYYYMMDDhhmmssToDateTime(20240115143022) AS val2, toTypeName(val2);

Image
已知格式字符串

API 通常会将日期作为字符串返回。如果您已知其具体格式，可以使用 parseDateTime 函数并指定一个 MySQL 日期格式字符串进行解析：

SELECT

    parseDateTime('15/01/2024 14:30:22', '%d/%m/%Y %H:%i:%s') AS val1,

    toTypeName(val1);

这将返回一个包含时区信息的 DateTime 类型数据。

如果你更喜欢 Joda 日期格式字符串，可以使用 parseDateTimeInJodaSyntax 函数，它会产生相同的结果：

SELECT

    parseDateTimeInJodaSyntax('15/01/2024 14:30:22', 'dd/MM/yyyy HH:mm:ss') AS val2,

    toTypeName(val2);

Image
DateTime 的最佳尝试解析

前面介绍的三种方法都假定我们已知精确的日期格式。但如果我们不知道怎么办？这时就轮到 parseDateTimeBestEffort 系列函数登场了。假设我们有混合了不同格式的日期：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

)

SELECT raw, parseDateTimeBestEffort(raw) AS val, toTypeName(val)

FROM dates;

我们也可以像之前介绍的函数那样，使用 parseDateTimeBestEffort64 将其转换为 DateTime64 类型：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

)

SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)

FROM dates;

如果我们传入一个完全无效的日期会怎样？

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

    UNION ALL

    SELECT 'not a date' AS raw

)

SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)

FROM dates;

ClickHouse 会抛出异常！

我们可以通过 parseDateTimeBestEffort64OrNull 对应函数来规避这个问题，它会返回 NULL：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

    UNION ALL

    SELECT 'not a date' AS raw

)

SELECT raw, parseDateTime64BestEffortOrNull(raw) AS val, toTypeName(val)

FROM dates;

或者，如果你希望得到一个实际的日期时间值，parseDateTimeBestEffort64OrZero 会回退到 1970 年 1 月 1 日午夜：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

    UNION ALL

    SELECT 'not a date' AS raw

)

SELECT raw, parseDateTime64BestEffortOrZero(raw) AS val, toTypeName(val)

FROM dates;

Image
类型转换 (Casting)

如果你想避免在查询中显式调用解析函数，可以使用 ::DateTime 将字符串值直接强制转换为日期类型。然而，这里有一个值得关注的重要设置：cast_string_to_date_time_mode。

默认情况下，它设置为 basic，可以处理 YYYY-MM-DD 和 YYYY-MM-DD HH:MM:SS 等标准格式，但其他任何格式都将失败。为了支持更广泛的日期格式，请将其更改为 best_effort。请注意，此设置对于完全无效的日期仍然会抛出异常。

你可以在每个查询中以内联方式指定此设置：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

)

SELECT raw, raw::DateTime AS val, toTypeName(val)

FROM dates

SETTINGS cast_string_to_date_time_mode = 'best_effort';

或者在会话 (session) 级别配置它，这样你就不必在每个查询中都指定它：

SET cast_string_to_date_time_mode = 'best_effort';

这样，相同的查询无需 SETTINGS 子句即可正常工作：

WITH dates AS (

    SELECT '2024-01-15T14:30:22.000Z' AS raw

    UNION ALL

    SELECT '2024-01-15' AS raw

    UNION ALL

    SELECT '1704067295' AS raw

)

SELECT raw, raw::DateTime AS val, toTypeName(val)

FROM dates;

最后，假设我们有以下文件，其中包含了多种日期格式的数据：

dates.csv

raw

2024-01-15T14:30:22.000Z

2024-01-15

1704067295

我们可以使用相同的方法解析该文件中的日期数据：

SELECT raw, raw::DateTime AS val, toTypeName(val)

FROM file('dates.csv', CSVWithNames);

┌─raw──────────────────────┬─────────────────val─┬─toTypeName(val)─┐

│ 2024-01-15T14:30:22.000Z │ 2024-01-15 14:30:22 │ DateTime        │

│ 2024-01-15               │ 2024-01-15 00:00:00 │ DateTime        │

│ 1704067295               │ 2024-01-01 00:01:35 │ DateTime        │

└──────────────────────────┴─────────────────────┴─────────────────┘

图片
Meetup 活动讲师招募

我们正为上海活动招募讲师，如果你有独特的技术见解、实践经验或 ClickHouse 使用故事，非常欢迎你加入我们，成为这次活动的讲师，与大家分享你的经验。

点击此处或扫描下方二维码，立刻报名成为讲师！
图片

/END/

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G

图片
图片

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]

图片图片

KEEP THE THREAD GOING

更多来源文章