如何将毫秒级的POSIXct / POSIXt时间戳从R写入Postgres表

# 安装并加载所需的R扩展包
install.packages("RPostgreSQL")
library(RPostgreSQL)

# 连接到Postgres数据库
con <- dbConnect(PostgreSQL(),
                 user = "your_username",
                 password = "your_password",
                 host = "your_host",
                 dbname = "your_database")

# 创建表格用于存储时间戳数据
dbExecute(con, "CREATE TABLE timestamp_table (id SERIAL PRIMARY KEY, timestamp TIMESTAMPTZ)")

# 生成示例时间戳数据
timestamps <- Sys.time() + 1:10 # 替换为实际的时间戳数据

# 将R中的时间戳转换为微秒级格式
microsecond_timestamps <- as.integer(timestamps * 1000000)

# 将数据插入到Postgres表中
for (timestamp in microsecond_timestamps) {
  dbExecute(con, paste("INSERT INTO timestamp_table (timestamp) VALUES (",
                      timestamp, ")"))
}

# 关闭数据库连接
dbDisconnect(con)