数据库构建时间序列

pgsql 构建时间序列

时间戳转时间

# 时间戳转分钟
to_char(to_timestamp(ts)  AT TIME ZONE 'Asia/Shanghai','yyyy-MM-dd HH24:MI:00')
# 时间戳转天
to_char(to_timestamp(ts)  AT TIME ZONE 'UTC','yyyy-MM-dd 00:00:00'),

构建时间序列

### 时间寸长单位
# 分钟 '1 minute'::interval
# 天 '1 day', 'x day'
# 小时 '1 hour', 'x hour'

SELECT 
	to_char(b, 'YYYY-MM-DD HH24:MI:00') AS time_s 
FROM generate_series (
	to_timestamp('2024-10-21 00:00:00','YYYY-MM-DD HH24:mi:ss'), 
	to_timestamp('2024-10-22 10:54:00', 'YYYY-MM-DD HH24:mi:ss'),
	'1 minute'::interval
) as b;

针对没有时间点的数据需要使用 FULL OUTER JOIN 才能链接起来

# subsql 
SELECT
    -- to_char(to_timestamp(ts) AT TIME ZONE 'UTC','yyyy-MM-dd 00:00:00') stat_time,
    to_char(to_timestamp(ts) AT TIME ZONE 'Asia/Shanghai','yyyy-MM-dd HH24:MI:00') stat_time,
    COUNT(*) "pv_num",
    COUNT(DISTINCT "uuid") "uv_num"
FROM xxx.xxx_table
WHERE 
    ts BETWEEN '1729440000' AND '1729612799'
 GROUP BY stat_time 
 ORDER BY stat_time asc;

# 时间序列sql
SELECT 
    time_s, 
    dds.* 
FROM ( $sql ) AS dds
FULL OUTER JOIN ( 
    SELECT 
        to_char(b, 'YYYY-MM-DD HH24:MI:00') AS time_s 
    FROM generate_series (
        to_timestamp('2024-10-21 00:00:00','YYYY-MM-DD HH24:mi:ss'), 
        to_timestamp('2024-10-22 10:54:00', 'YYYY-MM-DD HH24:mi:ss'),
        '1 minute'::interval) AS b
) tt ON tt.time_s = stat_time
ORDER BY time_s ASC;

---

clickhosue 构建时间序列

时间戳转时间

# 天
# $statTimeFormat = '%Y-%m-%d 00:00:00';

# 小时
# $statTimeFormat = '%Y-%m-%d %H:00:00';

formatDateTime(toDateTime(ts+28800), '%Y-%m-%d %H:%i:00') as time_s

时间格式转时间类型

    toDatae()

构建时间序列

#### 时间格式
# 天
# $timeGenFunc= 'toStartOfDay';

# 小时
# $timeGenFunc = 'toStartOfHour';

SELECT
    toStartOfHour(toDateTime('2024-09-01 00:00:00') + number * 60) AS time_s
FROM
    numbers(ROUND((toUnixTimestamp('2024-09-30 23:00:00') - toUnixTimestamp('2024-09-01 00:00:00')) / 60))
GROUP BY time_s
ORDER BY time_s ASC 

构建时间序列，连表，补充没有时间点的数据, 通过 toDateTime 将时间戳转换出来的时间格式转换为时间类型

# subsql

SELECT
    formatDateTime(toDateTime(ts), '%Y-%m-%d %H:00:00') AS stat_time,
    sum(IF(event_code = 're', 1,0)) "re_num",
FROM pwa_event_point_log
WHERE
    ts BETWEEN '1725120000' AND '1727711999'
GROUP BY stat_time 
ORDER BY stat_time asc;

SELECT 
	time_s, 
	dds.* 
FROM (
   $subsql 
) AS dds
FULL OUTER JOIN (
    SELECT
        toStartOfHour(toDateTime('2024-09-01 00:00:00') + number * 60) AS time_s
    FROM numbers(
        ROUND((toUnixTimestamp('2024-09-30 23:00:00') - toUnixTimestamp('2024-09-01 00:00:00')) / 60))
    GROUP BY time_s
) tt
# 通过 toDateTime 将时间戳转换出来的时间格式转换为时间类型
ON toDateTime(dds.stat_time) = tt.time_s
ORDER BY tt.time_s ASC;