topic Re: Is it possible to display\calculate the ISO year number for a date ? in Splunk Search

Is it possible to display\calculate the ISO year number for a date ?

dirkpeter — Mon, 14 Jan 2019 12:54:02 GMT

With strftime(_time, "%Y-%V"), I can create a period to sort on a year and ISO weeknumber.

When I have events on 30-12-2018, 31-12-2018 and 01-01-2019 there is a sort problem.

strftime(30-12-2018, "%Y-%V")  -> 2018-52
strftime(31-12-2018, "%Y-%V")  -> 2018-01 this should be 2019-01 for a correct sort
strftime(01-01-2019, "%Y-%V")  -> 2019-01

Is it possible to display or calculate the iso year number for a date ?

Re: Is it possible to display\calculate the ISO year number for a date ?

richgalloway — Mon, 14 Jan 2019 14:02:25 GMT

You should submit a Support request to report that bug.

Re: Is it possible to display\calculate the ISO year number for a date ?

mayurr98 — Mon, 14 Jan 2019 14:43:10 GMT

I do not think that is a bug because %Y will always give you year from that date so it will always give you 2018 only.
Well I have found a workaround for this :

Try this run anywhere search :

| makeresults 
| eval date="30-12-2018,31-12-2018,01-01-2019" 
| makemv date delim="," 
| mvexpand date 
| table date 
| eval date_epoch=strptime(date,"%d-%m-%Y") 
| eval year=strftime(date_epoch,"%Y"),day_mon=strftime(date_epoch,"%d-%m"),new_year=if(like(date,"31-12-%"),year+1,year) 
| eval new_date=day_mon+"-"+new_year 
| eval new_date=strptime(new_date,"%d-%m-%Y") 
| eval ISOWN=strftime(new_date,"%Y-%V") 
| table date ISOWN

Basically what I have done is whenever date is 31-12-* it will add +1 to the year so %Y will not fail.

let me know if this helps!

Re: Is it possible to display\calculate the ISO year number for a date ?

vnravikumar — Mon, 14 Jan 2019 14:52:37 GMT

Hi @mayurr98

As per the above logic for 30-12-2019 it should be Week 01, 2020. but its giving wrong as 2019-01.

Re: Is it possible to display\calculate the ISO year number for a date ?

dirkpeter — Mon, 21 Jan 2019 15:04:04 GMT

I want to calculate the ISO year number based on a date.

First ISO week:
The ISO 8601 definition for week 01 is the week with the Gregorian year's first Thursday in it. The following definitions based on properties of this week are mutually equivalent, since the ISO week starts with Monday:
It is the first week with a majority (4 or more) of its days in January.
Its first day is the Monday nearest to 1 January.
It has 4 January in it. Hence the earliest possible first week extends from Monday 29 December (previous Gregorian year) to Sunday 4 January, the latest possible first week extends from Monday 4 January to Sunday 10 January.
It has the year's first working day in it, if Saturdays, Sundays and 1 January are not working days.

ISO YEAR ISO WEEK Starts on
2019 1 ma 31-12-2018
2020 1 ma 30-12-2019
2021 1 ma 04-01-2021
2022 1 ma 03-01-2022
2023 1 ma 02-01-2023
2024 1 ma 01-01-2024
2025 1 ma 30-12-2024
2026 1 ma 29-12-2025

If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, it is part of week 53 of the previous year. If it is on a Saturday, it is part of the last week of the previous year which is numbered 52 in a common year and 53 in a leap year. If it is on a Sunday, it is part of week 52 of the previous year.