How to fix Incorrect timestamp in DB Connect ?

[pmcfadden91]

Hi, I am trying to figure out how to convert the timestamp in DB Connect back to a MM/DD/YYYY value. I was able to do this with the fieldformat command. However when I added the chart command, it changed back into numerical values (1442358000.000, 1442444400.000, etc..) How do I correct this problem?

| dbquery "PB CSL" limit=1000 "select a.SOURCE_SYSTEM_NAME, trunc(creation_time), count(*) Count from PB_CSL.activity a, PB_CSL.Activitymap b where creation_time < trunc(sysdate) - 0 and creation_time  >= trunc(sysdate) -5 and a.service_id = b.activity_id group by SOURCE_SYSTEM_NAME, trunc(creation_time) order by SOURCE_SYSTEM_NAME, trunc(creation_time)" | rename TRUNC(CREATION_TIME) as Date| fieldformat "Date"=strftime('Date', "%m-%d-%Y") | chart values(COUNT) over SOURCE_SYSTEM_NAME by Date

[gcato · Accepted solution]

HI pmcfadden91,

The fieldformat command does not change the underlying value of Date field. As per the docs...

With the fieldformat command you can use eval expressions to change the format of a field value when the results render. You can change the format without changing the underlying value of the field. Commands later in the search pipeline cannot modify the formatted value.

Trying using an eval instead on fieldformat , e.g. |eval Date = strftime('Date', "%m-%d-%Y")

Hope this helps.