Announcement

Collapse
No announcement yet.

Date and Time

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

  • Rick Kelly
    replied
    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, 03:34 AM.

    Leave a comment:


  • John Petty
    replied
    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.

    Leave a comment:


  • Rick Kelly
    replied
    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.

    Leave a comment:


  • John Petty
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • Rick Kelly
    replied
    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

    Leave a comment:


  • Rick Kelly
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • Rick Kelly
    started a topic Date and Time

    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
Working...
X