table query

[Hungry_Goat]

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?

[rory_ororke]

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

[TerryLynch]

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

[Hungry_Goat]

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

[TerryLynch]

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

[Connor_Gervin]

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'[x])_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