D3 and mvBase

 View Only
  • 1.  UTC Date format

    Posted 06-09-2021 21:49
    Edited by David Andrews 06-25-2021 17:48

    Generating a date in UTC Date Format

    The data format for an external entity we were supplying data to had changed. They changed the date format from the international YYYYMMDD format to the UTC format. D3 does not have an intrinsic conversion for this format, so I had to come up with a way of generating this date format.

    After some research, I found a command using PowerShell that could provide me with this date format, but on execution from within D3 I found it to be too slow, especially in a loop. I needed to use only D3 to create the date format to ensure optimal speed.

    One of the things to keep in mind is whether daylight savings time is in effect (or not). Again, D3 does not provide this info and calling a Powershell script was just too slow. I had to find a way to store a flag in D3 which could be used by a D3 subroutine to know about daylight savings time (DST) and produce the date in UTC format. My idea was to set a DST flag in D3 once a day when the user logs in. This flag can then be read as often as required by D3 with little effect on a loop.

    This lead me to further research and I found a Python script to determine if DST is in effect. I made a few modifications specifically for our time zone in Adelaide, Australia:

    # daylightsavingstest.py

    #

    # Python script to determine whether or not daylight savings is in effect.

    #

    # Returns the values True or False.

    #                                                                        

    # 22/09/2020 WHK with some help from the internet... :)

    #

    import datetime                                                          

    import pytz                                                               

                                                                             

    def is_dst(dt,timeZone):                                                 

       aware_dt = timeZone.localize(dt)                                      

       return aware_dt.dst() != datetime.timedelta(0,0)                      

                                                                             

    timeZone = pytz.timezone("Australia/Adelaide")                           

                                                                              

    dt = datetime.datetime.now()                                             

    print( is_dst(dt,timeZone))                                              

     

    We're using D3 V9.2.2 and don't have easy access to running Python scripts, so I created a DOS batch wrapper to invoke the script.

    @echo off                                                                         

    rem                                                                               

    rem Invoke a Python script that checks if Daylight Savings Time is in effect.     

    rem Returns "True" or "False".                                                    

    rem                                                                               

    rem 23/09/2020 WHK                                                                

    rem                                                                               

    rem Note: This method was chosen because when D3 shells out it uses the pick user's

    rem       environment variables and the PATH does not include the path to Python. 

    rem       This batch file adds the Python path before executing the Python script 

    rem       to determine if daylight savings time is in effect.                     

    rem                                                                                

    SET PATH=%PATH%;C:\Users\wkadmin\AppData\Local\Programs\Python\Python37-32\       

    Python c:\scripts\daylightsavingstest.py

     

    This can be invoked using the D3 shell prefix (!) and a BASIC EXECUTE command like this:

    VERB = '!c:\Scripts\is_dst'

    EXECUTE VERB CAPTURING TEXT

    DST = (TEXT = 'True')      

     

    Now that I have the DST flag, I can save it to a control file and read it as often as I like with little impact on a loop's speed. The above code was embedded in the login program and the flag written to a system control file item, e.g. SDCONTROL DAYLIGHT.SAVINGS.TIME.FLAG.

    From here on it's fairly easy to create the UTC time formatted date. See the subroutine below:

    PSS.GEN.UTC

    001      SUBROUTINE PSS.GEN.UTC( UTC.DATE )

    002 *

    003 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    004 *

    005 *   Subroutine:  PSS.GEN.UTC

    006 *

    007 *  Description:  Convert a date to Universal Time Code (UTC) or provide

    008 *                today's date/time as UTC.

    009 *

    010 *      Author :  Walter H. Kiess

    011 *      Dated  :  23/09/2020

    012 *      Version:  2.0

    013 *

    014 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    015 *

    016 * Parameters

    017 * ==========

    018 *

    019 * INPUT:   UTC.DATE - <1> Date /time to convert to UTC. Date may be in

    020 *                         OCONV format, e.g. 31/12/2018, or

    021 *                         31/12/2018 8:34:33 or internal date only format

    022 *                         e.g. 18524

    023 *                     <2> Optional: "FRAC" to append fraction of a second

    024 *

    025 * OUTPUT:  UTC.DATE - Converted date/time

    026 *

    027 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    028 *

    029 * Revisions:

    030 *

    031 * WRnnnn: dd/mm/yy xxx Description

    032 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    033 *

    034 $options ext

    035 *

    036      IF NOT(ASSIGNED(UTC.DATE)) THEN UTC.DATE = ''         ;* Prevent errors, default to today.

    037 *

    038 * Set timezone offset according to daylight savings time setting

    039 *

    040      CALL PSS.IS.DST( DST )                                ;* Is Daylight Savings Time in effect?

    041      IF DST THEN

    042         TZ.OFFSET = 10.5

    043      END ELSE

    044         TZ.OFFSET = 9.5

    045      END

    046 *

    047      IF UTC.DATE<2, 1, 1> = 'FRAC' THEN

    048         UTC.DATE = UTC.DATE<1, 1, 1>

    049         FRAC = 1                                           ;* Add fractions of a second to time

    050      END ELSE

    051         FRAC = 0

    052      END

    053 *

    054 * Check if supplied date is Null, ICONV or OCONV format and set to ICONV format

    055 *

    056      IF UTC.DATE = '' THEN                                 ;* No date supplied, use today.

    057         IDATE = DATE()

    058         ITIME = TIME()

    059      END ELSE

    060         IF UTC.DATE = UTC.DATE'MCN' THEN                   ;* ICONV format, save it.

    061            IDATE = UTC.DATE

    062            ITIME = TIME()

    063         END ELSE                                           ;* OCONV format, convert it.

    064            IF INDEX(UTC.DATE, ' ', 1) THEN                 ;* A time was also specified,

    065               OTIME = FIELD(UTC.DATE, ' ', 2)              ;*  extract time...

    066               ITIME = ICONV(OTIME, 'MT')                   ;*  convert time to ICONV

    067               UTC.DATE = FIELD(UTC.DATE, ' ', 1)           ;*  extract date...

    068               IDATE = ICONV(UTC.DATE, 'D')                 ;*  convert date to ICONV

    069            END ELSE                                        ;* No time specified,

    070               IDATE = ICONV(UTC.DATE, 'D')                 ;*  use current time.

    071               ITIME = TIME()

    072            END

    073         END

    074      END

    075 *

    076      ITIME -= TZ.OFFSET * 60 * 60                          ;* Apply timezone offset

    077      IF ITIME <= 0 THEN                                    ;* Adjust time to yesterday

    078         ITIME += 86400

    079         IDATE -= 1

    080      END

    081 *

    082 * Assemble UTC date/time

    083 *

    084      UTC.DATE = OCONV(IDATE, 'D4Y')'R%4': '-': OCONV(IDATE, 'DM')'R%2': '-': OCONV(IDATE, 'DD')'R%2': 'T': OCONV(ITIME, 'MTS'):

        'Z'

    085 *

    086      RETURN

    087 *

    088 *~~~~~~~~~~~~~~~~~~~~~~~~ END OF SUBROUTINE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     

    Note the use of a subroutine to read the flag. I did this as the original implementation used a PowerShell script to do this but I found it too slow when used in a loop. I didn't want to break any other functionality in the system so I simply replaced the functionality of the subroutine to yield the same, but faster, result.

    PSS.IS.DST

    001      SUBROUTINE PSS.IS.DST( OUT.DST )

    002 *

    003 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    004 *

    005 *   Subroutine:  PSS.IS.DST

    006 *

    007 *  Description:  Is Daylight Savings Time in effect?

    008 *

    009 *      Author :  Walter H. Kiess

    010 *      Dated  :  23/09/2020

    011 *      Version:  1.1

    012 *

    013 * Note: is_dst is a DOS batch file which calls a Python script to determine

    014 *       whether Daylight Savings Time is in effect. The batch file was

    015 *       necessary as it adds the Python path to the environment to enable

    016 *       the system to run the Python script.

    017 *

    018 * Note2: As I was developing this concept I realised this sub could be called

    019 *        many times in a loop depending on it's usage. Shelling out to

    020 *        Windows to check for DST would take too long so I moved the test

    021 *        to the login program (SDBP) SDSGON which now sets the flag

    022 *        (SDCONTROL) DAYLIGHT.SAVINGS.TIME.FLAG to 1=Yes or 0=No. This is

    023 *        much quicker to read using an OCONV than shelling out.

    024 *

    025 *        I decided to keep using the sub as a simple standard way of obtaining

    026 *        the flag without having to remember where the flag was stored.

    027 *

    028 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    029 *

    030 * Parameters

    031 * ==========

    032 *

    033 * OUTPUT:  OUT.DST - Flag indication state of Daylight Savings Time:

    034 *                    0=False, 1=True, i.e. in effect.

    035 *

    036 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    037 *

    038 * Revisions:

    039 *

    040 * WRnnnn: dd/mm/yy xxx Description

    041 *

    042 *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    043 *

    044 $options ext

    045 *

    046 ***  VERB = '!c:\Scripts\is_dst'                           ;* Is Daylight Savings Time in effect?

    047 ***  EXECUTE VERB CAPTURING TEXT

    048 ***  IF TEXT = 'True' THEN

    049 ***     OUT.DST = 1               ;* All this code was transfered to SDBP SDSGON (the login program)

    050 ***  END ELSE

    051 ***     OUT.DST = 0

    052 ***  END

    053 *

    054      OUT.DST = OCONV('DAYLIGHT.SAVINGS.TIME.FLAG', 'TSDCONTROL;X;;1')

    055      OUT.DST = (OUT.DST = '1') ;* Force false in case the flag is missing

    056 *

    057      RETURN

    058 *

    059 *~~~~~~~~~~~~~~~~~~~~~~~~ END OF SUBROUTINE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     

    So there you have it. A simple call to PSS.GEN.UTC( UTC.DATE ) will return a date in UTC format by converting a supplied value or now, if none provided. And as a bonus, a call to PSS.IS.DST( IS.DST ) will tell you if daylight savings time is in effect.

    Hopefully, this will help someone in a similar situation with a solution.

    Disclaimer: Feel free to use this code, as is, with no warranties intended, implied or given, at your own risk.

     



    ------------------------------
    Walter Kiess
    Manager IT
    SA Police Super
    Adelaide SA Australia
    ------------------------------


  • 2.  RE: UTC Date format

    PARTNER
    Posted 06-10-2021 09:01
    Edited by Chris Wolcz 06-10-2021 15:51
    Walter,

    If you would like to avoid calling Python, you could keep an SDCONTROL item with begin and end dates for time changes. I think it will be a low maintenance as you could put several years of information. You could find time change dates e.g. here: https://www.timeanddate.com/time/zone/australia/adelaide

    If a variable would store starting dates (in odd MVs) and ending dates (in even MVs)  consecutively,  a single 'AR' locate with the given date would tell you if you are in the daylight saving or not. You would need to consider the time of the day, if you hit the "time change" date.

    ------------------------------
    Chris Wolcz
    Senior Software Developer
    EXEControl Global Solutions
    Clifton Park NY United States
    ------------------------------



  • 3.  RE: UTC Date format

    Posted 06-10-2021 18:17
    Thanks for your excellent suggestion, Chris.
    We have a similar arrangement in place for the Pay Period End Dates (PPED) for every pay fortnight. I found that because it is only updated once in a decade or so, the users forget the process is there and eventually the system spits out an error saying there are no more fortnights, go and create some more, but they don't know how to do it due to the time lag and change in staff. I guess I could write a phantom or login process that updates the table on a daily or even annual basis, or better still, when it runs out of dates simply create another decade's worth...
    But it is what it is and it's working well, so I will leave it be. There are many more pressing issues, as usual...

    ------------------------------
    Walter Kiess
    Manager IT
    SA Police Super
    Adelaide SA Australia
    ------------------------------