Hi Samuel,

Your example, while indeed containing code, isn't reproducible.  More
problematic though is you haven't distilled it to just the package in
question (since you have your wrapper), and this is just flatly the wrong
list.

Try R-SIG-DB, emailing a clean example to the maintainer (which I have heard
is bad for that package), or posting on R-help.  But as it stands it has
zero to do with finance and this list.

Best,
Jeff

On Thu, Jul 28, 2011 at 10:07 AM, <Samuel.Meichtry@bkw-fmb.ch> wrote:

> Hi there,
>
> I use R since several years and have never seen this before.
>
> When I try to fetch hourly data from a database the hours get lost!
>
> 1.      I have used the same function with an older PC and an older
> R-Version and it worked before and now it doesn't work anymore!
> 2.      Using the same function with a slightly adapted sql  still works
> (meaning that the hours are shown, see below)
> 3.      The Sql-Statement from the suspicious function on the database
> shows the hours, but not in R.
> 4.      It does not matter if I use RStudio, R.exe, RGui
>
> Do I have problems with the RODBC-package? Should I get an earlier
> R-Version?
>
> Does somebody has an idea where this is coming from?
>
>
> B.R.
> Samuel
>
> P.S.:
>
>
> RESULT:
> > head(GetTimeSerie(10400,20005,1,StartDate="2000-01-01"))
>   TimeStamp Value
> 1 2006-01-01 35.67
> 2 2006-01-01 30.16
> 3 2006-01-01 30.15
> 4 2006-01-01 30.14
> 5 2006-01-01 30.13
> 6 2006-01-01 30.12
> > head(GetTimeSerieWeekday(10400,20005,1,StartDate="2000-01-01"))
>            TimeStamp Value
> 1 2006-01-02 00:00:00 35.63
> 2 2006-01-02 01:00:00 35.60
> 3 2006-01-02 02:00:00 33.82
> 4 2006-01-02 03:00:00 34.62
> 5 2006-01-02 04:00:00 32.55
> 6 2006-01-02 05:00:00 35.50
> >
>
>
>
> #* Function which does not work any more !!!
> GetTimeSerie <-
> function(TimeSerie,Product,Granularity=1,Interpolation=0,AggFunction=1,AggComplete=1,StartDate=HistStartDate,EndDate=as.character(Sys.Date())){
>    if(!("RODBC" %in% loadedNamespaces())){library(RODBC)}
>    channel <- odbcConnect(dsn = "DbAnalyseHet", uid =
> Sys.getenv("username"), case = "nochange")
>    sqlRaw <- " select *
>                from
> [DBAnalyseHET].[dbo].NormalizeDataBounded(%TimeSerie%,%Product%,%Granularity%,%Interpolation%,%AggFunction%,%AggComplete%,'%StartDate%','%EndDate%')"
>    sqlRaw<-gsub("%TimeSerie%",TimeSerie,sqlRaw)
>    sqlRaw<-gsub("%Product%",Product,sqlRaw)
>    sqlRaw<-gsub("%Granularity%",Granularity,sqlRaw)        #1/4h=-2,
> 1/2h=-1, original=0, 1h=1, 1d=2, 1m=3, 1q=4, 2q=5, 1y=6
>    sqlRaw<-gsub("%Interpolation%",Interpolation,sqlRaw)    #None=-1,
> Constant=0, Linear=1
>    sqlRaw<-gsub("%AggFunction%",AggFunction,sqlRaw)        #None=0, Avg=1,
> Min=2, Max=3, Sum=4, Count=5, QuantileXX=1XX
>    sqlRaw<-gsub("%AggComplete%",AggComplete,sqlRaw)        #not complete=0,
> complete=1
>    sqlRaw<-gsub("%StartDate%",StartDate,sqlRaw)
>    sqlRaw<-gsub("%EndDate%",EndDate,sqlRaw)
>    result<-sqlQuery(channel,sqlRaw)
>    odbcClose(channel)
>    print(sqlRaw)
>    return(result)
> }
>
> #* Function which is almost the same and works !!!
> GetTimeSerieWeekday <-
> function(TimeSerie,Product,Granularity=1,Interpolation=0,AggFunction=1,AggComplete=1,StartDate=HistStartDate,EndDate=as.character(Sys.Date())){
>    if(!("RODBC" %in% loadedNamespaces())){library(RODBC)}
>    channel <- odbcConnect(dsn = "DbAnalyseHet", uid =
> Sys.getenv("username"), case = "nochange")
>    sqlRaw <- " select *
>                from
> [DBAnalyseHET].[dbo].NormalizeDataBounded(%TimeSerie%,%Product%,%Granularity%,%Interpolation%,%AggFunction%,%AggComplete%,'%StartDate%','%EndDate%')
> where Datepart(weekday,timestamp) in (2,3,4,5,6)"
>    sqlRaw<-gsub("%TimeSerie%",TimeSerie,sqlRaw)
>    sqlRaw<-gsub("%Product%",Product,sqlRaw)
>    sqlRaw<-gsub("%Granularity%",Granularity,sqlRaw)        #1/4h=-2,
> 1/2h=-1, original=0, 1h=1, 1d=2, 1m=3, 1q=4, 2q=5, 1y=6
>    sqlRaw<-gsub("%Interpolation%",Interpolation,sqlRaw)    #None=-1,
> Constant=0, Linear=1
>    sqlRaw<-gsub("%AggFunction%",AggFunction,sqlRaw)        #None=0, Avg=1,
> Min=2, Max=3, Sum=4, Count=5, QuantileXX=1XX
>    sqlRaw<-gsub("%AggComplete%",AggComplete,sqlRaw)        #not complete=0,
> complete=1
>    sqlRaw<-gsub("%StartDate%",StartDate,sqlRaw)
>    sqlRaw<-gsub("%EndDate%",EndDate,sqlRaw)
>    result<-sqlQuery(channel,sqlRaw)
>    odbcClose(channel)
>    print(sqlRaw)
>    return(result)
> }
>
>
>
>
>        [[alternative HTML version deleted]]
>
> _______________________________________________
> R-SIG-Finance@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions
> should go.
>



-- 
Jeffrey Ryan
jeffrey.ryan@lemnica.com

www.lemnica.com
www.esotericR.com

	[[alternative HTML version deleted]]

