Informix

 View Only
Expand all | Collapse all

Calculate the ISO Week Number

  • 1.  Calculate the ISO Week Number

    Posted Tue August 24, 2021 10:08 AM
    Hello All,

    I'm trying to convert to week number (1-53) for a specific date.
    And I want to use ISO week.

    I looked at the post below, but it didn't help much.
    https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number

    I wrote a messy query like the one below... but it seems inaccurate. Please advise on what is wrong or what needs to be improved.
    Thanks,

    SELECT ymd,
           (d3 / 7 + 1)::int iw
    FROM
    (
    SELECT dt
          ,TO_CHAR(dt, '%Y%m%d')::char(10) ymd
          ,(((TRUNC(dt - 1 units day, 'day') - NEXT_DAY(TRUNC(TRUNC(dt - 1 units day, 'day') - 1 units day, 'year') , 'SUN'))::interval day(3) to day)::varchar(12)::int ) d3
      FROM  (
    SELECT TO_DATE(yy||'01', '%Y%m') + (LEVEL - 1) units day dt
      FROM (SELECT '2021' yy FROM dual)
     CONNECT BY LEVEL <= 366)
    )​






    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    #Informix


  • 2.  RE: Calculate the ISO Week Number

    IBM Champion
    Posted Tue August 24, 2021 10:34 AM

    Write a C UDR  - it will be quicker, something like this  - pseudo code 100% not tested

     

    mi_integer oni_isoweek(mi_datetime *p_dte, MI_FPARAM *fp)

    {

         struct timeval   tv;

         struct tm      *ptm;

         dtestr          char(26);

     

    if(mi_fp_argisnull(fp,0)==MI_TRUE)

         {

    gettimeofday(&tv, NULL);

        ptm=localtime(&tv.tv_sec);

    }

        else

         {

            dtestr=mi_datetime_to_string(p_dte);

            dtestr[19]= NULL;

            strptime(dtestr,"%Y:%M:%D %H:%m:%s", &ptm);

    }

    return ((tm.tm_yday/7 + 1))

    }

     

     






  • 3.  RE: Calculate the ISO Week Number

    IBM Champion
    Posted Tue August 24, 2021 11:01 AM

    This compiles are runs

     

    mi_integer oni_isoweek(mi_datetime *p_dte, MI_FPARAM *fp)

    {

            struct timeval   tv;

            struct tm       *ptm;

            char             dtestr[40];

     

            gettimeofday(&tv, NULL);

            ptm=localtime(&tv.tv_sec);

     

            if(mi_fp_argisnull(fp,0) != MI_TRUE)

            {

                    strcpy(dtestr, mi_datetime_to_string(p_dte));

                    dtestr[19]= 0 ;

                    strftime(dtestr, 40,  "%Y:%M:%D %R:%S", ptm);

            }

            return ((ptm->tm_yday/7 + 1));

    }

     

    begin work;

        drop function if exists oni_isoweek(datetime year to fraction(5));

     

        create function oni_isoweek (datetime year to fraction(5))

            returns integer

            with (not variant, handlesnulls, parallelizable)

            external name "$INFORMIXDIR/extend/Oninit.1.0/oninit.bld(oni_isoweek)" language c;

     

        grant execute on function oni_isoweek (datetime year to fraction(5)) to public;

     

    commit work;

     

    select oni_isoweek(CURRENT) from sysmaster:sysdual;

    select oni_isoweek(NULL) from sysmaster:sysdual;

     

     

     






  • 4.  RE: Calculate the ISO Week Number

    IBM Champion
    Posted Tue August 24, 2021 12:15 PM

    Formatting the wrong way J

     

    Use strptime(dtestr, "%F %R:%S", ptm)

     






  • 5.  RE: Calculate the ISO Week Number

    Posted Wed August 25, 2021 01:24 AM
    Take a look at the SPL Jonathan Leffler wrote back in 2001 here: How do I calculate the week number given a date?

    Kind regards

    ------------------------------
    Roland Wintgen
    DBA, Genero/4GL Developer
    EVG Martens GmbH & Co. KG
    Moenchengladbach
    ------------------------------



  • 6.  RE: Calculate the ISO Week Number

    Posted Wed August 25, 2021 02:01 AM
    I think this was posted to the old newsgroup comp.databases.informix many, many years ago. Can't find the original posting now, so I don't know who the original author is, perhaps Jonathan Leffler?

    create or replace procedure "informix".date2week(_date date) returning char(7);   -- "yyyy.ww"
    
    define p_day          smallint;
    define p_month        smallint;
    define p_year         smallint;
    define p_firstweekday smallint;
    define p_days         smallint;
    define p_week         char(7);
    
     if (_date is null)
     then let _date = today;
     end if;
     let p_week = " ";
     let p_day = day(_date);
     let p_month = month(_date);
     let p_year = year(_date);
     let p_firstweekday = weekday(mdy(1, 1, p_year));
     let p_days = (_date - mdy(1, 1, p_year) + p_firstweekday - 1) / 7;
     if (p_firstweekday > 4)
     then if (p_days = 0)
          then let p_week = date2week(mdy(12, 31, p_year - 1));
               let p_days = p_week[6,7];
               let p_year = p_year - 1;
          else if ((p_firstweekday = 7) and ((mod(p_year, 4) = 0) and
    ((mod(p_year, 100) > 0) or (mod(p_year, 400) = 0))) and (p_month = 12)
    and (p_day = 31))
               then let p_days = 1;
                    let p_year = p_year + 1;
               end if;
          end if;
     else if (_date >= mdy(12, 29, p_year))
          then let p_firstweekday = weekday(mdy(12, p_day, p_year));
               if (p_day = 31)
               then if ((p_firstweekday = 1) or (p_firstweekday = 2) or
    (p_firstweekday = 3))
                    then let p_days = 0;
                         let p_year = p_year + 1;
                    end if;
               elif (p_day = 30)
               then if ((p_firstweekday = 1) or (p_firstweekday = 2))
                    then let p_days = 0;
                         let p_year = p_year + 1;
                    end if;
               elif (p_day = 29)
               then if (p_firstweekday = 1)
                    then let p_days = 0;
                         let p_year = p_year + 1;
                    end if;
               end if;
          end if;
          let p_days = p_days + 1;
     end if;
     let p_week[1,4] = p_year;
     let p_week[5] = ".";
     let p_week[6] = mod(p_days - mod(p_days, 10), 100) / 10;
     let p_week[7] = mod(p_days, 10);
     return (p_week);
    end procedure;​


    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------



  • 7.  RE: Calculate the ISO Week Number

    Posted Wed August 25, 2021 05:07 AM
    Thats funny because your code seems to be my updated version of Jonathans original procedure I posted above. I've been using this SPL for many years now and it still works flawlessly.

    Kind regards

    ------------------------------
    Roland Wintgen
    DBA, Genero/4GL Developer
    EVG Martens GmbH & Co. KG
    Moenchengladbach
    ------------------------------



  • 8.  RE: Calculate the ISO Week Number

    Posted Wed August 25, 2021 05:34 AM

    Ah, yes, I was able to track it down, it appears that this is the version you (Roland Wintgen) posted to comp.databases.informix on January 4th, 1999. We have been using this for many years.

    I'm sorry, I didn't see your post regarding Jonathan Leffler's version before I posted.



    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------



  • 9.  RE: Calculate the ISO Week Number

    Posted Wed August 25, 2021 03:29 AM
    Thanks to everyone replied.

    After Googling more related materials, I made the SPL again.

    create function weeknum ( dt varchar(10) )
    returning int;
    
    define wknum int;
    select trunc(((to_date(dt, '%Y-%m-%d') - yearstart)::interval day(3) to day::varchar(12)::integer) / 7) + 1
    into wknum
    from
    (
    select case when nextyr <= to_date(dt, '%Y-%m-%d') then nextyr
                when curryr <= to_date(dt, '%Y-%m-%d') then curryr
                else prioryr 
           end yearstart
    from 
    (
    select  
            mdy(1,1,1753) + trunc(((jan4 - 1 units year - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7  units day prioryr,
            mdy(1,1,1753) + trunc(((jan4                - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7  units day curryr,
            mdy(1,1,1753) + trunc(((jan4 + 1 units year - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7  units day nextyr
    from (
    select mdy(1,1,1900) + (extend (to_date(dt, '%Y-%m-%d'), year to year) - mdy (1,1,1900))::varchar(12)::integer units year + 3 units day jan4 from dual
    )
    )
    );
    return wknum;
    end function;
    
    
    $ dbaccess test -
    
    > select weeknum('2021-08-25') from sysmaster:sysdual;
    
    
    (expression)
    
              34
    
    1 row(s) retrieved.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 10.  RE: Calculate the ISO Week Number

    IBM Champion
    Posted Wed August 25, 2021 09:11 AM

    Cos I was curious, processing 10,000 rows

     

    time dbaccess eric oni_isoweek.sql >/dev/null 2>&1

     

    real    0m1.444s

    user    0m0.026s

    sys     0m0.017s

     

    time dbaccess eric weeknum.sql >/dev/null 2>&1

     

    real    0m0.370s

    user    0m0.028s

    sys     0m0.016s

     

    Cheers

    Paul