跳至主要内容

在 SQLite 中巧用日期函数

在本教程中，学习如何在 SQLite 中使用日期函数。
· 3分钟 阅读

使用 AI 探索

在 Postgres 系列的 SQL 实现（如 Amazon Redshift）中，一些最实用的函数是 DATE_DIFF 和 DATE_TRUNC：

  • DATE_DIFF 返回两个日期之间经过的时间量。例如，下面的代码会给出 date1 与 date2 之间的天数：
DATE_DIFF('day', date1, date2)

DATE_DIFF 非常适合计算从注册到取消的天数，或从登录到登出的小时数。

  • DATE_TRUNC 会将日期截断到最近的日、周、月或年。例如，下面的代码会给出时间戳 my_timestamp 所在周的周一：
DATE_TRUNC('week', my_timestamp)

DATE_TRUNC 非常适合用于数据聚合。例如，我们可以用它通过截断到每月的起始日期来统计月活跃用户数（MAU）。

但并非所有 SQL 实现都提供这些优秀函数。我们的从零开始学习 SQL代码采用的是 SQLite，这是一种可在单个 Docker 实例上运行的轻量级 SQL 实现。SQLite 非常适合网站后端和小型项目，但缺少我最喜欢的这两个函数。好在，我们有替代方法。

要模拟 DATE_DIFF，我们可以使用一个鲜为人知的函数 juliandate。据维基百科所述，“儒略日编号（JDN）是指从世界时正午开始计数的儒略日序列中分配给整太阳日的整数，其中儒略日编号 0 分配给公元前 4713 年 1 月 1 日（星期一）中午开始的那一天”。通过将日期转换为浮点数，我们可以通过相减来求出两个时间戳之间的差。

julian day number

我们甚至可以通过乘以 24 将结果转换为小时，或通过乘以 24 * 60 转换为分钟。

我们还可以通过使用 strftime 来模拟 DATE_TRUNC 的部分功能。该函数会将时间戳按给定的输出格式转换为字符串。

%d 月中的日：00 %f 小数秒：SS.SSS %H 小时：00-24 %j 年中的第几天：001-366 %J 儒略日编号 %m 月份：01-12 %M 分钟：00-59 %s 自 1970-01-01 起的秒数 %S 秒：00-59 %w 星期几 0-6，周日==0 %W 年中的第几周：00-53 %Y 年：0000-9999

通常我们可以用它在不同的时间戳格式之间转换，例如将 YYYY-MM-DD 转为 MM-DD-YYYY：

strftime('%M-%D-%Y', mydate)

但我们也可以巧妙选择格式来截断到相应的粒度。例如，如果我们想截断到最近的月份，可以这样做：

strftime('%M/%Y', mydate)

或者，我们可以通过以下方式截断到最近的周：

strftime('%Y-%w', mydate)

借助这两个简单技巧，您就能在 SQLite 中完成一些与 Amazon Redshift 相同的精彩分析！

如果您想进一步学习 SQL 基础，欢迎参加 DataCamp 的 数据科学 SQL 入门课程，并查看我们的SQL 新手教程。

主题

SQL