Hi,

I am new to R and have been really having a great time dealing with millisecond timestamps in financial data.

I can get them to work in R using read.csv() then then converting the time stamp column using  as.POSIXct(DATA$TIMESTAMP, format = "%Y-%m-%d %H:%M:%OS");

However, when I use DBI and PgSql to query a table for ticks / bbo I have no idea how to specify not to drop off the trailing millisecond.
<code>

options(digs.secs = 3);

library("DBI")
library("RPostgreSQL")

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="market_data", host="host", user="user", password="password")
NGZ2 <- dbGetQuery(con, "select * from trades blah blah blah");

</code>

Summary(NGZ2) yields:
> summary(NGZ2zoo)
 instrument_id    table_trade_id      trade_size     trade_price
 Min.   :584445   Min.   :2786130   Min.   : 2500   Min.   :3708
 1st Qu.:584445   1st Qu.:2790335   1st Qu.: 2500   1st Qu.:3742
 Median :584445   Median :2795493   Median : 2500   Median :3760
 Mean   :584445   Mean   :2795617   Mean   : 3070   Mean   :3759
 3rd Qu.:584445   3rd Qu.:2800727   3rd Qu.: 2500   3rd Qu.:3776
 Max.   :584445   Max.   :2806760   Max.   :70000   Max.   :3833
 exchange_generated_price    trade_id          timestamp
 Mode :logical            Min.   :65001155   Min.   :2012-11-19 00:49:07
 FALSE:2761               1st Qu.:65767044   1st Qu.:2012-11-19 14:25:28
 TRUE :474                Median :66313346   Median :2012-11-19 16:25:25
 NA's :0                  Mean   :66306294   Mean   :2012-11-19 16:23:35
                          3rd Qu.:66880936   3rd Qu.:2012-11-19 18:50:35
                          Max.   :67377417   Max.   :2012-11-19 23:02:20
 agressor_side_isbuy
 Min.   :-1.00000
 1st Qu.:-1.00000
 Median : 0.00000
 Mean   :-0.06801
 3rd Qu.: 1.00000
 Max.   : 1.00000


Meanwhile using a read.csv() for a similar dataset yields:


NGX2  <- read.csv("iNGCal1310-12-2012.csv", header=TRUE)



> summary(NGX2)

                   TIMESTAMP         BID_SIZE          BID

 2012-10-22 16:04:19.561:    12   Min.   : 2500   Min.   :3.795

 2012-10-22 13:21:33.533:    11   1st Qu.: 2500   1st Qu.:3.911

 2012-10-22 13:54:06.927:    11   Median : 2500   Median :3.927

 2012-10-22 14:44:43.331:    11   Mean   : 4356   Mean   :3.929

 2012-10-22 15:50:03.755:    11   3rd Qu.: 5000   3rd Qu.:3.943

 2012-10-22 16:09:08.757:    11   Max.   :15000   Max.   :3.989

 (Other)                :119621

      ASK           ASK_SIZE

 Min.   :3.904   Min.   : 2500

 1st Qu.:3.919   1st Qu.: 2500

 Median :3.935   Median : 5000

 Mean   :3.938   Mean   : 4667

 3rd Qu.:3.950   3rd Qu.: 7500

 Max.   :4.007   Max.   :15000



After processing with as.PosixCT:



> NGX2$TS <-as.POSIXct(NGX2$TIMESTAMP, format = "%Y-%m-%d %H:%M:%OS");

> summary(NGX2)

                   TIMESTAMP         BID_SIZE          BID

 2012-10-22 16:04:19.561:    12   Min.   : 2500   Min.   :3.795

 2012-10-22 13:21:33.533:    11   1st Qu.: 2500   1st Qu.:3.911

 2012-10-22 13:54:06.927:    11   Median : 2500   Median :3.927

 2012-10-22 14:44:43.331:    11   Mean   : 4356   Mean   :3.929

 2012-10-22 15:50:03.755:    11   3rd Qu.: 5000   3rd Qu.:3.943

 2012-10-22 16:09:08.757:    11   Max.   :15000   Max.   :3.989

 (Other)                :119621

      ASK           ASK_SIZE           TS

 Min.   :3.904   Min.   : 2500   Min.   :2012-10-22 00:00:00

 1st Qu.:3.919   1st Qu.: 2500   1st Qu.:2012-10-22 13:59:36

 Median :3.935   Median : 5000   Median :2012-10-22 14:56:57

 Mean   :3.938   Mean   : 4667   Mean   :2012-10-22 14:52:21

 3rd Qu.:3.950   3rd Qu.: 7500   3rd Qu.:2012-10-22 16:04:16

 Max.   :4.007   Max.   :15000   Max.   :2012-10-22 16:52:08




Is there any way to specify date format for a dbGetQuery () ?


Straight from SQL:
instrument_id |         timestamp          | best_bid_size | best_bid | best_offer | best_offer_size | data_id
---------------+----------------------------+---------------+----------+------------+-----------------+----------
        592802 | 2012-11-19 13:39:52.855-05 |          5000 |    3.864 |      3.872 |            7500 | 17632624
       1461673 | 2012-11-19 13:39:52.855-05 |          5000 |    -0.26 |     -0.237 |            2500 | 17632625
        592790 | 2012-11-19 13:39:52.855-05 |          5000 |    3.951 |       3.96 |            2500 | 17632626
        595724 | 2012-11-19 13:39:52.855-05 |          7500 |   -0.404 |      -0.37 |            2500 | 17632627
        592792 | 2012-11-19 13:39:52.855-05 |          7500 |    3.897 |        3.9 |           10000 | 17632628
        592796 | 2012-11-19 13:39:52.855-05 |          2500 |    3.865 |      3.868 |           10000 | 17632629
        592792 | 2012-11-19 13:39:52.855-05 |         10000 |    3.897 |        3.9 |           10000 | 17632630
        592796 | 2012-11-19 13:39:52.855-05 |          5000 |    3.865 |      3.868 |           10000 | 17632631
        592787 | 2012-11-19 13:39:52.856-05 |         10000 |    3.837 |      3.842 |           10000 | 17632632
        592792 | 2012-11-19 13:39:52.856-05 |          2500 |    3.898 |        3.9 |           10000 | 17632633
        592793 | 2012-11-19 13:39:52.856-05 |         10000 |    3.887 |      3.891 |           10000 | 17632634
        592796 | 2012-11-19 13:39:52.856-05 |          7500 |    3.865 |      3.868 |           10000 | 17632635
        592787 | 2012-11-19 13:39:52.856-05 |         12500 |    3.837 |      3.842 |           10000 | 17632636
        592792 | 2012-11-19 13:39:52.856-05 |          5000 |    3.898 |        3.9 |           10000 | 17632637
        592793 | 2012-11-19 13:39:52.856-05 |         12500 |    3.887 |      3.891 |           10000 | 17632638
        592796 | 2012-11-19 13:39:52.856-05 |         10000 |    3.865 |      3.868 |           10000 | 17632639
        592787 | 2012-11-19 13:39:52.856-05 |          2500 |    3.838 |      3.842 |           10000 | 17632640


**********************************************************************
This e-mail is confidential and for the individual or entity named above and may contain privileged information. If you are not the intended recipient, please notify us immediately by return e-mail or telephone and destroy this message. This communication is for informational purp...{{dropped:22}}

