TimeStored Blog

[admin]

We’ve added a full listing of string functions here:
http://www.timestored.com/kdb-guides/kdb-string-functions

[admin]

Testing frameworks for kdb+ include:

1. qunit – unit testing similar to junit etc with asserts and integrates well with qStudio
2. k4unit – unit testing driven from a csv file
3. qspec – spec testing framework for kdb+

[admin]

Kdb doesn’t have a “case when then else”, it has something similar called vector conditional.
This link gives details: http://www.timestored.com/kdb-guides/q-quirks#caseStatement

Basically standard SQL:
`SELECT
CASE
WHEN Date1 >= Date2 THEN Date1
ELSE Date2
END AS MostRecentDate`

Kdb Code:

`q)update mostRecentDate:?[date1>=date2;date1;date2] from t
date1 date2 mostRecentDate
————————————
2001.08.05 2007.09.28 2007.09.28
2011.05.21 2007.07.25 2011.05.21
2005.08.10 2008.07.06 2008.07.06
2003.02.16 2008.10.11 2008.10.11
2007.10.05 2004.03.01 2007.10.05`

The vector conditional takes three args ?[a;b;c]
a is a list of booleans
b and c are a list of same typed values
where a was true, the value is taken from b, otherwise it’s taken from c.

[admin]

The .z namespace contains functions for accessing the current data and time. e.g.

q).z.t
09:56:06.145
q).z.d
2013.05.21

The pattern is .z.{letter of data type wanted}

q)value each {x!x}`.z.p`.z.P`.z.t`.z.T`.z.d`.z.D`.z.n`.z.z`.z.Z
.z.p| 2013.05.21D09:56:06.145203000
.z.P| 2013.05.21D10:56:06.145203000
.z.t| 09:56:06.145
.z.T| 10:56:06.145
.z.d| 2013.05.21
.z.D| 2013.05.21
.z.n| 0D09:56:06.145203000
.z.z| 2013.05.21T09:56:06.145
.z.Z| 2013.05.21T10:56:06.145

[admin]

getenv

getenv `LOG_DIR

q)getenv `PATH
"C:\\Program Files (x86)\\NVIDIA Corporation\\PhysX\\Common;C:\\Windows\\syst..

[admin]

From the console you can use ctrl+c to break during a long running command:
http://www.timestored.com/kdb-guides/debugging-kdb#interrupt-q
Make sure to exit debug mode so the server answers client queries.

[admin]

We have added some notes on creating functional select that you may find useful:
http://www.timestored.com/kdb-guides/functional-queries-dynamic-sql

[admin]

You could import printf like functionality using a C shared library (windows DLL, Linuz .so)

[admin]

If you start kdb with the argument -T:

q -T 10

This will set a timeout on client queries of 10 seconds. Note not all queries are polite enough to stop when requested and I think commands at the console may ignore this setting.

You should also be able to modify this setting using
\T (seconds)
from within kdb.

[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

[Author]

Posts