CODEWITHSUNDEEP
kdb+
Rhys Martin
Rhys Martin

Reputation: 191

How to parse a string in format "dd/MM/yyyy" to date type in q kdb?

How would you parse a date string in format "dd/MM/yyyy" using q kdb?

It is possible when the month argument is first "MM/dd/yyyy" as follows:

"D"$"1/20/2014"
2014-01-20d

However if day is first "dd/MM/yyyy" 

"D"$"20/1/2014"
0Nd

Upvotes: 3

Views: 4964

Answers (3)

mkst
mkst

Reputation: 674

Note that you do not have to pad with zeroes (tested with 3.3):

q)"." sv ("/" vs "1/20/2014") 2 0 1
"2014.1.20"
q)"D"$ "." sv ("/" vs "1/20/2014") 2 0 1
2014.01.20

In a function:

q)f:{"D"$"."sv("/"vs x)2 0 1}
q)f "1/20/2014"
2014.01.20

If you want a function that can handle both lists and individual dates:

q)g:{"D"$$[10=type x;"."sv("/"vs x)2 0 1;"."sv/:("/"vs/:x)[;2 0 1]]}
q)g "7/20/2014"
2014.07.20
q)g ("1/20/2014";"7/20/2014";"03/20/2014")
2014.01.20 2014.07.20 2014.03.20

... which is a little better than using each:

q)\ts:100000 g ("1/20/2014";"7/20/2014";"03/20/2014")
308 1168
q)\ts:100000 f each ("1/20/2014";"7/20/2014";"03/20/2014")
327 1312

... and quicker than padding/razing:

q)h:{"D"$raze"0"^neg[4 2 2]$(y vs z)iasc`YYYY`MM`DD?x}[`MM`DD`YYYY;"/";]
q)\ts:100000 h each ("1/20/2014";"7/20/2014";"03/20/2014")
615 1312

Upvotes: 3

Thomas Smyth
Thomas Smyth

Reputation: 5644

If you want to avoid changing system variables and have greater control over all possible date formats you can always write a custom date parser such as this:

f:{"D"$raze"0"^neg[4 2 2]$(y vs z)iasc`YYYY`MM`DD?x}

Which takes 3 parameters; date format expected, delimiter and date string. To handle your example it would be set up as follows:

q)f[`MM`DD`YYYY;"/";"1/20/2014"]
2014.01.20

It can also handle more unconventional date formats:

q)f[`MM`YYYY`DD;"p";"1p2014p20"]
2014.01.20

Obviously the above is overkill compared to inbuilt date parsing for your example but it does give a greater degree of flexibility.

Upvotes: 2

Rahul
Rahul

Reputation: 3969

KDB supports parsing of different datetime formats. Check details here: https://code.kx.com/q/ref/casting/#tok

For your case you need to set 'z' option which specifies the format for date parsing.

0 is "mm/dd/yyyy" and 1 is "dd/mm/yyyy".

Details: https://code.kx.com/q/ref/syscmds/#z-date-parsing

This is how you do it for your example:

 q) \z 1
 q) "D"$"20/1/2014"
 q) 2014.01.20

Upvotes: 5

Related Questions