How to get date picker to work with dates output by DBX and Oracle

[chazaswell]

I have tried using column names such as DATE, TIMESTAMP, EVENTDATE when selecting data from an Oracle timestamp field. The search date picker just ignores my selecting Today, Last Week, etc. I know I can write custom SQL to find the correct records, but would like to leave the user the ability to select dates using date picker. Do I need a specific column name with a particular date format? There must be a way to do this. Thanks.

[neusse]

I don't agree that there are two types of time ranges from the time picker. Everything derives down to earliest and latest. It is really very simple. I have setup a macro that actually manages this. This allows the time picker to be transparent for the user. It only requires a small statement in the SQL like between.

makeresults
|addinfo
| eval info_max_time=if( info_max_time="+Infinity",now(),info_max_time)
| eval olate = strftime( info_max_time, "%Y-%m-%d %H:%M:%S.%3N" )
| eval oearl = strftime( info_min_time, "%Y-%m-%d %H:%M:%S.%3N" )
| map search="dbxquery yada yada \"select \"$X$\" from $table$ where $checkvar$ between to_timestamp('$oearl$', 'YYYY-MM-DD hh24:mi:ss.ff') and to_timestamp('$olate$', 'YYYY-MM-DD hh24:mi:ss.ff') \""
| eval _time=$checkvar$

This is the jist of what goes in the macro for Oracle. The date conversions would be different for other SQL. It works very well and is transparent.

Regards,

[jcoates_splunk]

The Splunk time selector element allows users to switch from exact time representations (between then and now) and relative time representations (earlier than a week ago). Unfortunately the SQL needed to handle these two types of time is different, so customization of the form to limit available time input formats is advised.

[neusse]

I downvoted this post because this statement of non compatibility of time is inaccurate.

[neusse]

My answer below actually fixes this issue between SQL and Splunk and allow for the fixed timepicker values to be passed into a SQL query and return correct results.