RODBC & POSIX & Daylight Saving blues

Dear All,

The recent improvement in RODBC to recognize datetimes in tables has
exposed my ongoing confusion.

All my data are obtained from a satellite system (Argos) which tags events
in the GMT time zone.  Daylight saving is ignored.  To my way of thinking
this means that

   1.  twelve-o-clock means halfway through the day regardless of season, and
   2.  the difftime of any two dates where the time is set to
twelve-o-clock should be an integer, regardless of which season each of the
dates are in.

I illustrate my confusion with a two-line table in an Access 2000 database
table where the single field called 'theDate' contains the two values:

30/07/04 12:00:00
30/11/04 12:00:00

then I bring the datetimes into R with the following code:

 > library (RODBC)
 > theChannel <- odbcConnect("phonetagcopy", "", "")
 > pp <- sqlFetch(theChannel, "DateTest")
 > odbcClose(theChannel)
 > pp$theDate

[1] "2004-07-30 12:00:00 GMT Daylight Time"
[2] "2004-11-30 12:00:00 GMT Standard Time"

 >
 > unclass(pp$theDate) / 86400

[1] 12629.46 12752.50
attr(,"tzone")
[1] ""

 >
 > difftime (pp$theDate[1], pp$theDate[2])

Time difference of -123.0417 days

It appears that sqlFetch has (in this case wrongly) assumed that my
datetimes are corrected for Daylight Saving.  How can I persuade it to
accept that all my datetimes are in straight GMT?

All date-time conversions in R by default occurs in the current timezone.
If you set it to GMT for the duration of the sqlFetch call, it should do
as you intended (but had not told R, which is not clairvoyant).

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 272860 (secr)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Prof Brian Ripley <ripley <at> stats.ox.ac.uk> writes:

:

: 
: > Dear All,
: >
: > The recent improvement in RODBC to recognize datetimes in tables has
: > exposed my ongoing confusion.
: >
: > All my data are obtained from a satellite system (Argos) which tags events
: > in the GMT time zone.  Daylight saving is ignored.  To my way of thinking
: > this means that
: >
: >    1.  twelve-o-clock means halfway through the day regardless of season, 
and
: >    2.  the difftime of any two dates where the time is set to
: > twelve-o-clock should be an integer, regardless of which season each of the
: > dates are in.
: >
: > I illustrate my confusion with a two-line table in an Access 2000 database
: > table where the single field called 'theDate' contains the two values:
: >
: > 30/07/04 12:00:00
: > 30/11/04 12:00:00
: >
: > then I bring the datetimes into R with the following code:
: >
: >  > library (RODBC)
: >  > theChannel <- odbcConnect("phonetagcopy", "", "")
: >  > pp <- sqlFetch(theChannel, "DateTest")
: >  > odbcClose(theChannel)
: >  > pp$theDate
: > [1] "2004-07-30 12:00:00 GMT Daylight Time"
: > [2] "2004-11-30 12:00:00 GMT Standard Time"
: >  >
: >  > unclass(pp$theDate) / 86400
: > [1] 12629.46 12752.50
: > attr(,"tzone")
: > [1] ""
: >  >
: >  > difftime (pp$theDate[1], pp$theDate[2])
: > Time difference of -123.0417 days
: >
: > It appears that sqlFetch has (in this case wrongly) assumed that my
: > datetimes are corrected for Daylight Saving.  How can I persuade it to
: > accept that all my datetimes are in straight GMT?
: 
: All date-time conversions in R by default occurs in the current timezone.
: If you set it to GMT for the duration of the sqlFetch call, it should do
: as you intended (but had not told R, which is not clairvoyant).
: 

On Windows you have to set the whole computer to GMT which has
the potential to interfere with other programs so I don't think 
that that can be the final solution.  I believe that on UNIX it
would be better since its per process there but even then
it has the potential to disrupt the current R session.

If the data is stored in a table using a type that does not have
time zones then it should be mapped into a datetime class in R that 
also has no time zones.   It would be safer to convert it to chron
or to create some new datetime class that matches the data's type
or not convert it and leave it to the user.

Gabor Grothendieck <ggrothendieck <at> myway.com> writes:

: 
: Prof Brian Ripley <ripley <at> stats.ox.ac.uk> writes:
: 

: : If you set it to GMT for the duration of the sqlFetch call, it should do
: : as you intended (but had not told R, which is not clairvoyant).
: : 
: 
: On Windows you have to set the whole computer to GMT which has

Paul Roebuck pointed out to me offlist that this can be done
per process in Windows too so I was wrong on this point.

   ... start up Windows console ...
   cd \Program Files\rw2001beta
   set TZ=GMT
   bin\Rgui

In R,

   Sys.time() # displays date and time in GMT time zone


I also tried doing this from within R but was unsuccessful:

   R> Sys.time()
   [1] "2004-11-11 11:37:53 Eastern Standard Time"
   R> Sys.putenv(TZ = "GMT")
   R> Sys.time()  # wanted GMT result but did not get it
   [1] "2004-11-11 11:38:08 Eastern Standard Time"
   R> R.version.string  # Windows XP
   [1] "R version 2.0.1, 2004-11-04"