topic Re: Timestamp to date conversion in Getting Data In

Timestamp to date conversion

nc-mvw — Tue, 01 Sep 2020 09:27:20 GMT

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?

ITWhisperer — Tue, 01 Sep 2020 09:35:54 GMT

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")

rnowitzki — Tue, 01 Sep 2020 09:40:34 GMT

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