如何将含12小时制时间戳的CSV导入PostgreSQL/BigQuery？

PostgreSQL 解决方案 #

方法：显式时间戳解析 #

由于你的时间戳格式[m/dd/yyyy hh:mi:ss AM/PM]是非标准格式，修改datestyle无法覆盖这种带AM/PM的12小时制解析逻辑，必须用TO_TIMESTAMP函数手动指定格式匹配规则。

步骤示例：

1. 创建临时表存储原始CSV的文本格式时间戳（避免导入失败）：

CREATE TEMP TABLE fitbit_raw (
  timestamp_str TEXT,
  -- 替换为你的其他字段，如 steps INT, calories_burned FLOAT 等
);

2. 用COPY命令导入CSV文件：

COPY fitbit_raw FROM '/本地路径/fitbit_data.csv' WITH (FORMAT csv, HEADER);

3. 创建正式表并转换时间戳：

CREATE TABLE fitbit_data (
  timestamp TIMESTAMP,
  -- 对应其他字段
);

INSERT INTO fitbit_data (timestamp, ...)
SELECT 
  TO_TIMESTAMP(timestamp_str, 'MM/DD/YYYY HH12:MI:SS AM'),
  -- 其他字段直接映射，如 steps, calories_burned
FROM fitbit_raw;

原理 #

TO_TIMESTAMP的第二个参数是格式匹配符：

• MM: 两位月份（自动兼容单月份数字，如1或01）
• DD: 两位日期
• YYYY: 四位年份
• HH12: 12小时制小时数
• MI: 分钟
• SS: 秒
• AM: 匹配上午/下午标识（不区分大小写）

PostgreSQL的datestyle仅控制默认的标准日期格式解析，无法处理这种带AM/PM的自定义格式，因此必须显式指定解析规则。

BigQuery 解决方案 #

方法1：加载时直接指定时间戳格式 #

使用bq命令行工具加载CSV时，通过--timestamp_format参数指定匹配规则：

bq load --source_format=CSV \
--skip_leading_rows=1 \
--schema 'timestamp:TIMESTAMP, steps:INT64, calories_burned:FLOAT64' \
--field_delimiter ',' \
your_project.your_dataset.fitbit_data \
/本地路径/fitbit_data.csv \
--timestamp_format='%m/%d/%Y %I:%M:%S %p'

方法2：加载到临时表后转换 #

如果已经将原始数据加载到带文本时间戳的表中，用PARSE_TIMESTAMP函数转换：

CREATE OR REPLACE TABLE your_project.your_dataset.fitbit_data AS
SELECT
  PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', timestamp_str) AS timestamp,
  steps,
  calories_burned
FROM your_project.your_dataset.fitbit_raw;

原理 #

BigQuery默认仅支持ISO8601标准时间戳格式，--timestamp_format或PARSE_TIMESTAMP的格式符含义：

• %m: 两位月份
• %d: 两位日期
• %Y: 四位年份
• %I: 12小时制小时数
• %M: 分钟
• %S: 秒
• %p: 匹配AM/PM标识

内容的提问来源于stack exchange，提问作者Anikó Mihály