在sql及Python数据分析过程中，经常需要对时间进行转换和处理，对此进行了总结。

sql

import sys
import io
from pig import DBConfig

%load_ext sql
sys.stdout = io.StringIO()

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 
engine_impala = DBConfig(db_category='impala').get_connection_text()
%sql $engine_impala

%%sql 
select 
    to_timestamp('2024-11-01', 'yyyy-MM-dd') str_to_ts, 
    from_timestamp('2024-11-01', 'yyyyMM') ts_to_str, 
    unix_timestamp('2024-11-01', 'yyyy-MM-dd') str_to_uts,
    from_unixtime(unix_timestamp('2024-11-01', 'yyyy-MM-dd'), 'yyyyMM') uts_to_str,
    unix_timestamp('2024-11-01') ts_to_uts, 
    to_timestamp(unix_timestamp('2024-11-01')) uts_to_ts
str_to_ts ts_to_str str_to_uts uts_to_str ts_to_uts uts_to_ts
2024-11-01 00:00:00 202411 1730419200 202411 1730419200 2024-11-01 00:00:00

hive

格式转换

取消timestamp节点后，可复用impala架构。

engine_hive = DBConfig(db_category='hive').get_connection_text()
%sql $engine_hive

%%sql 
select 
    unix_timestamp('2024-11-01', 'yyyy-MM-dd') str_to_uts,
    from_unixtime(unix_timestamp('2024-11-01', 'yyyy-MM-dd'), 'yyyyMM') uts_to_str
str_to_uts uts_to_str
1730390400 202411

oracle

格式转换

flowchart TD a[string] b[timestamp] a -- to_date(string dt, string pattern) --> b b -- to_char(timestamp t, string pattern) --> a 
engine_oracle = DBConfig(db_category='oracle').get_connection_text()
%sql $engine_oracle

%%sql 
select 
    to_date('2024-11-01', 'yyyy-MM-dd') str_to_ts, 
    to_char(to_date('2024-11-01', 'yyyy-MM-dd'), 'yyyyMM') ts_to_str
from dual
str_to_ts ts_to_str
2024-11-01 00:00:00 202411

python

基于datetime模块

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

datetime.datetime(2023, 1, 14, 0, 0)

# 从时间转为文本
ts_str = ts.strftime('%Y-%m')
ts_str

'2023-01'

# 简单的天和小时数间隔  
t2 = ts + timedelta(days=-5, hours=6)
t2

datetime.datetime(2023, 1, 9, 6, 0)

# 通过dateutil.relativedelta增加对年月间隔的支持
from dateutil.relativedelta import relativedelta
t3 = t2 + relativedelta(months=3, years=5)
t3

datetime.datetime(2028, 4, 9, 6, 0)

# 日期构建
t4 = date(t3.year, t3.month, t3.day)
t4

datetime.date(2028, 4, 9)

t5 = t4.replace(day=10)
t5

datetime.date(2028, 4, 10)

# 时间差
delta = t3 - t2
delta

datetime.timedelta(days=1917)

基于pandas包

# 格式转换
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
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 
# 从文本转为日期时间
df['ts'] = pd.to_datetime(df['raw_str'], format='%Y-%m-%d %H:%M:%S')
df
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 
# 从日期时间转为文本
df['str_month'] = df['ts'].dt.strftime('%Y-%m')
df
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 
# 可直接获取年月日季度等数值
df['quarter'] = df['ts'].dt.quarter
df
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 
# 指定年月日等参数
df['set_day_1'] = df['ts'].transform(lambda x: x.replace(day=1))
df
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 
# 简单的天和小时数间隔
df['+30days'] = df['ts'] + pd.Timedelta(days=30)
df
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 
# 通过DateOffset增加对年月间隔的支持
from pandas.tseries.offsets import DateOffset
df['months_years'] = df['ts'] + DateOffset(years=2, months=-3)
df
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 
# 时间差
df['diff'] = df['months_years'] - df['ts']
df
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 
df['diff'].dt.days

0    639
1    639
2    639
3    639
Name: diff, dtype: int64

分析和总结

  1. 数据库的时间处理方法，可视化后，基本都是一些范式；
  2. 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部分