如何让R中odbc与DBI兼容datetime2并保留高精度？

问题根源 #

核心问题并非ODBC Driver 17不支持datetime2类型，而是R的DBI/odbc包默认会把POSIXct时间对象映射为SQL Server的DATETIME类型（仅支持3位小数精度），而非你需要的DATETIME2(7)。同时，默认连接参数未明确启用高精度时间的映射规则，导致写入时精度被截断。

配置调整步骤 #

1. 写入时强制指定字段类型

调用dbWriteTable时，通过field.types参数显式声明col_time对应SQL Server的datetime2(7)类型，强制使用高精度时间类型写入：

con |> dbWriteTable(
  "test", 
  tbl, 
  append = TRUE,
  field.types = list(col_time = "datetime2(7)")
)

2. 调整ODBC连接参数

在dbConnect时添加datetime_as = 'datetime2'参数，让驱动默认将R的时间对象映射为datetime2类型，避免后续操作重复指定类型：

con <- odbc::odbc() |> 
  DBI::dbConnect(
    Driver = "ODBC Driver 17 for SQL Server",
    Server = "你的服务器地址",
    Database = "你的数据库名",
    UID = "用户名",
    PWD = "密码",
    datetime_as = 'datetime2'  # 启用高精度时间映射
  )

3. 确保R时间对象的精度

部分系统（如Windows）中lubridate::now()默认仅保留毫秒精度，可手动构造含7位小数的时间对象测试：

# 构造高精度时间实例
ts <- as.POSIXct("2024-01-01 12:34:56.1234567", format = "%Y-%m-%d %H:%M:%OS7")
tbl <- tibble::tibble(col_time = ts)

验证精度 #

写入后可通过DBI查询确认精度是否保留：

result <- con |> DBI::dbGetQuery("SELECT col_time FROM test WHERE col_time = ?", params = list(ts))
print(result$col_time, digits = 10)

内容的提问来源于stack exchange，提问作者Tobias