TimeStored Blog

[admin]

Kdb outline how to import time zone data from java here:
http://code.kx.com/wiki/Cookbook/Timezones

Almost all math operators work on the underlying time data formats. For example if you use xbar on time, you casn easily round time to the nearest 5 minutes.

Functions for handling time include:
.Q.addmonths

Time Handling Settings
\z changes US/UK date format

Variables for returning time in various formats:
.z.n gmt (timespan)
.z.N local (timespan)
.z.p gmt (timestamp)
.z.P local (timestamp)
.z.z gmt time (datetime)
.z.Z local time (datetime)
.z.D .z.d .z.T .z.T
ltime
gtime

[admin]

For reading a pipe delimited file you can use 0:

Here I first use read0 to read the file as a list of strings, to demonstrate it’s the same format you gave:

q)read0 `:source.txt
"name|id|age|height"
"P13141|212314|23|167"
"R3145|212315|34|190"

On the right hand side of 0: We supply the file handle.
On the left is a two item list
“SJII” – denotes the four types for each column – symbol, long, int, int
enlist “|” – means take the pipe as the delimiter and enlist tells kdb there is a header row
like so:

q)("SJII"; enlist "|") 0: `:source.txt
name id age height
------------------------
P13141 212314 23 167
R3145 212315 34 190

[admin]

q code is evaluated right to left. When you say:
select from t where a=1 or b=`c
What kdb is evaluating is:
select from t where a=(1 or (b=`c))
Which reduces to:
select from t where a=1

You can see this from the example below:

q)t:([] a:5?01b; b:5?`c`d)
q)t
a b
---
0 c
1 c
1 d
0 c
0 d
q)select from t where a=1 or b=`c
a b
---
1 c
1 d
q)select from t where a=(1 or b=`c)
a b
---
1 c
1 d

To make kdb behave how you expect place parentheses around a=1 like so:

q)select from t where (a=1) or b=`c
a b
---
0 c
1 c
1 d
0 c

There is a similar issue when using “and”, though mostly in kdb you will want to use comma to separate conditions rather than “and”.

[admin]

nyi = Not Yet Implemented
Currently you can’t flip a keyed table in kdb, either use 0!t or () xkey t to remove the key and then flip the table.


q)t:([a:1 2 3] b:`p`o`i)
q)t
a| b
-| -
1| p
2| o
3| i
q)flip t
'nyi

q)flip 0!t
a| 1 2 3
b| p o i
q)flip () xkey t
a| 1 2 3
b| p o i


[admin]

Mike,

Unfortunately you cannot delete a namespace: http://www.timestored.com/kdb-guides/q-quirks#deleteNamespace

You can delete all the variables contained in a namespace with
delete from `.ns
To delete from the default namespace:
delete from `.

[admin]

Hi Dan,

So here is the short qsql you mentioned for selecting the last entries by a grouping, simple “select by”:


q)t:([] sym:`A`A`B`A`B`C`C`C; ex:`p`p`p`p`o`o`o`o; v:til 8; p:asc 8?100.)
q)t
sym ex v p
-----------------
A p 0 12.53237
A p 1 16.94894
B p 2 26.85287
A p 3 37.97537
B o 4 50.51764
C o 5 56.72789
..
q)select by sym from t
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778
q)select by sym,ex from t
sym ex| v p
------| ----------
A p | 3 37.97537
B o | 4 50.51764
B p | 2 26.85287
C o | 7 88.14778


If we wrote this the long way and considered the parse tree:

q)parse "select last v, last p by sym from t"
?
`t
()
(,`sym)!,`sym
`v`p!((last;`v);(last;`p))


We could create the functional query dynamically.
We could then replace last with first, to dynamically generate and run our query.


q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(last;x)} each c]} [`t; `sym]
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778

q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(first;x)} each c]} [`t; `sym`ex]
sym ex| v p
------| ----------
A p | 0 12.53237
B o | 4 50.51764
B p | 2 26.85287
C o | 5 56.72789


Functional queries sometimes have their uses. Aaron Davies has some notes on them: http://www.q-ist.com/2013/03/my-kdb-user-meeting-presentation.html

[admin]

The length error with xcols is being caused by your table being keyed. There’s no real reason for this other than that xcols currently does not support keyed tables. You can use either ` xkey t or the shortcut 0!t to unkey the table and then use xcols. e.g.

q)t:2!([] date:.z.d+til 3; time:3?.z.t; sym:`p`o`i; price:1 2 3.; size:100*1 2 3)
q)t
date time | sym price size
-----------------------| --------------
2013.04.02 05:19:46.499| p 1 100
2013.04.03 08:06:26.022| o 2 200
2013.04.04 05:40:18.945| i 3 300
q)`size`price xcols t / reproduce same length error
k){(x,f@&~(f:cols y)in x)#y}
'length
#
`size`price`date`time`sym
(+`date`time!(2013.04.02 2013.04.03 2013.04.04;05:19:46.499 08:06:26.022 05:4..
q.q))\

q)0!t
date time sym price size
--------------------------------------
2013.04.02 05:19:46.499 p 1 100
2013.04.03 08:06:26.022 o 2 200
2013.04.04 05:40:18.945 i 3 300

q)`size`price xcols 0!t
size price date time sym
--------------------------------------
100 1 2013.04.02 05:19:46.499 p
200 2 2013.04.03 08:06:26.022 o
300 3 2013.04.04 05:40:18.945 i

[admin]

q code is has no order of precedence, it is interpreted from right to left. e.g.

q)3%2-100+10*neg 11+3
0.07142857
q)3 % (2- (100+ (10*(neg (11+3)))))
0.07142857

[admin]

The assign error occurs when you try to assign a value to a kdb keyword. “type” is a reserved word. Notice your code works when I change that one word.


q)UK_US_ORDERS:([] date:(); time:(); sym:(); size:(); price:(); TYPE:())
q)UK_US_ORDERS
date time sym size price TYPE
-----------------------------
q)


It is possible to workaround this limitation to make your column named type but it would only leave your table extremely hard to query so it is not recommended.

[admin]

Hi Dan,

An alternative method of saving a table is using 0:
filehandle 0: “separator” 0: table

For example:


q)t
sym time price size
------------------------------
kjd 04:37:40.682 88.49717 28
iik 07:54:24.777 66.8001 904
oje 03:09:02.404 76.41439 511
ndo 07:34:51.930 48.54351 139
lkc 02:32:20.516 21.3479 775
all 04:41:36.592 6.323408 353
..
q)1_"," 0: t / convert to comma separated and drop first row
"kjd,04:37:40.682,88.49717,28"
"iik,07:54:24.777,66.8001,904"
"oje,03:09:02.404,76.41439,511"
"ndo,07:34:51.930,48.54351,139"
"lkc,02:32:20.516,21.3479,775"
"all,04:41:36.592,6.323408,353"
"eln,02:16:35.865,75.98419,740"
"bdi,10:33:07.085,6.746338,251"
..
q)`:/temp/a.csv 0: 1_"," 0: t / save to file
`:/temp/a.csv
q)read0 `:/temp/a.csv / check what was written
"kjd,04:37:40.682,88.49717,28"
"iik,07:54:24.777,66.8001,904"
"oje,03:09:02.404,76.41439,511"
"ndo,07:34:51.930,48.54351,139"
"lkc,02:32:20.516,21.3479,775"
"all,04:41:36.592,6.323408,353"
"eln,02:16:35.865,75.98419,740"
"bdi,10:33:07.085,6.746338,251"
..


[admin]

IPC transfer of data is limited to objects under 2GB in size, when an object is too large to transfer you will see a limit error.

http://www.timestored.com/kdb-guides/kdb-database-limits#limit-error

Try pulling the columns separately then join-each like so:


q)a:h "select sym,time from t"; b:h "select price,size from t"; r:a,'b
q)r
sym time price size
------------------------------
kjd 04:37:40.682 88.49717 28
iik 07:54:24.777 66.8001 904
oje 03:09:02.404 76.41439 511
ndo 07:34:51.930 48.54351 139
lkc 02:32:20.516 21.3479 775
all 04:41:36.592 6.323408 353
..


Be careful as if there is an insert between your calls, the join will fail with a length error. The alternative is to pull rows 0-50000, 50000-10000, 100000+ … in separate calls.

[admin]

\P sets the precision of data shown throughout kdb.
The number of decimal places shown for a floating point number can be adjusted as follows:


q)\P
7i
q)1.10123010310312
1.10123
q)\P 15
q)1.10123010310312
1.10123010310312


this controls the output to the web browser and saving csv’s etc.

[admin]

kdb supports the full range of execution controls, while, if, do, exceptions.

Consider the following while loop:

q){ i:0; while[i<5; show "hello ",string i; i+:1] }[]
"hello 0"
"hello 1"
"hello 2"
"hello 3"
"hello 4"


If we wanted to break from it early dependent on a condition we could do:

q){ i:0; while[i<5; show "hello ",string i; if[i~3; :`p]; i+:1] }[]
"hello 0"
"hello 1"
"hello 2"
"hello 3"
`p


The single colon at :`p with nothing on the left is the equivalent of return in most other languages.

Some more details on debugging and exceptions can be found at:
http://www.timestored.com/kdb-guides/debugging-kdb

[admin]

You can use \t to record how long a query takes:

q)trade:`date xasc 100000?([] sym:1000?`RBS`o`i; size:1000?1000; price:1000?10.; date:1000?.z.d)
q)select vwap:size wavg price by date from trade where sym =`RBS
date | vwap
----------| ---------
2000.01.10| 6.852109
2000.01.18| 7.724738
2000.01.22| 7.328888
2000.01.30| 7.99496
2000.02.08| 0.8450724
2000.02.10| 0.276614
..
q)\t select vwap:size wavg price by date from trade where sym =`RBS
7
q)\t select vwap:size wavg price by date from trade where sym =`RBS
4


To get a reliable timing use a do loop to repeat the action a number of times:

q)\t do[100; select vwap:size wavg price by date from trade where sym =`RBS]
238
q)\t do[100; select vwap:size wavg price by date from trade where sym =`RBS]
225


\ts can be used to return both the time taken and the space used in bytes. Alternatively you can use the system format:

q)\ts do[100; select vwap:size wavg price by date from trade where sym =`RBS]
219 1772336
q)system "ts do[100; select vwap:size wavg price by date from trade where sym =`RBS]"
245 1772336


[admin]

Good news qStudio 1.26 already supports both exporting to excel and row numbers on results.

Simply right click on the result to export.

[Author]

Posts