https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120755#M184050 <P>After query MySQL data base in DB connect, the date is number, how to make it as "YYYY-MM-DD HH-MM-SS"?</P> Thu, 11 Sep 2014 09:05:10 GMT felix_fxm 2014-09-11T09:05:10Z https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120755#M184050 <P>After query MySQL data base in DB connect, the date is number, how to make it as "YYYY-MM-DD HH-MM-SS"?</P> Thu, 11 Sep 2014 09:05:10 GMT https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120755#M184050 felix_fxm 2014-09-11T09:05:10Z https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120756#M184051 <P>Hi Felix,</P> <P>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:</P> <PRE><CODE>... | eval date=strftime(time_epoch, "%F %T") </CODE></PRE> <P>Greetings</P> <P>Tom</P> Thu, 11 Sep 2014 10:50:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120756#M184051 tom_frotscher 2014-09-11T10:50:36Z https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120757#M184052 <P>Hi. You can also try using an SQL statement in your query that converts epoch time to datetime. For example:</P> <PRE><CODE>SELECT FROM_UNIXTIME(epoch timestamp, optional output format) The default output is YYYY-MM-DD HH:MM:SS </CODE></PRE> Thu, 11 Sep 2014 18:23:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120757#M184052 sroback_splunk 2014-09-11T18:23:54Z https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120758#M184053 <P>Thanks Tom and Sroback! I have tried both, but failed. Following is my search command.</P> <P>| 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")</P> <P>But the column of “Start_Time” is still a number, such as "1402012699.000".<BR /> Type of this column_name is "Date_Time".</P> <P>Is there some problem with my search command?</P> Mon, 28 Sep 2020 17:34:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120758#M184053 felix_fxm 2020-09-28T17:34:08Z https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120759#M184054 <P>Hi Tom, You might want to have a look at this documentation about timestamps and dboutput in DB Connect:<BR /> <A href="http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestamps_and_database_output">http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestamps_and_database_output</A><BR /> Make sure to review the section on "Timestamp best practices." In case you are using timestamp as rising column, it is not recommended. </P> <P>Also, you might want to review this Search Reference doc to make sure you are using proper syntax for the "eval" command:<BR /> <A href="http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/CommonEvalFunctions">http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/CommonEvalFunctions</A></P> Tue, 16 Sep 2014 20:14:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-change-the-date-format-if-query-MySQ-data-base-in-DB/m-p/120759#M184054 sroback_splunk 2014-09-16T20:14:41Z