sql及Python常用时间处理函数
在sql及Python数据分析过程中，经常需要对时间进行转换和处理，对此进行了总结。
sql
hive
格式转换
flowchart TD
a[string]
b[timestamp]
c[unix_timestamp]
a -- to_timestamp(string dt, string pattern) --> b
b -- date_format(timestamp t, string pattern) --> a
b -- unix_timestamp(timestamp t) --> c
c -- from_unixtime(bigint unixtime) --> b
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
raw_str = '2023-01-14 00:00:00'
# 从文本转为时间
ts = datetime.strptime(raw_str, '%Y-%m-%d %H:%M:%S')
ts
Out[1]:
datetime.datetime(2023, 1, 14, 0, 0)
In [2]:
# 从时间转为文本
ts_str = ts.strftime('%Y-%m')
ts_str
Out[2]:
'2023-01'
In [3]:
# 简单的天和小时数间隔
# 可通过dateutil.relativedelta增加对年月间隔的支持
# from dateutil.relativedelta import relativedelta
# t3 = t2 + relativedelta(months=3, years=5)
t2 = ts + timedelta(days=-5, hours=6)
t2
Out[3]:
datetime.datetime(2023, 1, 9, 6, 0)
基于pandas包
In [4]:
# 格式转换
import pandas as pd
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']})
df
Out[4]:
| raw_str | |
|---|---|
| 0 | 2023-01-14 00:00:00 |
| 1 | 2023-01-21 00:00:00 |
| 2 | 2023-02-11 00:00:00 |
| 3 | 2023-02-18 00:00:00 |
In [5]:
# 从文本转为日期时间
df['ts'] = pd.to_datetime(df['raw_str'], format='%Y-%m-%d %H:%M:%S')
df
Out[5]:
| raw_str | ts | |
|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 |
In [6]:
# 从日期时间转为文本
df['str_month'] = df['ts'].dt.strftime('%Y-%m')
df
Out[6]:
| raw_str | ts | str_month | |
|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 |
In [7]:
# 可直接获取年月日季度等数值
df['quarter'] = df['ts'].dt.quarter
df
Out[7]:
| raw_str | ts | str_month | quarter | |
|---|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 | 1 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 | 1 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 | 1 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 | 1 |
In [8]:
# 指定年月日等参数
df['set_day_1'] = df['ts'].transform(lambda x: x.replace(day=1))
df
Out[8]:
| raw_str | ts | str_month | quarter | set_day_1 | |
|---|---|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 | 1 | 2023-01-01 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 | 1 | 2023-01-01 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 | 1 | 2023-02-01 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 | 1 | 2023-02-01 |
In [9]:
# 简单的天和小时数间隔
df['+30days'] = df['ts'] + pd.Timedelta(days=30)
df
Out[9]:
| raw_str | ts | str_month | quarter | set_day_1 | +30days | |
|---|---|---|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 | 1 | 2023-01-01 | 2023-02-13 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 | 1 | 2023-01-01 | 2023-02-20 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 | 1 | 2023-02-01 | 2023-03-13 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 | 1 | 2023-02-01 | 2023-03-20 |
In [10]:
# 通过DateOffset增加对年月间隔的支持
from pandas.tseries.offsets import DateOffset
df['months_years'] = df['ts'] + DateOffset(years=2, months=-3)
df
Out[10]:
| raw_str | ts | str_month | quarter | set_day_1 | +30days | months_years | |
|---|---|---|---|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 | 1 | 2023-01-01 | 2023-02-13 | 2024-10-14 |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 | 1 | 2023-01-01 | 2023-02-20 | 2024-10-21 |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 | 1 | 2023-02-01 | 2023-03-13 | 2024-11-11 |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 | 1 | 2023-02-01 | 2023-03-20 | 2024-11-18 |
In [11]:
# 时间差
df['diff'] = df['months_years'] - df['ts']
df
Out[11]:
| raw_str | ts | str_month | quarter | set_day_1 | +30days | months_years | diff | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2023-01-14 00:00:00 | 2023-01-14 | 2023-01 | 1 | 2023-01-01 | 2023-02-13 | 2024-10-14 | 639 days |
| 1 | 2023-01-21 00:00:00 | 2023-01-21 | 2023-01 | 1 | 2023-01-01 | 2023-02-20 | 2024-10-21 | 639 days |
| 2 | 2023-02-11 00:00:00 | 2023-02-11 | 2023-02 | 1 | 2023-02-01 | 2023-03-13 | 2024-11-11 | 639 days |
| 3 | 2023-02-18 00:00:00 | 2023-02-18 | 2023-02 | 1 | 2023-02-01 | 2023-03-20 | 2024-11-18 | 639 days |
In [12]:
df['diff'].dt.days
Out[12]:
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部分