need to print dates from Thanksgiving onward for the rest of the week until Monday
index="test" source="test" date=* mon=* year=* (STATDATE>=2016-11-22 AND STATDATE<=2016-11-30) SITE=USA | eval day_c = strftime(_time,"%Y-%m-%d") | eval enddate= year+"-11-29"| eval startdate= year+"-11-24" | eval DiffInSecs = strptime(enddate, "%Y-%m-%d")-strptime(startdate, "%Y-%m-%d") | eval td = strftime(DiffInSecs, "%Y-%m-%d %A") | table day_c td
Expected Results:
2016-11-24 2016-11-24 Thursday
Actual results:
2016-11-29 1970-01-05 Monday
2016-11-28 1970-01-05 Monday
2016-11-27 1970-01-05 Monday
2016-11-26 1970-01-05 Monday
2016-11-25 1970-01-05 Monday
2016-11-24 1970-01-05 Monday
2016-11-23 1970-01-05 Monday
2016-11-22 1970-01-05 Monday
So, first, you are asking to print the days. This is all you need for that.
index="test" source="test" date=* mon=* year=* (STATDATE>=2016-11-22 AND STATDATE<=2016-11-30) SITE=USA
| eval day_c = strftime(_time,"%Y-%m-%d %A")
Or this...
| makeresults
| eval enddate= "2017-11-29"
| eval startdate= "2017-11-24"
| eval enddate= strftime(strptime(enddate,"%Y-%m-%d"),"%Y-%m-%d %A")
| eval startdate= strftime(strptime(startdate,"%Y-%m-%d"),"%Y-%m-%d %A")
| table startdate enddate
However, Thanksgiving is the fourth Thursday in November in the US, so the calculation needs to start from the year.
This is aircode, but the method has been tested in Excel.
| makeresults
| eval year = "2017"
| eval nov01 = strptime(year."1101","%Y%m%d")
| eval weekday=strftime(nov01,"%w")
| eval thurs4day=if(weekday>3,32-weekday,25-weekday)
| eval startdate = nov01 - 86400 + thurs4day*86400
| eval enddate = startdate + 4*86400
| eval enddate= strftime(enddate,"%Y-%m-%d %A")
| eval startdate= strftime(startdate,"%Y-%m-%d %A")
| table startdate enddate
I think the | eval td = strftime(DiffInSecs, "%Y-%m-%d %A")
is the problem. The DiffInSecs is not a date, it's just the time diff between your start and end date. If you convert it to a epoch date using strptime, it'll show 1970 date. What do you want to display as part of field td?
I am actually calculating the difference between 2 dates where my year is dynamic based on the data.
In return I need to whole date in the same format
whole idea is to calculate the thanks giving day of the year also when I ll have thanks giving day as year-mon-day I need to print one week in the same format from that day.
2016
2015
2017
What does DiffInSecs represents (from which date to which date)?