How to extract month from datetime


#1

Hi,

is there a way to extract the month from a datetime attribute as an integer?
So far, I’ve only found out how to get the month as a string via strftime(datetimeattribute, ‘%m’).
I could then cast it to int via the cast() operator but this seems a bit convoluted and I have to specify the whole schema again to cast just one attribute.

Side note: casting the string ‘12’ to int16 yields 12, but casting ‘12’ to int8 gives 49 - what’s going on?


#2

Hi, a couple things:

  1. you should be able to cast via an apply. Like
apply(input_array, new_attribute, int8(old_attribute))

If you like, project it out and then rename it. This is the sort of thing that the R package makes a lot easier.

  1. Thanks for finding the bug! That will need to be fixed:
bio@p4xen7:~$ iquery -aq "create array test <val:string> [i=1:1,1,0]"
Query was executed successfully
bio@p4xen7:~$ iquery -aq "store(build(test, '12'), test)"
{i} val
{1} '12'
bio@p4xen7:~$ iquery -aq "apply(test, val2, int8(val))"
{i} val,val2
{1} '12',49

Workaround:

bio@p4xen7:~$ iquery -aq "apply(test, val2, int8(int32(val)))"
{i} val,val2
{1} '12',12
  1. Back to your original question - in the CE there is no option to do it out-of-the-box. You could of course do it by adding a trivial User-Defined Function. In the enterprise edition there are some timeseries routines for this.

#3

That’s weird - last time I tried directly casting in the apply operator I had the impression it doesn’t work. But I must have made another mistake because now it does :smiley:
Thanks for the int8 workaround, I’ll use that for now.
I think I’ll also try my hand at writing a user defined function later on, just to learn how that works.

Thanks! :smile:


#4

Update. Turns out there is a prototype function in our labs page. Take a look:

github.com/Paradigm4/superfunpack#strpftime