CODEWITHSUNDEEP
datekdb+
Mrunal Gosar
Mrunal Gosar

Reputation: 4683

how to format date column in kdb

I am newbie to KDB. I have a KDB table which I am querying as:

select[100] from table_name

now this table has got some date columns which have dates stored in this format

yyyy.mm.dd

I wish to query that table and retrieve the date fields in specific format (like mm/dd/yyyy). If this would've been any other RDBMS table this is what i would have done:
select to_date(date_field,'mm/dd/yyyy') from table_name
I need kdb equivalent of above. I've tried my best to go through the kdb docs but unable to find any function / example / syntax to do that. Thanks in advance!

Upvotes: 0

Views: 5151

Answers (4)

Benjaminliupenrose
Benjaminliupenrose

Reputation: 76

now qdate is available for datetime parsing and conversion

Upvotes: 0

nyi
nyi

Reputation: 3229

Check out this GitHub library for datetime formatting. It supports the excel way of formatting date and time. Though it might not be the right fit for formatting a very large number of objects (but if distinct dates are very less then a keyed table and lj can be used for lookup).

q).dtf.format["mm/dd/yyyy"; 2016.09.23]  
"09/23/2016"

q).dtf.format["dd mmmm yyyy"; 2016.09.03]  // another example
"03 September 2016"

Upvotes: 1

lholmes
lholmes

Reputation: 41

As Anton said KDB doesn't have an inbuilt way to specify the date format. However you can extract the components of the date individually and rearrange as you wish.

For the example table t with date column:

q)t
date
----------
2008.02.04
2015.01.02

q)update o:{"0"^"/"sv'flip -2 -2 4$'string`mm`dd`year$\:x}date from t
date       o
-----------------------
2008.02.04 "02/04/2008"
2015.01.02 "01/02/2015"

From right to left inside the function: we extract the month,day and year components with `mm`dd`year$:x before stringing the result. We then pad the month and day components with a null character (-2 -2 4$') before each and add the "/" formatting ("/"sv'flip). Finally the leading nulls are filled with "0" ("0"^).

Upvotes: 4

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

I don't think KDB has built-in date formatting features. The most reliable way is to format date by yourself. For example

t: ([]date: 10?.z.d);
update dateFormatted: {x: "." vs x; x[1],"/",x[2],"/",x[0]} each string date from t

gives

date       dateFormatted
------------------------
2012.07.21 "07/21/2012"
2001.05.11 "05/11/2001"
2008.04.25 "04/25/2008"
....

Or, more efficient way to do the same formatting is

update dateFormatted: "/"sv/:("."vs/:string date)[;1 2 0] from t

Upvotes: 0

Related Questions