FYI

    ‘digits.secs’: controls the maximum number of digits to print when
          formatting time values in seconds.  Valid values are 0...6
          with default 0.  See ‘strftime’.

options(digits.secs=6) is possibly of help, though I wasn't really
following along with the Postgresql side of the conversation.

Jeff


On Wed, Nov 21, 2012 at 2:28 PM, Kent Hoxsey <khoxsey@gmail.com> wrote:

> Ah, now it would appear you are asking simple R questions (rather than
> complicated finance questions) so I am going to point you toward some good
> sources. You will definitely want to do a little spelunking in the help,
> and there's a ton of good stuff (including examples) for working with time
> series on Stack Overflow (esp search for topics tagged with "r" and "xts").
>
> Display of the number of digits is controlled by the options() function,
> read about it with help(options) and set it with something like:
> options(digits=10). You will want to read up on POSIXct(), particularly the
> "Sub-second Accuracy" section.
>
> Your SQL  is most likely coming back into R as a dataframe
> (help(data.frame)) which you can look at using head() or tail() or str().
> In combination with the digits settings, you will likely be able to see all
> of the digits you seek.
>
> On Nov 21, 2012, at 12:22 PM, Michael Lyszczek wrote:
>
> > I am now doing this :
> >
> > select cast(extract(epoch from timestamp) as float) from trades order by
> timestamp;
> >
> > this gives me a list that looks like .....
> > 1353381185.089
> > 1353381185.089
> >  1353381189.96
> >  1353381189.96
> >  1353381189.96
> > 1353381190.353
> > 1353381190.353
> > 1353381190.353
> > 1353381191.139
> > 1353381191.139
> > 1353381191.139
> >  1353381256.04
> > 1353381855.805
> >  1353381864.52
> > 1353384140.324
> >
> > Etc....
> >
> > I try to bring it into R the same way, so new query:
> >
> > NGZ2zoo <- dbGetQuery(con, "select cast(extract(epoch from timestamp) as
> float), instrument_id, trade_size, trade_price, exchange_generated_price,
> trade_id, agressor_side_isbuy from trades where trades.instrument_id =
> (select CAST(max(securities.\"UnderlyingSymbol\") as INT) from securities
> where securities.\"FNYShortName\" = 'iNGZ2')");
> > Summary(NGZ2zoo)
> >   date_part        instrument_id      trade_size     trade_price
> > Min.   :1.35e+09   Min.   :584445   Min.   : 2500   Min.   :3708
> > 1st Qu.:1.35e+09   1st Qu.:584445   1st Qu.: 2500   1st Qu.:3742
> > Median :1.35e+09   Median :584445   Median : 2500   Median :3760
> > Mean   :1.35e+09   Mean   :584445   Mean   : 3070   Mean   :3759
> > 3rd Qu.:1.35e+09   3rd Qu.:584445   3rd Qu.: 2500   3rd Qu.:3776
> > Max.   :1.35e+09   Max.   :584445   Max.   :70000   Max.   :3833
> > exchange_generated_price    trade_id        agressor_side_isbuy
> > Mode :logical            Min.   :65001155   Min.   :-1.000
> > FALSE:2761               1st Qu.:65767044   1st Qu.:-1.000
> > TRUE :474                Median :66313346   Median : 0.000
> > NA's :0                  Mean   :66306294   Mean   :-0.068
> >                          3rd Qu.:66880936   3rd Qu.: 1.000
> >                          Max.   :67377417   Max.   : 1.000
> >
> > as.POSIXct(NGZ2zoo$date_part, origin="1970-01-01")
> > yields:
> > [3199] "2012-11-20 02:12:29 EST" "2012-11-20 00:29:30 EST"
> > [3201] "2012-11-20 02:15:27 EST" "2012-11-20 00:29:31 EST"
> > [3203] "2012-11-20 00:29:31 EST" "2012-11-20 00:29:33 EST"
> > [3205] "2012-11-20 02:15:28 EST" "2012-11-20 02:15:33 EST"
> > [3207] "2012-11-20 02:15:33 EST" "2012-11-20 02:16:11 EST"
> > [3209] "2012-11-20 02:18:21 EST" "2012-11-20 02:18:58 EST"
> > [3211] "2012-11-20 00:29:33 EST" "2012-11-20 00:29:34 EST"
> > [3213] "2012-11-20 00:29:40 EST" "2012-11-20 02:22:04 EST"
> > [3215] "2012-11-20 00:29:43 EST" "2012-11-20 02:22:31 EST"
> > [3217] "2012-11-20 02:22:33 EST" "2012-11-20 02:26:23 EST"
> > [3219] "2012-11-20 02:26:23 EST" "2012-11-20 02:27:53 EST"
> > [3221] "2012-11-20 02:29:30 EST" "2012-11-20 02:29:36 EST"
> > [3223] "2012-11-20 02:29:39 EST" "2012-11-20 02:47:05 EST"
> > [3225] "2012-11-20 02:47:05 EST" "2012-11-20 02:47:05 EST"
> > [3227] "2012-11-20 02:51:25 EST" "2012-11-20 02:53:37 EST"
> > [3229] "2012-11-20 02:53:44 EST" "2012-11-20 02:55:53 EST"
> > [3231] "2012-11-20 02:58:20 EST" "2012-11-20 03:14:16 EST"
> > [3233] "2012-11-20 03:24:15 EST" "2012-11-20 03:24:24 EST"
> > [3235] "2012-11-20 04:02:20 EST"
> >
> >
> > Even if I just do the system time:
> >> as.POSIXct(Sys.time(),  format = "%Y-%m-%d %H:%M:%OS")
> > [1] "2012-11-21 15:07:59 EST"
> >
> >
> > Is it just stored as a float? Or numeric?
> >
> > Any way you can see the actual value, not scientific notation?
> >
> > Thanks so much,
> >
> > Mike
> >
> > -----Original Message-----
> > From: r-sig-finance-bounces@r-project.org [mailto:
> r-sig-finance-bounces@r-project.org] On Behalf Of Kent Hoxsey
> > Sent: Wednesday, November 21, 2012 3:03 PM
> > To: r-sig-finance@r-project.org
> > Subject: Re: [R-SIG-Finance] Millisecond timestamp from PgSql using DBI
> / RPostgreSQL ? Anyway to set up a default timeseries format for the
> timestamp type?
> >
> > After working through a similar set of issues a while back, I discovered
> this very useful web page:
> >
> > http://www.epochconverter.com/
> >
> > Short summary: use your RDBMS to do the conversion to epoch time, and
> hand that number to R. Do not write SQL to write text to parse in R.
> >
> > In my experience, the task of converting from a string representation to
> POSIXct representation in R can be quite, erm, tedious when you're working
> with a lot of data. In contrast, doing the conversion in a source database
> (Oracle for me) is really fast, as are the downstream operations in R.
> >
> > Caveat: I have not attempted to preserve or work with the millis, so you
> might need to do some prototyping to get your conversion right coming out
> Postgres, but that web site includes quite a bit of discussion about the
> topic.
> >
> > HTH.
> > On Nov 21, 2012, at 10:16 AM, Michael Lyszczek wrote:
> >
> >> 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}}
> >>
> >> _______________________________________________
> >> 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.
> >
> >
> >       [[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.
>
> _______________________________________________
> 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

	[[alternative HTML version deleted]]

