How to generate a time table for use in a dynamic drop-down?

[mdsnmss]

I'm trying to build a search to populate a dynamic drop-down with relative times. I tried working with the time input but due to the format it returns values and lack of restriction on what can be selected it doesn't work for our use. We want to pass the results to a dbxquery that requires the time in a specific format. So far I have:

 | makeresults | eval time=strftime(relative_time(now(), "@mon+mon"),"%F %H:%M:%S:%3Q")

This just snaps forward relative time 1 month and converts it to the desired format. I'm trying to create a table with a snap forward 1 month and then values for the start of each month going X months back. I looked at the gentimes command but it seems it will only go by at most days. Ideally the results returned would be a table containing something like:

2017-01-01 00:00:00:000
2016-12-01 00:00:00:000
2016-11-01 00:00:00:000
2016-10-01 00:00:00:000
2016-09-01 00:00:00:000
etc.

Any suggestions would be appreciated!

[somesoni2 · Accepted solution]

Try like this (will give dates for next 10 months, update mvrange 2nd parameter per your need)

| makeresults | eval range=mvrange(1,11) | table range | mvexpand range | eval time=strftime(relative_time(now(),"@mon+".range."mon"),"%F %H:%M:%S:%3Q") | table time

[mdsnmss]

Added a slight change to adjust to my needs:

| makeresults | eval range=mvrange(1,11) | table range | mvexpand range | eval time=strftime(relative_time(now(),"@mon-".range."mon"),"%F %H:%M:%S:%3Q") | table time

This appears to solve our issue! Thank you for your help!

[mdsnmss]

Whoops copied the same thing back in:

| makeresults | eval range=mvrange(0,13) | table range | mvexpand range | eval time=strftime(relative_time(relative_time(now(), "+1mon@mon"), "@mon-".range."mon"), "%F %H:%M:%S:%3Q") | table time

[sundareshr]

Try this

| makeresults | eval selectedtime=$t.earliest$ | eval dbformattime=if(isnum(selectedtime), stftime(selectedtime, "%F %H:%M:%S:%3Q"), strftime(relative_time(now(), selectedtime), "%F %H:%M:%S:%3Q")

You could also do the formatting in the change event of the timepicker. Like this

<input type=time token="t">
...
<change>
  <eval token="dbxTime">if(isnum($t.earliest$), stftime($t.earliest$, "%F %H:%M:%S:%3Q"), strftime(relative_time(now(), $t.earliest$), "%F %H:%M:%S:%3Q")</eval>
</change>

And just use $dbxTime $ in your query

[mdsnmss]

Thanks for the quick response sundarshr. I've tried getting the change block to work before but haven't had any luck with it doing anything. Here is what I have now:

<input type="time" token="time">
      <label></label>
      <change>
        <eval token="dbxStart">if(isnum($time.earliest$), stftime($time.earliest$, "%F %H:%M:%S:%3Q"), strftime(relative_time(now(), $time.earliest$), "%F %H:%M:%S:%3Q")</eval>
        <eval token="dbxEnd">if(isnum($time.latest$), stftime($time.latest$, "%F %H:%M:%S:%3Q"), strftime(relative_time(now(), $time.latest$), "%F %H:%M:%S:%3Q")</eval>
      </change>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>$dbxStart$ and $dbxEnd$</title>

I put in the tokens in the title to test the conversions but they don't produce any values. I tried to use the tokens in the dbxquery with no luck as well. Is there a way to avoid using the timepicker and have populated values like the ones originally posted in a dropdown?

[mdsnmss]

I noticed there is a typo in the strftime in the code block. Fixing that did not resolve the issue.