Hi -I have time-series data (of time and trade size), but because Iimported it from a CSV into KX in it owns table, I can’t control howto format this “10/3/2011 9:30:00 AM” into a time that’s recognized byKX. Ideally, I would cut off the date and just have the time as I’minterested in xbar of size per minute.10/3/2011 9:30:00 AM 20010/3/2011 9:30:00 AM 200010/3/2011 9:30:00 AM 97800Any suggestions here? The timestamps are currently listed as a symThank you!
If you want the time only then one way is
q)z:`$“10/3/2011 9:30:00PM” / timestamp as symbol type
q){(12:00*“P”=first -2#x)+“T”$-2_10_x}string z
21:30:00.000
if you want the full date and time then
q){(“D”$10#x)+(12:00*“P”=first -2#x)+“T”$-2_10_x}string z
2011.10.03T09:30:00.000
btw, having timestamps stored as symbol type is probably something you want to avoid as this increases the memory used for interning strings. When they are initially parsed into the system, either parse them into a timestamp type using something like the above logic, or parse them as a char vector (“C” instead of “S”).
Not sure if I hit discard by accident on my response, but thank thank
you for your help on this Charles!
Having a slight issue with this as it’s giving me a slight error when
I attempt this part
q)z:`$“10/3/2011 9:30:00PM” / timestamp as symbol type
q){(12:00*“P”=3Dfirst -2#x)+“T”$-2_10_x}string z
{(12:00*“P”=3Dfirst -2#x)+“T”$-2_10_x}
'type
+
12:00
09:30:00.000
q))
ah, for older versions you would need
{(12:00:00.000*“P”=first -2#x)+“T”$-2_10_x}
q)meta Date
| c | t f a |
|---|---|
| Date | s |
| q)select from Date |
Date
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:00 AM
10/3/2011 9:30:03 AM
10/3/2011 9:30:05 AM
10/3/2011 9:30:06 AM
10/3/2011 9:30:06 AM
10/3/2011 9:30:06 AM
10/3/2011 9:30:08 AM
10/3/2011 9:30:08 AM
10/3/2011 9:30:08 AM
I apologize what must be more questions (after your more and more
answers :)!) but is there a way I can use
{(12:00:00.000*“P”=3Dfirst -2#x)+“T”$-2_10_x} for an entire column of
data?
I tried an “each”, but that didn’t work either. Thank you for all your
time … so much!
So I made another one called Date2
q)meta Date2
| c | t f a |
|---|---|
| Date | C |
| q)select from Date2 |
Date
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
“10/3/2011 9:30:00 AM”
{(12:00:00.000*“P”=3Dfirst -2#x)+“T”$-2_10_x}Date2
can’t get it to work though, thanks
given symbol type
q)f:{(12:00:00.000*“P”=first -2#x)+“T”$-2_10_x}
q)t:(date:`$(“10/3/2011 9:30:00 AM”;“10/3/2011 9:30:00 AM”))
q)select f each string date from t
date
09:30:00.000
09:30:00.000
q)meta t
c | t f a
----| -----
date| s
and again, but with stored as list of char vectors
q)t:(date:(“10/3/2011 9:30:00 AM”;“10/3/2011 9:30:00 AM”))
q)select f each date from t
date
09:30:00.000
09:30:00.000
q)meta t
c | t f a
----| -----
date| C
This is working perfectly except for the 12PM mark –
11:59:40.000 85900
11:59:45.000 89600
11:59:46.000 10400
11:59:53.000 43300
shifts over to 24:00
24:00:04.000 100
24:00:07.000 50000
24:00:11.000 900
24:00:15.000 6700
24:00:15.000 20000
I’m willing to hack it where all the timestamps of 24:00-24:59 and
just subtract it by 12:00 though to see if that works as I don’t
understand all the intricate parts of the syntax yet :)
sorry about that. I forgot about 12:00am -> 00:00
{(sum 12:00:00.000*(-1*12=`hh$t;“P”=first -2#x))+t:“T”$-2_10_x}
refactored
{(12:00:00.000*(-1*12=`hh$t)+“P”=first -2#x)+t:“T”$-2_10_x}
Thanks Charles, this worked really well! Appreciate all the help.