Date Find/Search Returns One Extra Day

Questions, tips and tricks and techniques for scripting in Servoy

Date Find/Search Returns One Extra Day

Postby kwpsd » Wed Mar 16, 2016 1:20 am

Servoy version 7.4.6

When searching a foundset for records having a specific date expression, the records returned contain those dates in the expression PLUS one extra day. Below is a code snippet asking for records having a billing date on or prior to 02/29/2016.

Code: Select all
    if ( fs.find() )
    {
        fs.billing_date = "<=#02/29/2016|MM/dd/yyyy"

        fs.search()
       
        ...
       
    }   


The very first record in the resultant foundset has a billing date of 03/01/2016 (and there are a few others). Below is a screen shot from the debugger showing the billing date of that first record.

<edit>

record_wrong_date.png
Date outside of search parameters
record_wrong_date.png (2.53 KiB) Viewed 13075 times


This is happening throughout our solution in non-related areas. Has anyone else come across this?
Last edited by kwpsd on Fri Mar 18, 2016 6:15 pm, edited 1 time in total.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby mboegem » Wed Mar 16, 2016 2:28 am

Hi Kim,

did you look in the performance data to see what query is executed on the sql server?
If not please do so and see if that makes any sense looking at the strange results you see.

Best is to clear performance data first, then run this particular find, then refresh performance data and lookup the query.

Hope this helps
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: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Date Find/Search Returns One Extra Day

Postby kwpsd » Wed Mar 16, 2016 7:48 am

Hi, Marc.

I hope you are doing well!

Thanks for the tip...I will certainly check the performance data to see if anything is amiss. However, I wanted to point out that in my example, not all records with a billing date of 03/01/2016 are returned...just some of them. I am finding this in other areas such as foundsets passed to Jasper Reports (our customers alerted us to this),
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby Bernd.N » Wed Mar 16, 2016 11:39 am

In case you want full control regarding the SQL statement, you could use this to actually create the SQL command

Code: Select all
    var
        /*** @type {JSDataSet} */
        dsData;

       dsData = databaseManager.getDataSetByQuery(server_name, sql_query, arguments, max_returned_rows);

       foundset.loadRecords(dsData);


I heard it could also be faster than a fs.search().

It might also be a problem that you convert with the pattern MM/dd/yyyy.
We use to_char(date, 'yyyy-mm-dd') < '2016-03-01' to be sure that any date comparison works fine.
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: Date Find/Search Returns One Extra Day

Postby ROCLASI » Wed Mar 16, 2016 12:51 pm

Bernd.N wrote:
Code: Select all
    var
        /*** @type {JSDataSet} */
        dsData;

       dsData = databaseManager.getDataSetByQuery(server_name, sql_query, arguments, max_returned_rows);

       foundset.loadRecords(dsData);


I heard it could also be faster than a fs.search().

That would be a myth.

Using databaseManager.getDataSetByQuery() and then foundset.loadRecords() you are firing 2 queries to the database instead of just 1.
And the fact that the foundset only needs to filter on primary keys (which is fast) doesn't negate the fact that the first query did do a search on non-PK values.
Also loading a dataset with more than 200 values will result in the use of a temp table, which is not fast at all.

Now if you would use SQL directly in the foundset.loadRecords() then it could indeed be faster depending on the complexity of the search.
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: Date Find/Search Returns One Extra Day

Postby kwpsd » Fri Mar 18, 2016 6:26 am

@Marc: The performance data did not reveal anything unusual. In fact, the Servoy generated where clause was identical to the one I created in sql editor.

@Bernd: It is not the date format. I tried several formats, and the results were the same.


I created a solution that demonstrates this odd behavior. It is not the find/search generated sql that is the problem, it is the parameters that are passed to the sql. I suspect the end-of-month date calculations are not handled properly.

If interested, the solution is posted in Servoy support.


SVY-9605 Find/Search Date Expression Broken https://support.servoy.com/browse/SVY-9605
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby Bernd.N » Fri Mar 18, 2016 2:13 pm

kwpsd wrote:I suspect the end-of-month date calculations are not handled properly

Hard to imagine that Servoy does any special calculation for end-of-month dates. All dates should be converted into those milliseconds since 1.1.1970, regardless if they happen to be end of month or not.

In your case, you wrote "Servoy will return all records on or prior to 08/31/2014 PLUS the next days records as well".
So is Servoy returning in this case all records with date 04/01/2014, but not 04/02/2014 ?

Regarding your billing dates in the field billing_date, do they have hours and seconds or are those always zero?
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: Date Find/Search Returns One Extra Day

Postby Andrei Costescu » Fri Mar 18, 2016 3:07 pm

I am curious if this is not somehow caused by a timezone/DST difference. Between something and something else :) (client-server-db)
What are the timezones of the client/server/db server? Are the extra dates you get consistent with such a time zone difference (so only off by max one extra hour for example if the time zone difference is 1 hour)

What is the value of "servoy.use.client.timezone" property in your servoy.properties file? Does it change anything if you set that to true or to false?
(If any of this gives useful info we should add it to the case as well)
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Date Find/Search Returns One Extra Day

Postby kwpsd » Fri Mar 18, 2016 6:18 pm

Regarding your billing dates in the field billing_date, do they have hours and seconds or are those always zero?


The time elements of the dates are zero.

What is the value of "servoy.use.client.timezone" property in your servoy.properties file?


servoy.use.client.timezone = true

Does it change anything if you set that to true or to false?


Changing servoy.use.client.timezone = false gave the same results.

Below is a screen shot of the test program:

test_dates_results.png
Test program results
test_dates_results.png (50.78 KiB) Viewed 13074 times
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby Bernd.N » Fri Mar 18, 2016 7:00 pm

In the first answer, Marc gave the tip to look at the performance data, there should be the SQL command.
Could you copy that SQL query here?
I would also be interested what happens when you go to the SQL server directly and fire that same SQL query there.

My feeling is that Andrei's timezone tip goes to the right direction. When I create a datetime field in Servoy and then look at the postgres table, it appears there without time zone:
ci_date timestamp without time zone
In case you do not need the time zones for the billing date, could you try to setup a date without time zone and check if that helps?
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: Date Find/Search Returns One Extra Day

Postby patrick » Fri Mar 18, 2016 11:15 pm

I have had the same problem a few years ago. In my case it was the JDBC driver. It was MS SQL Server and with a newer version (2012, 2014) microsoft introduced a newer date data type that the driver I was using didn't handle correctly. It seemed to round milliseconds. When you do a date only search (#) Servoy generates a between statement (between 'date 00:00:00' and 'date 23:59:59:999') and the driver oddly rounded the upper bounds to the next day. Got the exact right driver for the DB version and the problem was fixed.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Date Find/Search Returns One Extra Day

Postby kwpsd » Fri Mar 18, 2016 11:50 pm

Patrick,

We are using the Servoy supplied JTIDS driver:

Code: Select all
net.sourceforge.jtds.jdbc.Driver


with MSSQL 2012 and 2014. Is this the driver that exhibits the rounding problem? With which MSSQL data type were you having problems, 'datetime', 'datetime2', or both (our tables are using 'datetime' only)? What is the 'right driver'?
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby kwpsd » Sat Mar 19, 2016 12:28 am

I just downloaded and installed Microsoft's JDBC driver 'sqljdbc42.jar' and the problem went away!

Thank you so much, Patrick...I cannot tell you how long I have been fighting this problem. Drinks and dinner on me!

Suggestion: Servoy should warn developers about using the JTIDS driver with MSSQL.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Date Find/Search Returns One Extra Day

Postby patrick » Sat Mar 19, 2016 12:46 pm

I'm glad it helped. In my case it was the older Microsoft driver, but I think I had found out earlier that jtds suffers from the same problem. Servoy doesn't ship the latest version of the driver, maybe that would fix this issue, too. It would be nice if you could test that and create a case in Jira whatever your findings are. If it doesn't work with the latest version either we should investigate that. You can download the current version of jtds from here: https://sourceforge.net/projects/jtds/files/
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Date Find/Search Returns One Extra Day

Postby kwpsd » Sat Mar 19, 2016 6:47 pm

I am ecstatic that this problem has been solved. I could demonstrate the strange behavior but could not pin-point its source. One millisecond of rounding error never occurred to me.

Thank you to all who responded! I really appreciate your suggestions and insights.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to Methods

Who is online

Users browsing this forum: No registered users and 7 guests

cron