SQL如何转换字符串时间戳并筛选近12个月Stripe数据

核心实现逻辑 #

Stripe返回的start_date字段为字符串格式的秒级Unix时间戳，整体处理分三步完成：

• 先将字符串类型的时间戳显式转为整数/长整数数值类型，避免隐式转换带来的不可预期错误
• 调用对应数据库的时间戳转换函数，将数值型Unix时间戳转为数据库原生标准时间格式
• 基于转换后的标准时间字段，筛选时间落在当前时间往前推12个月区间内的记录

注意：Stripe返回的时间戳为秒级（长度10位），不要误按毫秒级（长度13位）做乘除1000的处理，否则会得到完全错误的时间结果。

如果表中存在非数字格式的脏数据，建议转换前增加格式校验，过滤不符合10位数字规则的无效start_date值，避免类型转换报错。

不同数据库的具体实现代码 #

MySQL / MariaDB #

使用CAST将字符串转为无符号整数，再通过FROM_UNIXTIME函数转为标准时间，搭配DATE_SUB计算12个月前的时间节点：

-- 单字段转换效果预览
SELECT FROM_UNIXTIME(CAST(start_date AS UNSIGNED)) AS standard_start_time
FROM your_stripe_data_table;

-- 筛选最近12个月数据的完整查询
SELECT *
FROM your_stripe_data_table
-- 可选：过滤无效脏数据
WHERE start_date REGEXP '^[0-9]{10}$'
  AND FROM_UNIXTIME(CAST(start_date AS UNSIGNED)) >= DATE_SUB(NOW(), INTERVAL 12 MONTH);

PostgreSQL #

使用::BIGINT做类型强转，通过to_timestamp函数转成带时区的标准时间戳：

-- 单字段转换效果预览
SELECT to_timestamp(start_date::BIGINT) AS standard_start_time
FROM your_stripe_data_table;

-- 筛选最近12个月数据的完整查询
SELECT *
FROM your_stripe_data_table
-- 可选：过滤无效脏数据
WHERE start_date ~ '^[0-9]{10}$'
  AND to_timestamp(start_date::BIGINT) >= NOW() - INTERVAL '12 months';

BigQuery #

使用CAST转为INT64类型，通过TIMESTAMP_SECONDS函数直接解析秒级时间戳：

-- 单字段转换效果预览
SELECT TIMESTAMP_SECONDS(CAST(start_date AS INT64)) AS standard_start_time
FROM `your_project.your_dataset.your_stripe_data_table`;

-- 筛选最近12个月数据的完整查询
SELECT *
FROM `your_project.your_dataset.your_stripe_data_table`
-- 可选：过滤无效脏数据
WHERE REGEXP_CONTAINS(start_date, r'^[0-9]{10}$')
  AND TIMESTAMP_SECONDS(CAST(start_date AS INT64)) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 MONTH);

Snowflake #

使用TO_TIMESTAMP函数直接接收整数类型的秒级时间戳做转换，通过DATEADD计算时间区间：

-- 单字段转换效果预览
SELECT TO_TIMESTAMP(CAST(start_date AS BIGINT)) AS standard_start_time
FROM your_stripe_data_table;

-- 筛选最近12个月数据的完整查询
SELECT *
FROM your_stripe_data_table
-- 可选：过滤无效脏数据
WHERE RLIKE(start_date, '^[0-9]{10}$')
  AND TO_TIMESTAMP(CAST(start_date AS BIGINT)) >= DATEADD('month', -12, CURRENT_TIMESTAMP());

内容的提问来源于stack exchange，提问作者Osman Goni Sufy