ROracle-dbWriteTable,日期列 [英] ROracle - dbWriteTable, date columns
问题描述
如何使用dbWriteTable
将带有日期列的数据框写入Oracle数据库?我正在尝试:
How can I write data frame with date columns to Oracle database using dbWriteTable
? I am trying:
df <-read.table(header = TRUE, sep = ',', stringsAsFactors = FALSE,
text="
id,product,origination_date,repayment_date
1,A,2012-01-01,2012-02-01
2,A,2012-01-01,2012-04-01
3,A,2012-01-01,NA
4,A,2012-02-01,2012-03-01
")
df$origination_date <- as.Date(df$origination_date, format = '%Y-%d-%m')
df$repayment_date <- as.Date(df$repayment_date, format = '%Y-%d-%m')
names(df) <- toupper(names(df))
dbWriteTable(oraCon, 'TMP', df)
这是数据库中的结果:
1 A 31-DEC-11 11.00.00.000000 PM 01-JAN-12 11.00.00.000000 PM
2 A 31-DEC-11 11.00.00.000000 PM 03-JAN-12 11.00.00.000000 PM
3 A 31-DEC-11 11.00.00.000000 PM
4 A 01-JAN-12 11.00.00.000000 PM 02-JAN-12 11.00.00.000000 PM
我也许可以理解我以时间戳而不是日期结尾,但是却无法理解为什么在31-DEC-11
中我想使用01-JAN-12
.
I can maybe understand that I end up with timestamp and not date, but cannot understand why there is 31-DEC-11
where I wanted 01-JAN-12
.
推荐答案
此问题是由于操作系统和Oracle会话的时区设置所致.您可以按以下步骤更改这些设置:
This problem is due to the timezone settings of both your operating system and your Oracle session. You can change these settings as follows:
Sys.setenv(TZ='CET')
Sys.setenv(ORA_SDTZ='CET')
很显然,您可能必须选择其他时区.我在所有R脚本的开头都包含了这些设置,但是也许有一种方法可以将这些设置设为新的默认设置.
Obviously you may have to choose a different timezone. I include these settings in the beginning of all my R scripts, but there may be a way to make these settings your new default.
有关详细信息,请参见此处在Oracle时区参数上.
See here for more information on the Oracle timezone parameters.
这篇关于ROracle-dbWriteTable,日期列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!