Splunk Search

Date Conversion

raghu0463
Explorer

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

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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)

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
Revered Legend

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)
0 Karma

raghu0463
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...