Dear all,
Retrieving an Oracle "Date" data type by means of RODBC (version
1.2-4) I get different classes in R depending on which operating
system I am in:
On MacOSX I get "Date" class
On Windows I get " "POSIXt" "POSIXct" class
The problem is material, as converting the "POSIXt"
"POSIXct" object
with as.Date() returns one day less ("2008-12-17 00:00:00 CET" is
returned as "2008-12-16").
I have 2 related questions:
1. Is there a way to control the conversion used by RODBC for types
"Date"? or is this controlled by the ODBC Driver (in my case the
Oracle driver in Windows and Actual on Mac OS X)?
2. What is the trick to get as.Date() to return the _intended_ date
(the date that the OS X environment "correctly" reads)?
Many thanks in advance for any guidance.
Best regards,
Ivan
I tend to avoid the issue by asking Oracle for a character string representation
of the date. I use sql like this:
to_char( thedatefield, 'yyyymmdd' ) as thedate
Then in R:
d <- as.Date( as.character( thedate, '%Y%m%d') )
Hope this helps,
Avram
------Original Message------
From: Ivan Alves
Sender: r-help-bounces at r-project.org
To: r-help at r-project.org
Sent: Dec 22, 2008 8:40 AM
Subject: [R] Treatment of Date ODBC objects in R (RODBC)
Dear all,
Retrieving an Oracle "Date" data type by means of RODBC (version
1.2-4) I get different classes in R depending on which operating
system I am in:
On MacOSX I get "Date" class
On Windows I get " "POSIXt" "POSIXct" class
The problem is material, as converting the "POSIXt"
"POSIXct" object
with as.Date() returns one day less ("2008-12-17 00:00:00 CET" is
returned as "2008-12-16").
I have 2 related questions:
1. Is there a way to control the conversion used by RODBC for types
"Date"? or is this controlled by the ODBC Driver (in my case the
Oracle driver in Windows and Actual on Mac OS X)?
2. What is the trick to get as.Date() to return the _intended_ date
(the date that the OS X environment "correctly" reads)?
Many thanks in advance for any guidance.
Best regards,
Ivan
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Sent from my Verizon Wireless BlackBerry
Ivan Alves wrote:> Dear all, > > Retrieving an Oracle "Date" data type by means of RODBC (version 1.2-4) > I get different classes in R depending on which operating system I am in: > > On MacOSX I get "Date" class > On Windows I get " "POSIXt" "POSIXct" class > > The problem is material, as converting the "POSIXt" "POSIXct" object > with as.Date() returns one day less ("2008-12-17 00:00:00 CET" is > returned as "2008-12-16").This is in a sense correct since CET is one hour ahead of GMT (two hours in Summer). What is a bit puzzling is that > ISOdate(2008,12,24) [1] "2008-12-24 12:00:00 GMT" > class(ISOdate(2008,12,24)) [1] "POSIXt" "POSIXct" > as.POSIXct("2008-12-24") [1] "2008-12-24 CET" > as.POSIXct("2008-12-24")+1 [1] "2008-12-24 00:00:01 CET" I.e. we have two ways of converting a timeless date to POSIXct, and they differ in noon/midnight, and in whether local timezone matters or not. I believe Brian did this, and he usually does things for a reason....> > I have 2 related questions: > > 1. Is there a way to control the conversion used by RODBC for types > "Date"? or is this controlled by the ODBC Driver (in my case the Oracle > driver in Windows and Actual on Mac OS X)? > > 2. What is the trick to get as.Date() to return the _intended_ date (the > date that the OS X environment "correctly" reads)?Add 12 hours, maybe? (43200 seconds) Or play around with the timezone, but that seems painful. -- O__ ---- Peter Dalgaard ?ster Farimagsgade 5, Entr.B c/ /'_ --- Dept. of Biostatistics PO Box 2099, 1014 Cph. K (*) \(*) -- University of Copenhagen Denmark Ph: (+45) 35327918 ~~~~~~~~~~ - (p.dalgaard at biostat.ku.dk) FAX: (+45) 35327907