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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...