Splunk Search

How do I make it so time conversion always has epoch start at midnight regardless of DST?

wjrbrady
New Member

I have the following SPL. I am trying to calculate days i want to look up for data. Instead of trying to load a whole year's worth of data, I want to select certain days. When i run this, it shows me for today. It starts at midnight but for days that are in DST it moves it 1hr forward so my data is off.

 makeresults 
| eval sdate=if("$custdate$"="",now(),strptime("$custdate$","%m/%d/%Y"))
| eval t=strftime(sdate,"%m/%d/%Y 00:00:00") 
| eval vdate=strftime(sdate,"%m/%d/%Y") 
| eval tu=strptime(t,"%m/%d/%Y 00:00:%Z")
| eval te=strftime(sdate,"%m/%d/%Y 00:00") 
| eval teu=strptime(te,"%m/%d/%Y 00:00")+86400
|eval te=strftime(teu,"%m/%d/%Y 00:00")
| eval ys=strptime(t,"%m/%d/%Y 00:00")-86400 
| eval ysd=strftime(ys,"%m/%d/%Y %H:%M") 
| eval ye=strptime(te,"%m/%d/%Y 00:00")-86400 
| eval yed=strftime(ye,"%m/%d/%Y %H:%M") 
| eval lws1=strptime(t,"%m/%d/%Y %H 00:%M 00")-604800 
| eval lws1d=strftime(lws1,"%m/%d/%Y %H:%M") 
| eval lwe1=strptime(te,"%m/%d/%Y 00:00")-604800  
| eval lwe1d=strftime(lwe1,"%m/%d/%Y %H:%M") 
| eval lws2=strptime(t,"%m/%d/%Y %H:%M")-1209600-$DST$  
| eval lws2d=strftime(lws2,"%m/%d/%Y %H:%M") 
| eval lwe2=strptime(te,"%m/%d/%Y %H:%M")-1209600-$DST$  
| eval lwe2d=strftime(lwe2,"%m/%d/%Y %H:%M") 
| eval lws3=strptime(t,"%m/%d/%Y %H:%M")-1814400-$DST$  
| eval lws3d=strftime(lws3,"%m/%d/%Y %H:%M") 
| eval lwe3=strptime(te,"%m/%d/%Y %H:%M")-1814400-$DST$  
| eval lwe3d=strftime(lwe3,"%m/%d/%Y %H:%M") 
| eval lws4=strptime(t,"%m/%d/%Y %H:%M")-2419200-$DST$  
| eval lws4d=strftime(lws4,"%m/%d/%Y %H:%M") 
| eval lwe4=strptime(te,"%m/%d/%Y %H:%M")-2419200-$DST$  
| eval lwe4d=strftime(lwe4,"%m/%d/%Y %H:%M") 
| eval lws5=strptime(t,"%m/%d/%Y %H:%M")-3024000-$DST$  
| eval lws5d=strftime(lws5,"%m/%d/%Y %H:%M") 
| eval lwe5=strptime(te,"%m/%d/%Y %H:%M")-3024000-$DST$  
| eval lwe5d=strftime(lwe5,"%m/%d/%Y %H:%M") 
| eval lys=strptime(t,"%m/%d/%Y %H:%M")-31449600 
| eval lysd=strftime(lys,"%m/%d/%Y %H:%M") 
| eval lye=strptime(te,"%m/%d/%Y %H:%M")-31449600 
| eval lyed=strftime(lye,"%m/%d/%Y %H:%M")
Tags (2)
0 Karma

wjrbrady
New Member

i think even with the relative time when you do the -"seconds" from epoch and it is before the time change it still somehow goes to 1am instead of midnight. The whole thing was that i wanted to use a txt box that if it was empty it would run for today and look back the last 5 weeks and get the data i needed. and if i put in a date it would take that date and figure out the epoch time to gather the dates needed. So i have fixed my issue by doing the following. and now with just a txt box i can lookup any date and get the relative data needed to compare instead of using the time picker that is provided.
hope this helps anyone out that is trying to do the same.
| makeresults
| eval sdate=if("$custdate$"="",now(),strptime("$custdate$","%m/%d/%Y"))
| eval t=strftime(sdate,"%m/%d/%Y 00:00:00")
| eval vdate=strftime(sdate,"%m/%d/%Y")
| eval tu=strptime(t,"%m/%d/%Y 00:00")

| eval te=strftime(sdate,"%m/%d/%Y 00:00")
| eval teu=strptime(te,"%m/%d/%Y 00:00")+86400
| eval te=strftime(teu,"%m/%d/%Y %H:%M")
| eval tedh=strftime(teu,"%H")
| eval teu=if(tedh=23,teu+3600,teu)
| eval ted=strftime(teu,"%m/%d/%Y %H:%M")

| eval ys=strptime(t,"%m/%d/%Y %H:%M")-86400
| eval ysd=strftime(ys,"%m/%d/%Y %H:%M")
| eval ysdh=strftime(ys,"%H")
| eval ys=if(ysdh=01,ys-3600,ys)
| eval ys=if(ysdh=23,ys+3600,ys)
| eval ysd=strftime(ys,"%m/%d/%Y %H:%M")

| eval ye=strptime(te,"%m/%d/%Y %H:%M")-86400
| eval yed=strftime(ye,"%m/%d/%Y %H:%M")
| eval yedh=strftime(ye,"%H")
| eval ye=if(yedh=01,ye-3600,ye)
| eval ye=if(yedh=23,ye+3600,ye)
| eval yed=strftime(ye,"%m/%d/%Y %H:%M")

| eval lws1=strptime(t,"%m/%d/%Y %H:%M")-604800
| eval lws1d=strftime(lws1,"%m/%d/%Y %H:%M")
| eval lws1dh=strftime(lws1,"%H")
| eval lws1=if(lws1dh=01,lws1-3600,lws1)
| eval lws1=if(lws1dh=23,lws1+3600,lws1)
| eval lws1d=strftime(lws1,"%m/%d/%Y %H:%M")

| eval lwe1=strptime(te,"%m/%d/%Y %H:%M")-604800

| eval lwe1d=strftime(lwe1,"%m/%d/%Y %H:%M")
| eval lwe1dh=strftime(lwe1,"%H")
| eval lwe1=if(lwe1dh=01,lwe1-3600,lwe1)
| eval lwe1=if(lwe1dh=23,lwe1+3600,lwe1)
| eval lwe1d=strftime(lwe1,"%m/%d/%Y %H:%M")

| eval lws2=strptime(t,"%m/%d/%Y %H:%M")-1209600

| eval lws2d=strftime(lws2,"%m/%d/%Y %H:%M")
| eval lws2dh=strftime(lws2,"%H")
| eval lws2=if(lws2dh=01,lws2-3600,lws2)
| eval lws2=if(lws2dh=23,lws2+3600,lws2)
| eval lws2d=strftime(lws2,"%m/%d/%Y %H:%M")

| eval lwe2=strptime(te,"%m/%d/%Y %H:%M")-1209600

| eval lwe2d=strftime(lwe2,"%m/%d/%Y %H:%M")
| eval lwe2dh=strftime(lwe2,"%H")
| eval lwe2=if(lwe2dh=01,lwe2-3600,lwe2)
| eval lwe2=if(lwe2dh=23,lwe2+3600,lwe2)
| eval lwe2d=strftime(lwe2,"%m/%d/%Y %H:%M")

| eval lws3=strptime(t,"%m/%d/%Y %H:%M")-1814400

| eval lws3d=strftime(lws3,"%m/%d/%Y %H:%M")
| eval lws3dh=strftime(lws3,"%H")
| eval lws3=if(lws3dh=01,lws3-3600,lws3)
| eval lws3=if(lws3dh=23,lws3+3600,lws3)
| eval lws3d=strftime(lws3,"%m/%d/%Y %H:%M")

| eval lwe3=strptime(te,"%m/%d/%Y %H:%M")-1814400
| eval lwe3d=strftime(lwe3,"%m/%d/%Y %H:%M")
| eval lwe3dh=strftime(lwe3,"%H")
| eval lwe3=if(lwe3dh=01,lwe3-3600,lwe3)
| eval lwe3=if(lwe3dh=23,lwe3+3600,lwe3)
| eval lwe3d=strftime(lwe3,"%m/%d/%Y %H:%M")

| eval lws4=strptime(t,"%m/%d/%Y %H:%M")-2419200

| eval lws4d=strftime(lws4,"%m/%d/%Y %H:%M")
| eval lws4dh=strftime(lws4,"%H")
| eval lws4=if(lws4dh=01,lws4-3600,lws4)
| eval lws4=if(lws4dh=23,lws4+3600,lws4)
| eval lws4d=strftime(lws4,"%m/%d/%Y %H:%M")

| eval lwe4=strptime(te,"%m/%d/%Y %H:%M")-2419200

| eval lwe4d=strftime(lwe4,"%m/%d/%Y %H:%M")
| eval lwe4dh=strftime(lwe4,"%H")
| eval lwe4=if(lwe4dh=01,lwe4-3600,lwe4)
| eval lwe4=if(lwe4dh=23,lwe4+3600,lwe4)
| eval lwe4d=strftime(lwe4,"%m/%d/%Y %H:%M")

| eval lws5=strptime(t,"%m/%d/%Y %H:%M")-3024000

| eval lws5d=strftime(lws5,"%m/%d/%Y %H:%M")
| eval lws5dh=strftime(lws5,"%H")
| eval lws5=if(lws5dh=01,lws5-3600,lws5)
| eval lws5=if(lws5dh=23,lws5+3600,lws5)
| eval lws5d=strftime(lws5,"%m/%d/%Y %H:%M")

| eval lwe5=strptime(te,"%m/%d/%Y %H:%M")-3024000
| eval lwe5d=strftime(lwe5,"%m/%d/%Y %H:%M")
| eval lwe5dh=strftime(lwe5,"%H")
| eval lwe5=if(lwe5dh=01,lwe5-3600,lwe5)
| eval lwe5=if(lwe5dh=23,lwe5+3600,lwe5)
| eval lwe5d=strftime(lwe5,"%m/%d/%Y %H:%M")

| eval lys=strptime(t,"%m/%d/%Y %H:%M")-31449600
| eval lysd=strftime(lys,"%m/%d/%Y %H:%M")
| eval lysdh=strftime(lys,"%H")
| eval lys=if(lysdh=01,lys-3600,lys)
| eval lys=if(lysdh=23,lys+3600,lys)
| eval lysd=strftime(lys,"%m/%d/%Y %H:%M")

| eval lye=strptime(te,"%m/%d/%Y %H:%M")-31449600
| eval lyed=strftime(lye,"%m/%d/%Y %H:%M")
| eval lyedh=strftime(lye,"%H")
| eval lye=if(lyedh=01,lye-3600,lye)
| eval lye=if(lyedh=23,lye+3600,lye)
| eval lyed=strftime(lye,"%m/%d/%Y %H:%M")
| table vdate t te tu teu ys ysd ye yed lws1 lws1d lwe1 lwe1d lws2 lws2d lwe2 lwe2d lws3 lws3d lwe3 lwe3d lws4 lws4d lwe4 lwe4d lws5 lws5d lwe5 lwe5d lys lysd lye lyed
| head 1

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Consider using the relative_time function. It will handle DST for you.

For example, instead of eval teu=strptime(te,"%m/%d/%Y 00:00")+86400 you could use eval teu=relative_time(sdate, "+1d@d").

---
If this reply helps you, an upvote would be appreciated.
0 Karma

wjrbrady
New Member

I will give that a shot as well to see if it works

0 Karma

wjrbrady
New Member

| makeresults
| eval sdate=now()
| eval t=strftime(sdate,"%m/%d/%Y 00:00:00")
| eval vdate=strftime(sdate,"%m/%d/%Y")
| eval tu=strptime(t,"%m/%d/%Y 00:00")
| eval te=strftime(sdate,"%m/%d/%Y 00:00")
| eval teu=strptime(te,"%m/%d/%Y 00:00")+86400
| eval te=strftime(teu,"%m/%d/%Y 00:00")
| eval ys=strptime(t,"%m/%d/%Y 00:00")-86400
| eval ysd=strftime(ys,"%m/%d/%Y %H:%M")
| eval ye=strptime(te,"%m/%d/%Y 00:00")-86400
| eval yed=strftime(ye,"%m/%d/%Y %H:%M")
| eval lws1=strptime(t,"%m/%d/%Y 00:00")-604800
| eval lws1d=strftime(lws1,"%m/%d/%Y %H:%M")
| eval lwe1=strptime(te,"%m/%d/%Y 00:00")-604800

| eval lwe1d=strftime(lwe1,"%m/%d/%Y %H:%M")
| eval lws2=strptime(t,"%m/%d/%Y %H:%M")-1209600

| eval lws2d=strftime(lws2,"%m/%d/%Y %H:%M")
| eval lwe2=strptime(te,"%m/%d/%Y %H:%M")-1209600
| eval lwe2d=strftime(lwe2,"%m/%d/%Y %H:%M")
| eval lws3=strptime(t,"%m/%d/%Y %H:%M")-1814400

| eval lws3d=strftime(lws3,"%m/%d/%Y %H:%M")
| eval lwe3=strptime(te,"%m/%d/%Y %H:%M")-1814400
| eval lwe3d=strftime(lwe3,"%m/%d/%Y %H:%M")
| eval lws4=strptime(t,"%m/%d/%Y %H:%M")-2419200
| eval lws4d=strftime(lws4,"%m/%d/%Y %H:%M")
| eval lwe4=strptime(te,"%m/%d/%Y %H:%M")-2419200
| eval lwe4d=strftime(lwe4,"%m/%d/%Y %H:%M")
| eval lws5=strptime(t,"%m/%d/%Y %H:%M")-3024000
| eval lws5d=strftime(lws5,"%m/%d/%Y %H:%M")
| eval lwe5=strptime(te,"%m/%d/%Y %H:%M")-3024000
| eval lwe5d=strftime(lwe5,"%m/%d/%Y %H:%M")
| eval lys=strptime(t,"%m/%d/%Y %H:%M")-31449600
| eval lysd=strftime(lys,"%m/%d/%Y %H:%M")
| eval lye=strptime(te,"%m/%d/%Y %H:%M")-31449600
| eval lyed=strftime(lye,"%m/%d/%Y %H:%M")

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

@somesoni2

Did the answer below solve your problem? If so, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

0 Karma