Announcement

Collapse
No announcement yet.

Date and Time

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Date and Time

    First time Powerbasic user here and before I begin my next development cycle I want to port and consolidate any number of common routines I have grown accustomed to into one DLL.

    This being my maiden venture, so to speak, and hopefully I will learn and grow familiar with this language.

    So the creation of this DLL begins...and the first area of interest covers date and time functions. I have in the past developed a complete port (in assembler) cover to cover the algorithms presented in the books Astronomical Algorithms, Second Edition by Jean Meeus and Calendrical Calculations: The Millennium Edition by Edward M. Reingold and Nachum Dershowitz along with a myriad of other custom functions I have found useful. So far, the port is going smoothly and PB is certainly proving an easy master to work for although a ways to go to approach my enjoyment of assembler (MASM32).

    With introductions to the group over with, this upcoming project encapsulates a type of business and I'm going to be in need of some business based date calculations that inevitably involve designation of non-business days both as recurring (week days that are workdays/non-workdays) during a year and special days that typically do not reoccur during the year usually spoken of as holidays.

    I am not a fan of storing dates (which require someone to maintain) and have taken a break from my DLL port to add a rules-based function that I really like that is capable of calculating every gregorian based public holiday I have been able to find on the internet when provided the gregorian year and the rules describing the holiday.

    Given a base date and some number of days to add/subtract which is very simple to do, if you add which days of the week are business or non-business and a list of the observed holidays (as a group of rule sets) two approaches come to mind for calculating the answer.

    1. Loop through, adding one day at a time, and checking each day against the weekday and holiday rules until the requisite number of days have been added

    2. Calculate the result directly via an algorithmic approach

    In keeping with my own and the PB battle cry of efficiency, I have delved into approach 2. It boils down to this:

    1. Calculate the preliminary ending date by just adding the presented days.

    2. Extend the ending date by the number of non-business weekdays found in the range calculation in step 1

    3. Repeat step 2 until the new ending date is a normal business workday.

    4. Examine each holiday and check if it is included in the current range and bump the ending date up when true only if the holiday also falls on a weekday that is workday

    5. If step 4 is true, redo the weekday check and restart step 4

    6. Done when step 4 finds no applicable holidays

    There exists the possibility of an endless loop race that I think is avoided as long as one day of the week is a normal workday and can hope that nobody would present 52 or 53 holidays that all fall on that same day of the week and cover an entire gregorian year.

    Of course, I have to keep track of when I extend the range past December 31st and regenerate holidays as needed which dictates that the holidays presented to the function be rules based.

    In my long winded way, are there any forum members with knowledge or tips on a better way to do this calculation? If I can this to work, I will never, never again store holiday dates, I promise!

    Another goal of mine is that this PB DLL be language neutral. If I am to use a set of holiday rules that contains a variable number of rule groups, is it possible, from PB to call this function and pass an array that the DLL just sees as one contiguous segment of memory? I haven't read up yet on how to take this pointer in my PB DLL and use it to parse the rule groups given the variable nature of the the entire structure size.

    TIA

    Rick Kelly
    ------------------------------------------------------------
    sigpic

    It has come to my attention that certain dubious forces are interpolating their desires in my search for Mom, apple pie and the girl you left behind. Stop it or I'll scream...

  • #2
    1. Loop through, adding one day at a time, and checking each day against the weekday and holiday rules until the requisite number of days have been added
    Easy, fast. Also the way I do it. probably because it's easy and fast.

    I have to do exactly the same thing, add some number of days to a date not including weekends. (I don't have to do holidays, but that's the way I set up the code if I ever had to do so.

    Holiday Rules?

    You just enter in a database or file
    Code:
    20091225     ; chistmas
    20090914     ; arbor day
    ....
    You can load these into an array, then just ARRAY SCAN the current date for a hit... if you get a hit, it's a holiday.

    "assuming" you are doing all this using FILETIME and SYSTEMTIME structures, you can just use GetDateFormat() against current date being tested (if not a weekend) and Windows will format the string for you.

    MCM
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      Date and Time

      Thank you for the feedback Michael...responses below

      [Michael Mattias;314298]Easy, fast. Also the way I do it. probably because it's easy and fast.

      I have to do exactly the same thing, add some number of days to a date not including weekends. (I don't have to do holidays, but that's the way I set up the code if I ever had to do so.
      I have found that fast is relative until you get into larger ranges of time and I want this function to be fast under all conditions without concern to the expanse of time. I have a function that can tell me the number of occurences of a weekday for any period of days this is always very fast.


      Holiday Rules?

      You just enter in a database or file
      Code:
      20091225     ; chistmas
      20090914     ; arbor day
      ....
      You can load these into an array, then just ARRAY SCAN the current date for a hit... if you get a hit, it's a holiday.

      "assuming" you are doing all this using FILETIME and SYSTEMTIME structures, you can just use GetDateFormat() against current date being tested (if not a weekend) and Windows will format the string for you.

      MCM
      I am not using FILETIME/SYSTEMTIME structures. It is a custom structure that contains holiday rules necessary to calculate the date for the applicable year. I am striving to avoid having my applications store any holiday dates opting for storing the rules for their calculation instead.

      Rick
      ------------------------------------------------------------
      sigpic

      It has come to my attention that certain dubious forces are interpolating their desires in my search for Mom, apple pie and the girl you left behind. Stop it or I'll scream...

      Comment


      • #4
        Date and Time

        I thought an example of the efficiencies that are available in the algorithmic approach to calculating a business date might be helpful.

        Assuming that we are starting with April 1, 2009, what is the date 20 business days from now?

        Lets also assume that Saturday/Sunday are the weekdays that are non-business days.

        1. April 1 + 20 days = April 21

        2. Saturday before April 1 = 3/28 and Saturday after 4/21 = 4/25

        3. Number Saturdays between 3/28 and 4/25, not counting 3/28 is 4

        4. Sunday before April 1 is 3/29 and Sunday after 4/21 is 4/26

        5. Number Sundays between 3/29 and 4/26, not counting 3/29 is 4

        6. Final result is April 21 + 4 Saturdays + 4 Sundays = 4/29

        It doesn't matter how many business days you want to use, the steps are the same and the algorithmic approach performance remains constant. It's possible under certain circumstances to add one additional direct calculation if the final result falls on a non-business day of the week.

        Cheers!

        Rick
        ------------------------------------------------------------
        sigpic

        It has come to my attention that certain dubious forces are interpolating their desires in my search for Mom, apple pie and the girl you left behind. Stop it or I'll scream...

        Comment


        • #5
          This is what I use to add some number of days to a date. My input is a date, my output must be the next workday (M-F) on or after the specified number of days (which may be postive or negative) is added to that date.
          Code:
          ' --------------------------------------------------------------------
          ' INTERNAL FUNCTION TO COMPUTE NEW DATE FROM INPUT DATE and NUMBER OF DAYS
          ' --------------------------------------------------------------------
          ' return input date + nDays (which may be signed) as a CCYYMMDD string
          ' Working OK 4-24-04. always returns a day mon-fri
          FUNCTION f_AddWorkDays (CCYYMMDD AS STRING, nDays AS LONG) AS STRING
          
              LOCAL st AS SYSTEMTIME
              LOCAL ft AS ftunion
              LOCAL nDaysDone AS LONG, E AS LONG
          
              IF LEN(CCYYMMDD) <> 8 OR ISTRUE(VERIFY(CCYYMMDD,"0123456789")) THEN
                  FUNCTION = ""
                  EXIT FUNCTION
              END IF
          
              RESET           st
              st.wyear     =  VAL(MID$(CCYYMMDD, 1,4))
              st.wmonth    =  VAL(MID$(CCYYMMDD, 5,2))
              st.wDay      =  VAL(MID$(CCYYMMDD, 7,2))
          
              ' convert starting date to filetime and validate date
              E       = SystemTimeToFileTime (st, ft.ft)
              IF ISFALSE E THEN
              '    E = GetLastError
                '  MSGBOX "ST2FT Failed on Error " & STR$(E)
                  ' fails on error zero if invalid date
                  FUNCTION = ""  ' could be "INVALID" or "BADDATE" or something just as easily
                  EXIT FUNCTION
              END IF
          
          
              nDaysDone =  0&
          
              DO UNTIL nDaysDone = nDays
                  ' add or subtract a day depending on direction
                  IF nDays < 0 THEN
                      ft.q  = ft.q - %ONE_FT_DAY
                  ELSE
                      ft.q  = ft.Q + %ONE_FT_DAY
                  END IF
                  ' check DOW. If not sat or sun, incr or decr daysdone
                   FileTimeToSystemTime  ft.ft, st
                   SELECT CASE st.wDayofWeek
                      CASE 0??,  6??    ' Sun or Sat
                          ' do nothing
                      CASE ELSE
                          IF nDays > 0 THEN
                             INCR nDaysDone
                          ELSE
                             DECR nDaysDone
                          END IF
                  END SELECT
              LOOP
          
              ' when we get here, SystemTime contains the ending date, so convert to CCYYMMDD
          
              FUNCTION = FORMAT$(st.wYear, "0000") & FORMAT$(st.wMonth, "00") & FORMAT$(st.wDay, "00")
          
          
          END FUNCTION
          All you have to do to account for holidays is in here.
          Code:
                      CASE ELSE     '  [B]Its not sat or sun [/B]
                          **************************************
                          IF "is a Holiday "  treat it as a Sat or Sun and 
                          do nothing else ; that is, allow the program 
                          to test the next day 
                         ' ***********************************
                          IF nDays > 0 THEN
                             INCR nDaysDone
                          ELSE
                             DECR nDaysDone
                          END IF
          I have found that fast is relative until you get into larger ranges of time and I want this function to be fast under all conditions
          This is plenty fast, and it's not even optimized.. however, I really could not optimize this if I had to check for holidays.

          But in your design, opting to store holiday rules instead of holiday dates, you could just build a table of all holidays in the range mindate to maxdate once, and scan it to see if you have a hit. Should be plenty quick.

          MCM
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            Also if you wish to maintain compatability with other major versions of basic you can also use SystemTimeToVariantTime and its logical reverse VariantTimeToSystemTime. Surprisingly it doesn't use any variants.

            Comment


            • #7
              Date and Time

              Thanks to all for the feedback. It just occurred to me that perhaps I'm viewing things a bit differently since I'm putting together a generic set of routines where I don't know in advance what may be coming at me.

              It seems that this is a code friendly group so I'll put some of code snippets up as things come along.

              One of our most obvious differences is that internally, my DLL uses Rata Die dates which is just a 32-bit integer representing the number of days since Jan 1, 1, my TIME type is a 32 bit integer representing the number of milliseconds since midnight and my DATETIME is a 64-bit integer representing the number of milliseconds since Jan 1, 1. Internally, I also use 64-bit floating point numbers where the fractional portion represents a portion of a day and I've borrowed and call them MOMENTS. Although I expose functions in my DLL that deal with converting to and from these types, all normal functions expose date/time using separate DWORD's for month, day, year, hour, minute, second and millisecond, weekday, etc.

              Although non-sensical at times, I can handle dates from the year -9999 to +9999 comfortably without using Win32 api calls. I'm not sure if I will also port over all the code that deals with the many other calendars such as Hebrew, Chinese, Islamic, etc. I do have the minimum 3 (Gregorian, Julian (Old Style) and ISO) I most frequently use already in place as well as Christian/Othodox easter calculations and I'll stick in all the routines that handle timezones (I have a rules matrix for 19 different ones) and to/from/calculate UTC vs Local vs Standard vs Apparent time, etc.

              It has been my practice to store only UTC date/times in my databases for locale sensitive columns and convert them to local time for reports/display and sometimes I have to mix several different locales on the same screen/report. Thinking about all this and now I'm a bit fatigued.

              As a PB newbie, no giggles please when you see some of my code.
              ------------------------------------------------------------
              sigpic

              It has come to my attention that certain dubious forces are interpolating their desires in my search for Mom, apple pie and the girl you left behind. Stop it or I'll scream...

              Comment


              • #8
                I doubt they will. For your reference the API's use a similar format of a double with the interger portion being a day count and the fractional portion being the fraction of a day. The difference between them is Filetime represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). VariantTime is not tied to a particulat time zone and starts with 1 as 31 December 1899 and does not report milliseconds.

                Comment


                • #9
                  Date and Time

                  Here is my first cut at a general purpose way of adding business days to a base date.

                  To help you understand the code a bit better...

                  The dates are 32 bit integers representing the number of days since Jan 1, 1 which is a Monday.

                  I use Sun=0....Sat=6 as my values for the 7 weekdays defined as %SUNDAY - %SATURDAY.

                  The function cmGregorianWeekday is just MOD 7 operation that also handles negative numbers so that the result is always in the range 0-6.

                  cmWeekDayAfter just calculates the next requested day of the week.

                  cmWeekDayBefore just calculates the first prior day of the week.

                  Code:
                   
                  FUNCTION cmGregorianBusinessAddWeekdays (BYVAL dwStart AS LONG, _
                                                           BYVAL dwEnd AS LONG, _
                                                           BYVAL dwSign AS LONG, _
                                                           BYVAL dwSunday AS LONG, _
                                                           BYVAL dwMonday AS LONG, _
                                                           BYVAL dwTuesday AS LONG, _
                                                           BYVAL dwWednesday AS LONG, _
                                                           BYVAL dwThursday AS LONG, _
                                                           BYVAL dwFriday AS LONG, _
                                                           BYVAL dwSaturday AS LONG) AS LONG
                  '
                  ' Add/Subtract days range to adjust for days of the week that are non business days
                  '
                  ' dwSunday through dwSaturday, %TRUE=business day, %FALSE=non business day
                  '
                  ' Returns fixed date representing the range extension either backwards from dsStart
                  ' or forwards from dwEnd
                  '
                  ' dwSign = 1 if range extension is forward from dwEnd, -1 if from dwStart
                  '
                  DIM dwNonBusinessDays       AS LOCAL LONG
                  DIM dwLoop                  AS LOCAL LONG
                     dwNonBusinessDays = -1
                      WHILE dwNonBusinessDays <> 0
                          dwNonBusinessDays = 0
                          IF dwSunday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%SUNDAY,dwStart,dwEnd)
                          END IF
                           IF dwMonday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%MONDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                          IF dwTuesday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%TUESDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                          IF dwWednesday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%WEDNESDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                         IF dwThursday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%THURSDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                          IF dwFriday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%FRIDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                          IF dwSaturday = %FALSE THEN
                              dwNonBusinessDays = cmGregorianWeekdayCount(%SATURDAY,dwStart,dwEnd) _
                                                + dwNonBusinessDays
                          END IF
                  ' If there are some non business weekdays found, adjust the range for next pass
                          SELECT CASE dwNonBusinessDays
                          CASE > 0
                  ' Test for which end of the range to adjust, make the adjustment and reset the new range
                              SELECT CASE dwSign
                              CASE = 1
                                 SELECT CASE dwEnd - dwStart + 1
                                      
                                  CASE <> dwNonBusinessDays           ' If all the days in the range are non-business
                                      dwStart = dwEnd + dwNonBusinessDays
                                      dwEnd = dwEnd + 1
                                      
                                  CASE ELSE
                                      
                                      dwStart = dwEnd + 1             ' Loop through one day at a time until a business day is found
                                      dwEnd = dwStart
                                      
                                  END SELECT
                              CASE ELSE
                                 SELECT CASE dwEnd - dwStart + 1
                                      
                                  CASE <> dwNonBusinessDays
                                      dwEnd = dwStart - dwNonBusinessDays
                                      dwStart = dwStart - 1
                                      
                                  CASE ELSE
                                      dwEnd = dwStart - 1             ' Loop through one day at a time until a business day is found
                                      dwStart = dwEnd
                                      
                                  END SELECT
                              END SELECT
                  ' Reverse to reset range
                              SWAP dwStart, dwEnd
                          END SELECT
                      WEND
                      FUNCTION = IIF(dwSign = 1,dwEnd,dwStart)        ' Check which end of the range to return
                  END FUNCTION
                  FUNCTION cmGregorianWeekdayCount (BYVAL dwWeekday AS LONG, _
                                                    BYVAL dwStart AS LONG, _
                                                    BYVAL dwEnd AS LONG) AS LONG
                  ' Calculate the number of ocurrences of dwWeekDay (%SUNDAY - %SATURDAY) in the
                  ' date range provided       
                  DIM dwCount                 AS LOCAL LONG
                  ' Start date must be less or equal to ending date
                      SELECT CASE dwStart
                      CASE > dwEnd
                          SWAP dwStart, dwEnd
                      END SELECT
                  ' If Start date and ending date are equal, return 0 days
                      SELECT CASE dwStart
                      CASE = dwEnd
                          dwCount = 0
                      CASE ELSE
                          dwCount = INT(ABS(cmWeekdayAfter(dwWeekday,dwEnd) _
                                 - cmWeekdayBefore(dwWeekday,dwStart)) / 7) _
                                 - 1
                      END SELECT
                      FUNCTION = dwCount
                  END FUNCTION
                  A quick example:

                  Add 20 business days to April 1, 2009, Sat/Sun are non business weekdays

                  April 1, 2009 = 733498 + 20 = 733518 or April 21, 2009

                  Calling the function as:

                  cmGregorianBusinessAddWeekdays (733498, _
                  733518,
                  1, _
                  0, _ ' Sunday
                  1, _
                  1, _
                  1, _
                  1, _
                  1, _
                  0) ' Saturday

                  The first pass through the WHILE/WEND loop in cmGregorianBusinessAddWeekdays finds 3 Sundays and 3 Saturdays for a total of 6 non business days. The inspected date range is adjusted to 733519 (April 22, 2009) through 733524 (April 27, 2009).

                  The second pass through the WHILE/WEND loop finds 1 Sunday and 1 Saturday for a total of 2 non business days for the April 22-27, 2009 range. The inspected date range is adjusted to 733525 (April 28, 2009) through 733526 (April 29, 2009).

                  The thrid pass through the WHILE/WEND loops finds 0 Sunday and 0 Saturday for a total of 0 (zero). We are done and the answer is April 29, 2009.

                  Before this function is called, the range has to adjusted to be sure the lower part of the range is passed first and that at least one weekday is specified as a normal business day (or a loop race will result). One special case had to be handled in cmGregorianBusinessAddWeekdays to handle the situation where the entire initial range is composed of non-business days. In that case, we just revert to looping one day at time to find the next business work day.

                  I'm out of state for the coming week. Enjoy!

                  Rick
                  Last edited by Rick Kelly; 26 Apr 2009, 04:34 AM.
                  ------------------------------------------------------------
                  sigpic

                  It has come to my attention that certain dubious forces are interpolating their desires in my search for Mom, apple pie and the girl you left behind. Stop it or I'll scream...

                  Comment

                  Working...
                  X