table query

Community Support
1

t1:( ts: time$(); sym: $())

insert[t1;(08:30:00.422;GOOG)]

insert[t1;(08:30:01.522;GOOG)]

insert[t1;(08:30:03.702;GOOG)]

b: select ts: (string[second$ts],":",string[int$mod[ts;1000]]) from t1

q)b

ts        

“08:30:00”

“08:30:01”

“08:30:03”

“:”       

“422”     

“522”     

“702”     

Expected

ts        

“08:30:00:422”

“08:30:01:522”

“08:30:03:702”

What is going on? This should be such a simple construct. Why do I have to spend 2 hours on this?

2

q)t1:( ts: time$(); sym: $())

q)insert[t1;(08:30:00.422;GOOG)]

,0

q)insert[t1;(08:30:01.522;GOOG)]

,1

q)insert[t1;(08:30:03.702;GOOG)]

,2

q)t1

ts           sym

08:30:00.422 GOOG

08:30:01.522 GOOG

08:30:03.702 GOOG

q)select ts: (string[second$ts],'":",'string[int$mod[ts;1000]]) from t1

ts

“08:30:00:422”

“08:30:01:522”

“08:30:03:702”

Thanks Rory

3

You’d be better off string-search-replacing than trying to concatenate.

q)select ssr’[;“.”;“:”]string ts from t1

ts

“08:30:00:422”

“08:30:01:522”

“08:30:03:702”

q)

A slightly quicker solution (given that the position of the dot is fixed) would be

q)select .[;(::;8);:;“:”]string ts from t1

ts

“08:30:00:422”

“08:30:01:522”

“08:30:03:702”

Terry

4

Thanks Terry
Can you please elaborate on the syntax 

select ssr’[;“.”;“:”]string ts from t1

Why do I need to use ’ (each-both) ?

I am assuming ssr is applied to each row and not after all the rows are returned.

E.g.

q)a:08:30:00.422

q)ssr[string a;“.”;“:”]

“08:30:00:422”

q)b:(08:30:00.422 09:10:00.123)

q)ssr’[string b;“.”;“:”]

“08:30:00:422”

“09:10:00:123”

Thanks

5

Correct, ssr has to be applied to each string (which is a list of lists).

To clarify - ssr is the slowest solution but the easiest to read and most succinct. If speed is a concern you should use the dot-indexing approach.

In my syntax below i deliberately projected the ssr function (left the first param blank) to force the column name to be parsed correctly. It could also have been written

select ts:ssr’[string ts;“.”;“:”] from t1

Or

select ts:{ssr[x;“.”;“:”]}each string ts from t1

Etc. Was just trying to shave some characters!

Terry

6

In the case that the dot is not fixed - the following solution from Igor is ~10x quicker that ssr’.

q)show t1:(ts:100000?08:30:00.0)
ts

03:37:12.823
00:53:18.265
05:50:34.147
..

q)c2p:{p:ss[r:raze x;“.”];r[p]:“:”;(0,-1_sums count’)_r}    / http://stackoverflow.com/a/38916772/2039505

q)\t a:select ts:ssr’[string ts;“.”;“:”] from t1
404
q)\t b:select c2p string ts from t1
43
q)a~b
1b

Connor