sql-server – sqlSave：将数据帧时间戳映射到SQL Server时间戳

我遇到两个问题

它将数据框的时间戳映射到浮点数.
2.它创建一个表,但没有数据上传,我收到一个错误.

以下是数据框的示例,mdf：

mdf <- structure(list(run = structure(c(1L,1L,1L),.Label = c("run_00","run_01","run_02","run_03","run_04"),class = "factor"),slot = structure(c(1L,.Label = c("slot 3","slot 4","slot 5","slot 6"),timestamp = structure(c(1320774563,1320774624,1320774686,1320774747,1320774809,1320774871),class = c("POSIXct","POSIXt"),tzone = ""),channel = structure(c(1L,.Label = c("och01","och02","och09","och10"),variable = structure(c(2L,2L,2L),.Label = c("num_blocks","num_collection","num_corr_0","num_corr_1","num_uncorr_srow","post_fec_err_rate","pre_fec_err_rate"),value = c(1,62,124,185,247,309)),.Names = c("run","slot","timestamp","channel","variable","value"),row.names = c(NA,6L),class = "data.frame")

> mdf
     run   slot           timestamp channel       variable value
1 run_00 slot 3 2011-11-08 12:49:23   och01 num_collection     1
2 run_00 slot 3 2011-11-08 12:50:24   och01 num_collection    62
3 run_00 slot 3 2011-11-08 12:51:26   och01 num_collection   124
4 run_00 slot 3 2011-11-08 12:52:27   och01 num_collection   185
5 run_00 slot 3 2011-11-08 12:53:29   och01 num_collection   247
6 run_00 slot 3 2011-11-08 12:54:31   och01 num_collection   309

当我尝试sqlSave到sql server数据库时,会发生什么…

> sqlSave(dbandle,mdf,tablename="mdf")
Error in sqlSave(dbandle,tablename = "mdf") : 
  [RODBC] Failed exec in Update
22018 0 [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

另外,当我查看表的数据类型时,我没有获取时间戳的“datetime”.为什么RODBC会将POSIXct时间表映射到除日期时间以外的任何内容,这是没有意义的.

[rownames] [varchar](255) NULL,[run] [varchar](255) NULL,[slot] [varchar](255) NULL,[timestamp] [float] NULL,[channel] [varchar](255) NULL,[variable] [varchar](255) NULL,[value] [float] NULL

如何解决这个问题？

解决方法

1)Lazy one：发生错误,将创建表,并在数据库中手动将列更改为datetime.下次会工作.

2)正确：使用varTypes

请注意,您的问题可以通过删除不必要的东西而被删除.除此之外,我可能不会在sql服务器中使用列名称时间戳,因为我看到混乱,因为内部时间戳数据类型是完全不同的.

library(RODBC)
mdf = data.frame(timestamp=as.POSIXct(Sys.time()))

varTypes = c(timestamp="datetime")
channel = odbcConnect("test")
sqlSave(channel,rownames=FALSE,append=TRUE,varTypes=varTypes)
close(channel)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!