[R] Treatment of Date ODBC objects in R (RODBC)

[Prof Brian Ripley]

On Mon, 22 Dec 2008, Peter Dalgaard wrote:

> 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....

Well, one is explicitly a way to set a date, and midday seems a good 
choice as most timezones are within +/- 12 hours.  OTOH as.POSIXct is 
using a format with a missing time, and the POSIX convention is that that 
missing times are zero.

The difference is between no time and missing time.


>
>
>> 
>> 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
>
> ______________________________________________
> 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.
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595