topic Re: Date Conversion in Splunk Search

Date Conversion

raghu0463 — Tue, 29 Sep 2020 15:06:52 GMT

Hi,
How to convert this SQL statement to SPL pls

select DateDiff(day, ga.Initial_L1_Decision_Date, Close_date) as [Days Since Initial L1 Decision] from table

Re: Date Conversion

somesoni2 — Mon, 31 Jul 2017 13:50:55 GMT

If both the dates are in epoch format, you can do like this

..| eval "Days Since Initial L1 Decision"=round(abs('Close_date'-'ga.Initial_L1_Decision_Date')/86400)

If they're not epoch, you need to convert them to epoch for calculation, like this (update the timeformat in strptime according to yours)

..| eval "Days Since Initial L1 Decision"=round(abs(strptime('Close_date',"%Y-%m-%d %H:%M:%S")-strptime('ga.Initial_L1_Decision_Date',"%Y-%m-%d %H:%M:%S"))/86400)

Re: Date Conversion

gcusello — Wed, 30 Sep 2020 02:54:47 GMT

Hi raghu0463,
I'm not sure of one thing: in your select it seems that there are three fields in Diff (day, ga.Initial_L1_Decision_Date, Close_date), are really three or not?
if you have two dates, try something like this:

your_search
| eval "Days Since Initial L1 Decision"=(strptime(ga.Initial_L1_Decision_Date,"%m/%d/%Y %H:%M:%S")-strptime(Close_date,"%m/%d/%Y %H:%M:%S"))/3600/24
| table your_fields "Days Since Initial L1 Decision"

I don't know time format of your dates, if they are wrong, modify the ones I used.

Bye.
Giuseppe

Re: Date Conversion

raghu0463 — Mon, 31 Jul 2017 15:11:36 GMT

is that strptime is for single field or do i need to apply for both together ?

Re: Date Conversion

somesoni2 — Mon, 31 Jul 2017 15:26:36 GMT

You need to apply strptime for each field which you want to convert from string to date/epoch.