Trouble with date conversion

[mistydennis]

I need to create a field (30days) with a date 30 days from the date in a given field (pubdate). I believe I have that part working, but can't seem to get the date to convert to the format I want.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=epochtime + 2592000
|convert ctime(30days)
|table pubdate, 30days

Which produces:

pubdate	30days
2022-09-30	10/30/2022 00:00:00.000000
2021-08-31	09/30/2021 00:00:00.000000

All I want to do is to format the 30days date field the same was as pubdate - "%Y-%m-%d". Everything I'm trying is producing an error.

[richgalloway · Accepted solution]

The convert command has options to control the format of the time string, but I prefer to use strftime.

|makeresults
|eval pubdate="2022-09-30,2021-08-31"
|makemv delim="," pubdate
|mvexpand pubdate
|eval epochtime=strptime(pubdate, "%Y-%m-%d") 
|eval 30days=relative_time(epochtime, "+30d")
|eval 30days=strftime('30days', "%Y-%m-%d")
|table pubdate, 30days

I also used relative_time() to compute the new timestamp.

I didn't do it in my example, but try to avoid field names beginning with digits as they can confuse the parser.  For instance, the strftime call failed until I used single quotes around the first argument.

[mistydennis]

Thank you, @richgalloway ! I see you have answered MANY questions regarding date and timestamps here - maybe it's time for you to just write a book and help us all out 🙂

[isoutamo]

In most cases I also prefer to use strftime, but in adhoc  queries and especially with mv fields, it’s easier way to convert all values.

[isoutamo]

Hi

have you tried 

...
| convert timeformat="%Y-%m-%d" ctimes(30days)

r. Ismo