在SQLite中修改日期函数

SQL的Postgres实现(例如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实现都具有这些强大的功能。对于我们的Scratch代码学习SQL, 我们使用SQLite, 这是一种轻量级的SQL实现, 可以在单个Docker实例上运行。 SQLite非常适合网站后端和小型项目, 但缺少我最喜欢的两个功能。幸运的是, 有解决方法。

为了模拟DATE_DIFF, 我们可以使用一个鲜为人知的函数juliandate。根据Wikipedia的说法, “儒略日编号(JDN)是指从世界标准时间正午开始在儒略日计数中分配给整个太阳日的整数, 而儒略日天数0则分配给1月1日星期一中午开始的那一天。公元前4713年”。通过将日期转换为浮点数, 我们可以使用减法来查找两个时间戳之间的差异。

我们甚至可以将答案乘以24来转换为小时, 或者将乘以24 * 60转换为分钟。

我们可以使用strftime模拟DATE_TRUNC的某些功能。此函数将时间戳转换为具有给定输出的字符串。

％dmonth of month：00％ffractional seconds：SS.SSS％Hhour：00-24％jday of year：001-366％JJulian day number％mmonth：01-12％Mminute：00-59％sseconds since 1970-01- 01％秒：00-59％0-6的星期几, 星期日== 0％W星期：00-53％YY年份：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基础的更多信息, 请参加srcmini的SQL for Data Science入门课程。