How to change the date format if query MySQ data base in DB connect.

[felix_fxm]

After query MySQL data base in DB connect, the date is number, how to make it as "YYYY-MM-DD HH-MM-SS"?

[sroback_splunk]

Hi Tom, You might want to have a look at this documentation about timestamps and dboutput in DB Connect:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestam...
Make sure to review the section on "Timestamp best practices." In case you are using timestamp as rising column, it is not recommended.

Also, you might want to review this Search Reference doc to make sure you are using proper syntax for the "eval" command:
http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/CommonEvalFunctions

[sroback_splunk]

Hi. You can also try using an SQL statement in your query that converts epoch time to datetime. For example:

SELECT FROM_UNIXTIME(epoch timestamp, optional output format)
The default output is YYYY-MM-DD HH:MM:SS

[tom_frotscher]

Hi Felix,

if your date is an epoch timestamp, you can use the strftime command to convert it. Assume time_epoch is your date as number, then:

... | eval date=strftime(time_epoch, "%F %T")

Greetings

Tom

[felix_fxm]

Thanks Tom and Sroback! I have tried both, but failed. Following is my search command.

| dbquery WAS_Dev limit=1000 "select * from wsa_db.metering left join (wsa_db.user)\n on (wsa_db.user.Oxygen_Id=wsa_db.metering.Oxygen_Id)"|eval date=strftime(time_epoch, "%F %T")

But the column of “Start_Time” is still a number, such as "1402012699.000".
Type of this column_name is "Date_Time".

Is there some problem with my search command?