编程学习（二）：sql及Python常用时间处理函数汇总
在sql及Python数据分析过程中，经常需要对时间进行转换和处理，对此进行了总结。
sql
implala
格式转换
flowchart TD
a[string]
b[timestamp]
c[unix_timestamp]
a -- to_timestamp(string dt, string pattern) --> b
b -- from_timestamp(timestamp t, string pattern) --> a
a -- unix_timestamp(string dt, string pattern) --> c
c -- from_unixtime(bigint unixtime, string pattern) --> a
b -- unix_timestamp(timestamp t) --> c
c -- to_timestamp(bigint unixtime) --> b
hive
格式转换
取消timestamp节点后，可复用impala架构。
oracle
格式转换
flowchart TD
a[string]
b[timestamp]
a -- to_date(string dt, string pattern) --> b
b -- to_char(timestamp t, string pattern) --> a
Python
基于datetime模块
格式转换
[In] [1]: from datetime import datetime, date, timedelta
[In] [2]: raw_str = '2023-01-14 00:00:00'
从文本转为时间
[In] [3]: ts = datetime.strptime(raw_str, '%Y-%m-%d %H:%M:%S')
[In] [4]: ts
[Out][4]: datetime.datetime(2023, 1, 14, 0, 0)
从时间转为文本
[In] [5]: ts_str = ts.strftime('%Y-%m')
[In] [6]: ts_str
[Out][6]: '2023-01'
数据运算
简单的天和小时数间隔
[In] [7]: t2 = ts + timedelta(days=-5, hours=6)
[In] [8]: t2
[Out][8]: datetime.datetime(2023, 1, 9, 6, 0)
通过dateutil.relativedelta增加对年月间隔的支持
[In] [9]: from dateutil.relativedelta import relativedelta
[In] [10]: t3 = t2 + relativedelta(months=3, years=5)
[In] [11]: t3
[Out][11]: datetime.datetime(2028, 4, 9, 6, 0)
日期构建
[In] [12]: t4 = date(t3.year, t3.month, t3.day)
[In] [13]: t4
[Out][13]: datetime.date(2028, 4, 9)
[In] [14]: t5 = t4.replace(day=10)
[In] [15]: t5
[Out][15]: datetime.date(2028, 4, 10)
时间差
[In] [16]: delta = t3 - t2
[In] [17]: delta
[Out][17]: datetime.timedelta(days=1917)
基于pandas包
格式转换
[In] [1]: import pandas as pd
[In] [2]: df = pd.
[In] [3]: df = pd.DataFrame({'raw_str': [
'2023-01-14 00:00:00',
'2023-01-21 00:00:00',
'2023-02-11 00:00:00',
'2023-02-18 00:00:00']})
[Out][3]:
| raw_str |
|:-------------------:|
| 2023-01-14 00:00:00 |
| 2023-01-21 00:00:00 |
| 2023-02-11 00:00:00 |
| 2023-02-18 00:00:00 |
从文本转为日期时间
[In] [4]: df['ts'] = pd.to_datetime(df['raw_str'], format='%Y-%m-%d %H:%M:%S')
[In] [5]: df
[Out][5]:
| raw_str | ts |
|:--------------------|:--------------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 |
从日期时间转为文本
[In] [6]: df['str_month'] = df['ts'].dt.strftime('%Y-%m')
[In] [7]: df
[Out][7]:
| raw_str | ts | str_month |
|:--------------------|:--------------------|:------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2023-01 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2023-01 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2023-02 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2023-02 |
可直接获取年月日季度等数值
[In] [8]: df['quarter'] = df['ts'].dt.quarter
[In] [9]: df
[Out][9]:
| raw_str | ts | str_month | quarter |
|:--------------------|:--------------------|:------------|----------:|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2023-01 | 1 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2023-01 | 1 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2023-02 | 1 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2023-02 | 1 |
指定年月日等参数
[In] [10]: df['set_day_1'] = df['ts'].transform(lambda x: x.replace(day=1))
[In] [11]: df
[Out][11]:
| raw_str | ts | set_day_1 |
|:--------------------|:--------------------|:--------------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2023-01-01 00:00:00 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2023-01-01 00:00:00 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2023-02-01 00:00:00 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2023-02-01 00:00:00 |
数据运算
简单的天和小时数间隔
[In] [12]: df['+30days'] = df['ts'] + pd.Timedelta(days=30)
[In] [13]: df
[Out][13]:
| raw_str | ts | +30days |
|:--------------------|:--------------------|:--------------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2023-02-13 00:00:00 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2023-02-20 00:00:00 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2023-03-13 00:00:00 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2023-03-20 00:00:00 |
通过DateOffset增加对年月间隔的支持
[In] [14]: from pandas.tseries.offsets import DateOffset
[In] [15]: df['months_years'] = df['ts'] + DateOffset(years=2, months=-3)
[Out][15]:
| raw_str | ts | months_years |
|:--------------------|:--------------------|:--------------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2024-10-14 00:00:00 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2024-10-21 00:00:00 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2024-11-11 00:00:00 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2024-11-18 00:00:00 |
时间差
[In] [16]: df['diff'] = df['months_years'] - df['ts']
[In] [17]: df
[Out][17]:
| raw_str | ts | months_years | diff |
|:--------------------|:--------------------|:--------------------|:------------------|
| 2023-01-14 00:00:00 | 2023-01-14 00:00:00 | 2024-10-14 00:00:00 | 639 days 00:00:00 |
| 2023-01-21 00:00:00 | 2023-01-21 00:00:00 | 2024-10-21 00:00:00 | 639 days 00:00:00 |
| 2023-02-11 00:00:00 | 2023-02-11 00:00:00 | 2024-11-11 00:00:00 | 639 days 00:00:00 |
| 2023-02-18 00:00:00 | 2023-02-18 00:00:00 | 2024-11-18 00:00:00 | 639 days 00:00:00 |
[In] [18]: df['diff'].dt.days
[Out][18]:
0 639
1 639
2 639
3 639
Name: diff, dtype: int64
分析和总结
- 数据库的时间处理方法，可视化后，基本都是一些范式；
- Python中datetime和pandas，类和方法基本是对应的。
附表
附表1：Python时间代码
| 指令 | 含意 | 示例 | 备注 |
|---|---|---|---|
| %a | 当地工作日的缩写。 | Sun, Mon, …, Sat (en_US); So, Mo, …, Sa (de_DE) | (1) |
| %A | 本地化的星期中每日的完整名称。 | Sunday, Monday, …, Saturday (en_US); Sonntag, Montag, …, Samstag (de_DE) | (1) |
| %w | 以十进制数显示的工作日，其中0表示星期日，6表示星期六。 | 0, 1, …, 6 | nan |
| %d | 补零后，以十进制数显示的月份中的一天。 | 01, 02, …, 31 | (9) |
| %b | 当地月份的缩写。 | Jan, Feb, …, Dec (en_US); Jan, Feb, …, Dez (de_DE) | (1) |
| %B | 本地化的月份全名。 | January, February, …, December (en_US); Januar, Februar, …, Dezember (de_DE) | (1) |
| %m | 补零后，以十进制数显示的月份。 | 01, 02, …, 12 | (9) |
| %y | 补零后，以十进制数表示的，不带世纪的年份。 | 00, 01, …, 99 | (9) |
| %Y | 十进制数表示的带世纪的年份。 | 0001, 0002, …, 2013, 2014, …, 9998, 9999 | (2) |
| %H | 以补零后的十进制数表示的小时（24 小时制）。 | 00, 01, …, 23 | (9) |
| %I | 以补零后的十进制数表示的小时（12 小时制）。 | 01, 02, …, 12 | (9) |
| %p | 本地化的 AM 或 PM 。 | AM, PM (en_US); am, pm (de_DE) | (1), (3) |
| %M | 补零后，以十进制数显示的分钟。 | 00, 01, …, 59 | (9) |
| %S | 补零后，以十进制数显示的秒。 | 00, 01, …, 59 | (4), (9) |
| %f | 微秒作为一个十进制数，零填充到 6 位。 | 000000, 000001, …, 999999 | (5) |
| %z | UTC 偏移量，格式为 ±HHMM[SS[.ffffff]] （如果是简单型对象则为空字符串）。 | (空), +0000, -0400, +1030, +063415, -030712.345216 | (6) |
| %Z | 时区名称（如果对象为简单型则为空字符串）。 | (空), UTC, GMT | (6) |
| %j | 以补零后的十进制数表示的一年中的日序号。 | 001, 002, …, 366 | (9) |
| %U | 以补零后的十进制数表示的一年中的周序号（星期日作为每周的第一天）。 在新的一年中第一个星期日之前的所有日子都被视为是在第 0 周。 | 00, 01, …, 53 | (7), (9) |
| %W | 以补零后的十进制数表示的一年中的周序号（星期一作为每周的第一天）。 在新的一年中第一个星期一之前的所有日子都被视为是在第 0 周。 | 00, 01, …, 53 | (7), (9) |
| %c | 本地化的适当日期和时间表示。 | Tue Aug 16 21:30:00 1988 (en_US); Di 16 Aug 21:30:00 1988 (de_DE) | (1) |
| %x | 本地化的适当日期表示。 | 08/16/88 (None); 08/16/1988 (en_US); 16.08.1988 (de_DE) | (1) |
| %X | 本地化的适当时间表示。 | 21:30:00 (en_US); 21:30:00 (de_DE) | (1) |
| %% | 字面的 ‘%’ 字符。 | % | nan |
版本记录
2024-01-27，初稿
2024-02-20，增加Python部分