SQL中的日期和时间
无论您有多少SQL经验,处理日期和时间都不是一项简单的任务,它们令人惊讶地棘手,有时候甚至要花费好几个小时才能解决问题。
本文将介绍一些对新手和专家都有益的日期/时间处理技巧。
操作卡
在深入代码之前,先对高级工具进行评估。就像玩棋盘游戏Pandemic
,让您角色的操作卡始终可见,这就能在每个回合中知道要采取什么操作。
下面是我们的SQL日期/时间操作卡,包括可以对日期/时间执行哪些操作。每个操作都有一系列单独的函数和语法。
假设您有一些带有字符串日期列的销售数据,而老板想要查看季度总计,您需要:
- 将字符串解析为日期(“20200110” -> 2020-01-10)
- 将日期截断到每个季度的开始(2020-01-10 -> 2020-01-01)
- 格式化日期以在图表或表格中看起来更直观(2020-01-01 -> “2020 Q1”)
对于上述操作,SQL的每种方言都有对应的函数,甚至还有独特的语法。因此,我不会详细介绍每个操作,您可以查看以下指南:
我的MVH(Most Valuable Hacks)
1. 隐式转换
大多数数据库都能实现“隐式转换”,这意味着您可以键入日期字符串如“2020-01-01”
,数据库会自动计算出日期DATE Jan 1, 2020
。
这对于过滤日期特别有用。
示例:提取2017年的所有伦敦天气数据。
为什么我喜欢这个技巧:如果没有隐式转换,我必须将字符串转换为日期,或者使用提取函数来获取过滤时想要的日期部分,但是对于像这样的简单日期范围,使用'YYYY-MM-DD'
的字符串作为过滤器是最高效的解决办法。
警告:大多数数据库要求日期采用标准ISO格式:'YYYY-MM-DD'
,但有些允许您自定义此格式。
2. 放眼全球
作为一个生活在欧洲的美国人,每当我看到像2020/10/06
这样的日期时,我都会有一阵恐慌,因为我不确定到底是10月6日还是6月10日。
而且,当我看到2020/10/06 10:00 AM
时,我更加困惑,是当地时间上午10点吗?还是世界标准时间?是否要考虑夏令时?
即使您的数据和团队都位于同一时区,考虑日期和时间的存储和呈现方式也至关重要。
为了解决这个问题,我倾向于:
- 始终使用UTC时间戳,如果需要,为时区调整的日期和时间创建新列。
- 在显示日期时,我总是将日期重新格式化为:MONTH DAY, YEAR。
示例:在Netflix数据的分析中,每个节目的开始时间都是UTC时间戳,但是要计算出一天中看电视最多的时间,我需要将其转换为当地时间。
为什么我喜欢这个技巧:让我安心地看到日期和时间同时表示为UTC和本地时间,进而选择处理问题的最佳方式。
警告:就日期的显示方式而言,每个工具都有不同的默认值。例如,在许多BI工具中,时间戳会自动调整为本地时间,因此您的定制转换可能是多余的。
3. 生成日期
如果不想找出事情发生的时间,而是想知道它们什么时候没有发生怎么办?
这需要生成一组数据集中不存在的统一日期。
这在进行同期群分析(cohort analyses)时尤其有用。
示例:假设我想查看在给定时间段内每天观看了多少小时的电视(包括没有观看电视的时间),我需要先生成想要查看的日期,然后将其加入原始数据中,当哪天没有看电视时,那个日期应该会出现一个NULL值:
为什么我喜欢这个技巧:找出某件事什么时候没有发生,与找出它们什么时候发生同样重要。
警告:每种SQL语法都有完全不同的方法来执行此操作。
数据黑客:开源金融数据仓库,专注数据工程和机器学习。
- 作者:Taylor Brownlow
- 来源:Medium
- 原文:Take your SQL from Good to Great: Part 2
- 翻译:数据黑客