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
... View more