How to execute a dbxquery in Splunk by adjusting only the time tokens?

[_pravin]

Hi Y'all,

I am trying to execute a dbxquery in Splunk by adjusting only the time tokens.

Splunk server is in a time zone and the database server is also in the same time zone but when I execute the query I can see that the query is executing with a lag of an hour.

When I execute the same in the database directly, I can see that there is no lag and the query executes perfectly.

Only difference I could sense is the usage of epoch time conversion in SPL and no conversion used in the database query which I feel might be a time zone difference between the servers.

| dbxquery query="select 100-(COUNT(DISTINCT (week)) *100 / (DATEDIFF (DATE_FORMAT(FROM_UNIXTIME('1680559200'), '%Y%m%d') , DATE_FORMAT(FROM_UNIXTIME('1672527600'), '%Y%m%d'))/7)) from `mxone_db`.`pdt_disruptions` where DATE_FORMAT (DATE, '%Y%m%d' ) between DATE_FORMAT(FROM_UNIXTIME('1672527600'), '%Y%m%d') AND DATE_FORMAT(FROM_UNIXTIME('1680559200'), '%Y%m%d')" connection="dbuser"

Solution: 47.3118

select 100-(COUNT(DISTINCT (week)) *100 / (DATEDIFF (DATE_FORMAT('2023-04-03', '%Y%m%d') , DATE_FORMAT('2023-01-01', '%Y%m%d'))/7)) from pdt_disruptions 
where DATE_FORMAT (DATE, '%Y%m%d' ) between DATE_FORMAT('2023-01-01', '%Y%m%d') AND DATE_FORMAT('2023-04-03', '%Y%m%d')

Solution: 46.7391

Not sure where is this query getting lost?
Please let me know if you have any clue of this issue.

Regards,
Pravin

[tscroggins · Accepted solution]

Hi,

Splunk DB Connect time zone conversion is explained here: https://docs.splunk.com/Documentation/DBX/3.8.0/DeployDBX/Createandmanagedatabaseconnections 

You appear to be using MySQL or a derivative; MySQL support for time zones is explained here: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html 

If both MySQL and Splunk are properly configured, time zone conversion should work as expected when using native date types (not string types). If not, you can perform an explicit conversion:

DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME('680559200'), 'Etc/UTC', 'PST8PDT'), '%Y%m%d')

To compare dates, time zone conversion shouldn't be necessary:

DATEDIFF(FROM_UNIXTIME('1680559200'), FROM_UNIXTIME('1672527600'))

[_pravin]

Hi @tscroggins ,

Thanks for the help. It seemed to fix the issue.

Regards,

Pravin