DateUtils error

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

DateUtils error

Postby Harjo » Wed Jun 06, 2018 12:25 pm

Hi there,

we use the DateUtils solution inside our solution, to calculate weeknumbers,
so far so good.

But most of the time, when we open our solution, and the weeknumber is touched in on of our calculations, we get this error:

Code: Select all
jun 06, 2018 12:12:33 PM com.servoy.j2db.util.Debug error
SEVERE: error executing calc: weeknumber
java.lang.RuntimeException: Exceptie bij het uitvoeren van berekening: weeknumber van tabel quotes, fout org.mozilla.javascript.EcmaError: ReferenceError: "calendar" is not defined. (core_DirectManager/scopes/DateUtils/getWeekOfYear#14)
   at com.servoy.j2db.scripting.TableScope.getCalculationValue(TableScope.java:193)
   at com.servoy.j2db.scripting.TableScope.getCalculationValue(TableScope.java:215)
   at com.servoy.j2db.dataprocessing.FoundSet.getCalculationValue(FoundSet.java:3403)
   at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:206)
   at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:169)
   at com.servoy.j2db.smart.dataui.CellAdapter$5.run(CellAdapter.java:1185)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
Caused by: org.mozilla.javascript.EcmaError: ReferenceError: "calendar" is not defined. (core_DirectManager/scopes/DateUtils/getWeekOfYear#14)


if we look this up inside the DateUtils scope, we see this:

Code: Select all
/**
* A java.util.Calendar instance used to do the math
* @private
* @properties={typeid:35,uuid:"6FEE1C0C-ECD0-4E98-A5BA-5420B77D0917",variableType:-4}
*/
var calendar = java.util.Calendar.getInstance();



/**
* @properties={typeid:24,uuid:"A92A0657-35C4-4ECC-8F5C-8A7D2C5C0CBF"}
*/
function getWeekOfYear(date) {
   calendar.setTimeInMillis(date.getTime());
   return calendar.get(java.util.Calendar.WEEK_OF_YEAR);
}


So somehow, the first time, var calendar is not initialized somehow...
How do we make sure, that this global scope is initialized before we touch a function inside a calculation?
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: DateUtils error

Postby Harjo » Mon Dec 03, 2018 4:07 pm

we still have this same issue...
Does somebody have the solution??
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: DateUtils error

Postby mboegem » Mon Dec 03, 2018 5:11 pm

Hi Harjo,

I've seen the error just once, but it disappeared after restart.
Does it help when you reference "scopes.svyDateUtils" in your onOpenSolution function?
That should just touch the scope without actually executing a function.
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: DateUtils error

Postby Bernd.N » Tue Dec 04, 2018 1:57 am

Harjo wrote:weeknumber is touched in one of our calculations

Do you mean a table calculation?

In case there is no solution, I would do a workaround, like creating a new field for the weeknumber in the table, always updating that field when the date changes, and then using that field in the calculation instead of the function that does not work on startup.

Violating Codd's normal form is needed sometimes... :)
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: DateUtils error

Postby ROCLASI » Wed Dec 05, 2018 9:08 am

Hi Harjo,

Seems that the scoped variable gets garbage collected somehow. Maybe add some sanity checking like so?
Code: Select all
/**
* A java.util.Calendar instance used to do the math
* @private
* @properties={typeid:35,uuid:"6FEE1C0C-ECD0-4E98-A5BA-5420B77D0917",variableType:-4}
*/
var calendar = java.util.Calendar.getInstance();


/**
* @properties={typeid:24,uuid:"A92A0657-35C4-4ECC-8F5C-8A7D2C5C0CBF"}
*/
function getWeekOfYear(date) {
   if (!calendar) {
      calendar = java.util.Calendar.getInstance();
   }
   calendar.setTimeInMillis(date.getTime());
   return calendar.get(java.util.Calendar.WEEK_OF_YEAR);
}


---

Another (fast) way of handeling time functions is using a time dimension table.
Create a table with a date column as the PK, then add columns for year, month, day, weekday, weeknr, quarteryr, halfyr, etc.. and fill it up with data for 2 centuries (1900-2100) which should cover most use-cases (adjust as needed). This will only be 73048 records. Now you only have to join against the date column and you get all the other values in the row without calculating them.
This (data warehousing) technique is also extremely fast with reporting. You just join over the dates and filter your result over the time_dimension columns.

How to create that data? PostgreSQL has a handy function called generate_series. This can generate rows with a lot of different data types. In this case you want to generate timestamps (which we convert to dates), like so:
Code: Select all
SELECT CAST(dateval AS date) date
    , extract('year' from dateval) as year
    , extract('month' from dateval) as month
    , extract('day' from dateval) as day
    , extract('isodow' from dateval) as weekday -- ISO 8601 day of the week numbering (starts with monday)
    , extract('week' from dateval) as weeknr -- ISO 8601 week numbering
    , extract('quarter' from dateval) as quarter_yr
    , ceil(extract('quarter' from dateval)/2) as half_yr
    , extract('doy' from dateval) as day_of_year
    -- add your own columns like accounting calendars like 445, etc.
FROM generate_series('1900-01-01'::timestamptz,
    '2099-12-31'::timestamptz,
    '1 day'
) dateval

We could create a table manually and then insert the data but there is a faster way to do all this in one SQL query:
Code: Select all
CREATE TABLE time_dimension AS (
    SELECT CAST(dateval AS date) date
        , extract('year' from dateval) as year
        , extract('month' from dateval) as month
        , extract('day' from dateval) as day
        , extract('isodow' from dateval) as weekday -- ISO 8601 day of the week numbering (starts with monday)
        , extract('week' from dateval) as weeknr -- ISO 8601 week numbering
        , extract('quarter' from dateval) as quarter_yr
        , ceil(extract('quarter' from dateval)/2) as half_yr
        , extract('doy' from dateval) as day_of_year
        -- add your own columns like accounting calendars like 445, etc.
    FROM generate_series('1900-01-01'::timestamptz,
        '2099-12-31'::timestamptz,
        '1 day'
    ) dateval
)

However this doesn't create the PK on the date column so we need to add it like so:
Code: Select all
ALTER TABLE time_dimension ADD CONSTRAINT time_dimension_pk PRIMARY KEY(date)

You can of course add extra indexes as needed. As this is a static lookup table there is no real 'cost' here other than disk space.

Hope either solution helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: DateUtils error

Postby Harjo » Wed Dec 05, 2018 3:31 pm

mboegem wrote:Hi Harjo,

I've seen the error just once, but it disappeared after restart.
Does it help when you reference "scopes.svyDateUtils" in your onOpenSolution function?
That should just touch the scope without actually executing a function.


I have put just a line in th onSolutionOpen method: scopes.svyDateUtils
and this fixes the issue!

Still a bit strange, because Servoy should 'do' this automaticly.
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: DateUtils error

Postby mboegem » Thu Dec 06, 2018 12:54 am

Seems like a loading order issue.
Maybe Johan can comment on this.

Otherwise create a JIRA ticket so Servoy can look into this.

For now it's good to see it being resolved although only while using a workaround
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: DateUtils error

Postby omar » Thu Dec 06, 2018 11:30 am

As a workaround you can also use this function I wrote as a part of the VFP (Visual FoxPro) plugin. Hope it helps.

Code: Select all
/**
* Returns the week number for the date parameter dDate.
* It supports both ISO 8601 and USA/Canada week numbering.
*
* @param {Date} dDate
* @param {Number} nOverruleFirstDayOfWeek (optional) - 0=Sunday 1=Monday
* @return {Number} number
* @author Omar van Galen
*
* @properties={typeid:24,uuid:"29442D35-3149-4B55-8816-B46C09B0BC71"}
*/
function week(dDate, nOverruleFirstDayOfWeek) {
// Remove time components of date
var justTheDate = new Date(dDate.getFullYear(), dDate.getMonth(), dDate.getDate())
var weekDiff = 0
// determine if ISO 8601 (european) or Canada/USA calculation should be used
if ((!nOverruleFirstDayOfWeek && utils.isMondayFirstDayOfWeek()) || nOverruleFirstDayOfWeek == 1) {
     // ISO 8601 (week 1 contains Jan 4th and first Thursday is in week 1)   Change date to Thursday same week
     var targetThursday = new Date(justTheDate.setDate(justTheDate.getDate() - ((justTheDate.getDay() + 6) % 7) + 3))
     // Take January 4th as it is always in week 1 (see ISO 8601)
     var targetJan4 = new Date(targetThursday.getFullYear(), 0, 4)
     // Change date to Thursday same week
     var firstThursday = new Date(targetJan4.setDate(targetJan4.getDate() - ((targetJan4.getDay() + 6) % 7) + 3))
     // Number of weeks between target Thursday and first Thursday
     weekDiff = (targetThursday - firstThursday) / (86400000 * 7)
} else {
     // Canada/USA (week 1 contains Jan 1st and first Saturday is in week 1)   Change date to Saturday same week
     var targetSaturday = new Date(justTheDate.setDate(justTheDate.getDate() - ((justTheDate.getDay() + 6) % 7) + 5))
     // Take January 1st as it is always in week 1
     var targetJan1 = new Date(targetSaturday.getFullYear(), 0, 1)
     // Change date to Saturday same week
     var firstSaturday = new Date(targetJan1.setDate(targetJan1.getDate() - ((targetJan1.getDay() + 6) % 7) + 5))
     // Number of weeks between target Saturday and first Saturday
     weekDiff = (targetSaturday - firstSaturday) / (86400000 * 7)
}
// return the result   
return 1 + weekDiff   
}
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 10 guests