Python与SQL: 实现时间间隔函数

煮雨小筑

已于 2024-03-26 14:42:22 修改

阅读量414

点赞数 5

文章标签： python 开发语言 clickhouse postgresql

于 2024-03-26 14:41:49 首次发布

本文链接：https://blog.csdn.net/weixin_44874457/article/details/137045591

版权

文章目录

前言

在数据分析过程中，我们常常需要将某个时间四舍五入到指定时间间隔的起始时间，如给定的时间为'2024-03-26 14:25:59'，但是我们想以30分钟为间隔分组统计数据，那么就希望'2024-03-26 14:25:59'变成'2024-03-26 14:00:00'。下面提供了python和sql的实现过程。

一、python实现过程

import pandas as pd 

# 示例数据
data = pd.DataFrame({'ftime': ['2024-03-26 07:00:00', '2024-03-26 07:15:00', '2024-03-26 07:29:59', '2024-03-26 07:50:00'],
                    'value': [1, 2, 3, 4]})

def get_interval_window(dts, interval=30):
    """传入一个时间返回特定间隔的开始时间
    如每30分钟为间隔 则传入2023-10-10 12:10:12 返回2023-10-10 12:00:00
    :param dts: 时间格式的值
    :param interval: 以几分钟为间隔  取值在0-60之间
    demo: data['a'] = pd.to_datetime(data['a'], format='') 可以加format参数
    data['b'] = data['a'].apply(lambda x: get_interval_window(x,interval=5))"""
    dt1 = dts.replace(minute=(dts.minute // interval) * interval).replace(second=0).replace(microsecond=0)
    return dt1

data['ftime'] = pd.to_datetime(data['ftime'])
data['interval'] = data['ftime'].apply(lambda x: get_interval_window(x))
data

输出结果如下，

二、SQL实现过程

1.clickhouse

clickhouse中主要用到toStartOfInterval函数

-- toStartOfInterval(column, interval 1 hour)
-- column: 时间列列名
-- interval: 时间间隔 interval 1 hour: 间隔1个小时  interval 30 minute: 间隔30分钟
select toStartOfInterval(ftime, interval 30 minute) as interval 
from table_name

-- 如果不想在clickhouse中建立示例数据去试验该函数，可以像下面这样，传入一个具体的时间值
select toStartOfInterval(toDateTime('2024-02-10 12:13:49'), interval 30 minute) as interval

2.postgresql

postgresql中没有直接的函数，需间接求得

-- postgresql中实现toStartOfInterval功能
-- 以每30分钟为间隔举例
-- 首先使用date_trunc函数, 只截取时间到小时 如'2024-12-12 12:35:31'将被截取到'2024-12-12 12:00:00'
select date_trunc('hour', timestamp '2024-12-12 12:35:31')
-- 输出: 2024-12-12 12:00:00

-- 然后计算原本时间的分钟数中包含多少个30分钟, 则要使用extract函数, 提取出分钟数
select extract(minute from timestamp '2024-12-12 12:35:31')
-- 输出: 35

-- 计算提取出的分钟数整除30分钟的商 floor是向下取整的函数
select floor(extract(minute from timestamp '2024-12-12 12:35:31') / 30)
-- 输出: 1 说明35分钟包含1个30分钟，则接下来只需要在date_trunc的基础上 加上1 * 30分钟即可

-- 使用interval函数来实现加上 1 * 30分钟的过程
select (date_trunc('hour', timestamp '2024-12-12 12:35:31') + 
        interval '30 minutes' * floor(extract(minute from timestamp '2024-12-12 12:35:31') / 30)) as interval 
-- 输出: 2024-12-12 12:30:00


-- 假如ftime是需要实现时间间隔的列，则完整代码如下，
select (date_trunc('hour', 'ftime') + interval '30 minutes' * floor(extract(minute from ftime) / 30)) as interval

date_trunc(unit, timestamp) : 用于将日期或时间戳截断到指定的精度
- unit: 指定截断的时间单位，可以是year、quarter、month、week、day、hour、minute、second等
- timestamp：要进行截断的日期或时间戳
extract(field from source): 用于从日期或时间戳中提取特定的部分
- field: 表示要提取的部分，可以是 year、month、day、hour、minute、second 等
- source：表示要从中提取部分的日期或时间戳
interval 'value unit': 表示一定的时间间隔 interval '1 hour'就表示1小时, + interval '1 hour'表示加1小时
- value：表示时间间隔的数值部分，可以为整数或小数
- unit：表示时间间隔的单位，可以是 year、month、day、hour、minute、second 等
floor: 向下取整函数，取小于所给值的最大的整数
- floor(1.8) = 1
- floor(1.2) = 1