0
> str(s)
 POSIXct[1:6630], format: "2011-02-14 09:31:00" "2011-02-14 09:32:00" "2011-02-14 09:33:00" "2011-02-14 09:34:00" ...
> head(s)
[1] "2011-02-14 09:31:00 EST" "2011-02-14 09:32:00 EST" "2011-02-14 09:33:00 EST" "2011-02-14 09:34:00 EST"
[5] "2011-02-14 09:35:00 EST" "2011-02-14 09:36:00 EST"
> 


> head(AMKR)
  date_holding time_holding close
1   2011-02-14     09:31:00  7.66
2   2011-02-14     09:32:00  7.58
3   2011-02-14     09:33:00  7.54
4   2011-02-14     09:34:00  7.54
5   2011-02-14     09:35:00  7.56
6   2011-02-14     09:36:00  7.54

> as.xts(AMKR[,-1:-2], s) -> newAMKR
> head(newAMKR)
                    [,1]
2011-02-14 00:00:00 7.74
2011-02-14 00:01:00 7.74
2011-02-14 00:02:00 7.74
2011-02-14 00:03:00 7.74
2011-02-14 00:04:00 7.74
2011-02-14 00:05:00 7.73

As you can see the time has changed.

The ticker 'AMKR' is extracted from mySQL db located on the same machine.

If anyone can point me in the right direction that would be greatly appreciated.

================================================

Update: There were a couple of problems.

1) The upload of the data into mySQL was done with time format set to %h:%i:%s when it should have been set to %H:%i:%s (note the capital H).

2) I deleted the data in mySQL and re-uploaded using H. Now I get a time of 12:00:00 for a quote occurring at 12pm but it appears as.xts interprets this as 12am and so all quotes that occur between 12:00:00 and 04:00:00 are interpreted to occur in the am.

I wonder why mySQL did not convert the time to the 24-hour clock format as I did use %H in the LOAD DATA query. I wonder if there is a way to force the change in R via as.POSIXct. Investigating.....

codingknob
  • 11,108
  • 25
  • 89
  • 126
  • 1
    You haven't provided enough information to accurately answer the question. Please provide a reproducible example. – Joshua Ulrich May 12 '11 at 03:06
  • I cannot reproduce this behaviour. Did you notice that the values of newAMKR are not the ones in AMKR? I believe there's something in your session that is not what you think it is. – Joris Meys May 12 '11 at 11:24
  • Thank you Joris for pointing that out. That led me to discover that the time format is the root of the problem. I am currently investigating. Thank you for your help. – codingknob May 12 '11 at 18:56
  • I figured out the problem. I need to upload the data into mySQL using the following time format: '%h:%i:%s %p'. I previously left out the *%p*. Also when you use the *%p* you need to use lower case *%h*. – codingknob May 12 '11 at 19:11
  • Thanks Joshua! I just followed your suggestion. – codingknob May 29 '11 at 21:17

1 Answers1

1

I figured out the problem. I need to upload the data into mySQL using the following time format: '%h:%i:%s %p'. I previously left out the %p. Also when you use the %p you need to use lower case %h.

codingknob
  • 11,108
  • 25
  • 89
  • 126