Timestamp to date conversion

[nc-mvw]

I struggle with converting a time stamp into a date. In my data EMPTY_DATE looks like this:

2020-08-27 00:00:00.0

I have tried the following:

| convert timeformat="%m/%d/%Y" ctime(EMPTY_DATE) AS date

...and this:

|eval date=strftime(EMPTY_DATE, "%m/%d/%Y")

...and this:
| eval time=strptime(EMPTY_DATE,"%Y%m%dT%H:%M:%S.%Q") | convert timeformat="%d%m%Y" ctime(time) as date |

All of the above returns empty columns. I don't know if it's because it doesn't recognize my time stamp or something else?

[rnowitzki · Accepted solution]

Hi @nc-mvw,

Using this, you get the time as epoch and assigning the date out of time to the date field:

| eval time=strptime(EMPTY_DATE,"%Y-%m-%d %H:%M:%S.%Q")
| eval date = strftime(time,"%Y-%m-%d")

BR
Ralph

[ITWhisperer]

You need to use strptime to parse the string to retrieve the time value, providing the format so:

eval time=strptime(EMPTY_DATE,"%Y-%m-%d %H:%M:%S.%Q")