Getting Data In

Convert String to Date Issue

matthewfry57
Explorer

I have gone through the forums looking for an answer to this, but nothing has worked.  I am trying to convert a string to a date.  I have data in an index that is extracted, with a field named Expiration_Date that contains a string that is actually a date/time, such as 5/22/2022 10:10:25 PM.

I found that this query works properly:

| makeresults
| eval x="08/04/16 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%y %H:%M:%S")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

This query outputs the converted time properly in the z field.  However, when I try to use this with my data, as such

index = ssl_certs
| eval x=Expiration_Date
| eval y=strptime(x, "%m/%d/%y %H:%M:%S")
| eval z=strftime(y, "%m/%d/%Y")
| table Expiration_Date, x, y, z

The x field is equal to the Expiration_Date field, but y and z fields are empty.  Is there something special I am missing here with loading in the values of the Expiration_Date field into the eval statements?

Labels (1)
0 Karma
1 Solution

TheDeFoor
Explorer

It looks like you are getting a 4 digit year, so it'd be more like this -

| makeresults
| eval x="8/04/2016 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%Y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

Lowercase y is for 2 digit years, capital Y is for 4 digit years

View solution in original post

TheDeFoor
Explorer

Hello!

What is the data you're getting for Expiration_Date? It looks like you are expecting MM/DD/YY HH:MM:SS or 07/27/21 01:29:33. Is this the format of the Expiration_Date field? Can you provide an example?

0 Karma

matthewfry57
Explorer

Expiration_Date values look like this:

matthewfry57_0-1627418039417.png

 

Tags (1)
0 Karma

isoutamo
SplunkTrust
SplunkTrust
Hi
Can you try to use %I and %p as you are using 12h not 24h time?
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables
r. Ismo
0 Karma

matthewfry57
Explorer

I had tried this also, same result.

index = ssl_certs
| eval x=Expiration_Date
| eval y=strptime(x, "%m/%d/%y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table Expiration_Date, x, y, z

matthewfry57_0-1627418134736.png

 

0 Karma

TheDeFoor
Explorer

It looks like you are getting a 4 digit year, so it'd be more like this -

| makeresults
| eval x="8/04/2016 9:40:41 PM"
| eval y=strptime(x, "%m/%d/%Y %I:%M:%S %p")
| eval z=strftime(y, "%m/%d/%Y")
| table x y z

Lowercase y is for 2 digit years, capital Y is for 4 digit years

matthewfry57
Explorer

That was my problem!!  Thank you much!!

0 Karma

isoutamo
SplunkTrust
SplunkTrust
You are sure that Expiration_Date is not a multivalue field or it’s not containing any additional characters?
0 Karma

matthewfry57
Explorer

Yup I am sure, here is an example of the extracted field.  No extra spaces or anything.

matthewfry57_0-1627418994175.png

The source data comes from a file that looks like this (obfuscated hostnames):

matthewfry57_1-1627419094233.png

 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...