Welcome to the new Gigaspaces XAP forum. To recover your account, please follow these instructions.

Ask Your Question
0

Date format clarification for SQLQuery

http://www.gigaspaces.com/wiki/displa...

If I have a java.util.Date object written in space and I have the following set -

space-config.QueryProcessor.date_format=yyyy-MM-dd space-config.QueryProcessor.datetime_format=yyy-MM-dd HH:mm:ss space-config.QueryProcessor.time_format=HH:mm:ss

How can I specify that I want to match on just the date portion in my sql or the complete date+time portion?

I need to construct a query where in I want to just match on the date portion of java date objects.

SQLQuery<myrequest> query = new SQLQuery<myrequest>(MyRequest.class, "Id = ? and myTimeStamp >= ? and myTimeStamp <= ? ", Id, startDate, endDate);

startDate and endDate are java.util.Date objects.

The above query fails to return anything when startDate and endDate are the same date for example Nov 11 2010 00:00:00, because the space has timestamps stored.

Any suggestions?

{quote}This thread was imported from the previous forum. For your reference, the original is [available here|http://forum.openspaces.org/thread.jspa?threadID=3541]{quote}

asked 2010-11-11 07:53:52 -0500

nitinmalik gravatar image

updated 2013-08-08 09:52:00 -0500

jaissefsfex gravatar image
edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

This works for me:

    static SQLQuery<IDeal> getDealQuery(String className , Calendar c)
    {
        SQLQuery<IDeal> dealQuery = new SQLQuery<IDeal> (className,"creationDate >= ? and creationDate <= ?");
        dealQuery.setParameter(1, "2010-" + (c.get(Calendar.MONTH) + 1) + "-" + (c.get(Calendar.DAY_OF_MONTH) - 1) + " 00:00:00");
        dealQuery.setParameter(2, "2010-" + (c.get(Calendar.MONTH) + 1) + "-" + (c.get(Calendar.DAY_OF_MONTH) + 2) + " 00:00:00");
        return dealQuery ;
    }

The space PU includes:

    <os-core:space id="space" url="/./mySpace">
    <os-core:properties>
        <props>
            <prop key="space-config.QueryProcessor.date_format">yyyy-MM-dd HH:mm:ss</prop>
        </props>
    </os-core:properties>
    </os-core:space>

Shay

answered 2010-11-11 08:04:56 -0500

shay hassidim gravatar image
edit flag offensive delete link more

Comments

And that will work for me too. Try to query for startDate <= creationDate <= endDate. Where start and end dates are the SAME day.

In my case creationDate isnt just date, its a timestamp, hence I want to query by just the date portion. For example, creationDate could be 2010-11-10 12:12:12

nitinmalik gravatar imagenitinmalik ( 2010-11-11 08:16:45 -0500 )edit

I've tried both format

<prop key="space-config.QueryProcessor.date_format">yyyy-MM-dd</prop>

as well as

<prop key="space-config.QueryProcessor.date_format">yyyy-MM-dd HH:mm:ss</prop>

no luck with same date queries, if the space has timestamps stored.

Any suggestions?

nitinmalik gravatar imagenitinmalik ( 2010-11-11 10:05:54 -0500 )edit

quote: "Where start and end dates are the SAME day"

Maybe something like this will work for you:
startDate <= creationDate < (startDate + 1)

where "startDate" has no time portion and (startDate + 1) is essentially next day midnight (no time portion again)?

\- Vanio

vanio gravatar imagevanio ( 2010-11-11 10:24:42 -0500 )edit

That is exactly what I have done as a work around.

private static long MILLIS_PER_DAY = 24 * 60 * 60 * 1000; endDate = new Date(endDate.getTime() + MILLIS_PER_DAY - 1);

But, I would prefer to use the dateformat feature if it is advertised as such.

nitinmalik gravatar imagenitinmalik ( 2010-11-11 10:27:56 -0500 )edit

You should use java.sql.Timestamp as part of the space class.
This works for me:

The space class:

public class MyClass {
    java.sql.Timestamp ts ;
    @SpaceIndex(type=SpaceIndexType.EXTENDED)
    public java.sql.Timestamp getTs() {
        return ts;
    }
    public void setTs(java.sql.Timestamp ts) {
        this.ts= ts;
    }
}

The application code:

GigaSpace gigaspace = new GigaSpaceConfigurer (new UrlSpaceConfigurer("/./mySpace")).gigaSpace();
Calendar c = Calendar.getInstance();
Timestamp startDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ), (c.get(Calendar.DAY_OF_MONTH)),0,0,0,0);
Timestamp endDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ), (c.get(Calendar.DAY_OF_MONTH) + 1),0,0,0,0);
SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class , "ts > ? and ts< ?");
query.setParameter(1, startDate);
query.setParameter(2, endDate);
System.out.println("Start Date:" + query.getParameters()[0]);
System.out.println("End   Date:" + query.getParameters()[1]);
MyClass ret[]= gigaspace.readMultiple(query, Integer.MAX_VALUE);

Shay

shay hassidim gravatar imageshay hassidim ( 2010-11-21 18:56:04 -0500 )edit

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2010-11-11 07:53:52 -0500

Seen: 132 times

Last updated: Nov 11 '10