Bad timestamps in DB Connect query results

[Karunamon]

I am running a query via a created dashboard on one of my production databases. I defined this in the DB Connect app, and I am not actually indexing the contents of the database.

One of my searches on this dashboard is:

| dbquery Prod1 "select * from source where guid='$GUID$'"

This query works and returns valid data for all fields except any time stamps. Instead of a readable timestamp, I just get a string of numbers like: 1349484107.000 (I assume this is an epoch date)

The fields in question on the database are of type DATETIME and some TIMESTAMP, and when running this query with the standard command line MySQL, proper dates are returned.

How do I correct the display format of these fields without indexing the data?

[cramasta · Accepted solution]

you can use the convert command to convert epoch to a human readabletimestamp

| dbquery Prod1 "select * from source where guid='$GUID$'" | convert timeformat="%Y:%m:%d% H:%M:%S" ctime(FIELD_WITH_EPOCH) AS c_time

[sowings]

You can use a calculated field (EVAL- in props.conf) to convert your fields to something pretty-printed.

[cramasta]

I dont think dbconnect app has a way of being told what fields are timestamps other than what is used to create the index time of the data.

You will probably either have to do it with the convert command or you can possible do it within the sql query itself

Example I stole from the web

SELECT DATE_FORMAT(FROM_UNIXTIME(date_col), '%e %b %Y') AS date_formatted . . .

[Karunamon]

Okay, that definitely works, but it's going to make for some hellaciously ugly search strings considering I have about 7 or 8 different time fields in this query result. Is there a way to have the human readable format be the default for all queries and relevant fields?