ISO8601 Date format in SQLquery

[anschoewe]

I've seen a decent amount of discussion about date/time handling.  I'm wondering if a solution has been delivered.  I'm currently trying to archive records from Salesforce.com. 
Date/times coming from records in Salesforce are stored in the following format:

2009-12-17T20:03:00.000Z

I would like to perform a SQL query from the OrientDB console like:

Select Id from Account Where CreatedDate < '2009-12-17T20:03:00.000Z'

I was playing with .asDateTime() and format() but I couldn't quite figure it out.  Is there a workaround or, even better, an elegant solution to this problem?  It seems like a common issue.

Btw, today was my first exposure to OrientDB.  I'm very impressed.  If querying works as advertised, this could really make my life easy for archiving a few gigs of records.

Thanks in advance for any help you might provide,

Andrew

[Luca Garulli]

Hi,

the key is using the format as you done. Follow this guide to have your format: http://docs.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html

In your case I've tested 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z''

with a simple query:

select sysdate('yyyy-MM-dd'T'HH:mm:ss.SSS'Z'') from POST

and works:

2011-12-03T10:23:46.949Z

Lvc@

[anschoewe]

Thank you for your help.  Unfortunately, I don' think it's actually doing a comparison as I would expected.  Give the following query:

select Id, vr__Mail_Date__c from vr__VR_Email_History_Contact__c Where vr__Mail_Date__c.format('yyyy-MM-dd'T'HH:mm:ss.SSS'Z'') < '2002-12-03T00:00:00.000Z' LIMIT 1

I'm returned this result:

{
  "@type": "d", "@version": 0,
  "Id": "a0e70000000cXcdAAE",
  "vr__Mail_Date__c": "2010-01-25T21:18:00.000Z"
}

As you can see, the date in the object returned is not less than the date in the where clause.  The date comparison is ignored.  In fact, if I remove the 'limit' portion of the query, it returns all the records of that class.  Am I missing something?

Andrew

[Luca Garulli]

Hi,

I've fixed a bug on formatting of dates, now works!

Furthermore I've added a new date() function to use in this way:

select data from Account where data < date('2011-12-03T00:00:00.000Z', 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'')

SVN r4297.

Lvc@

[anschoewe]

I was able to get this working with the following query:

select from vr__VR_Email_History_Contact__c Where date(vr__Mail_Date__c, 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'') < date('2009-02-19T22:30:01.000Z', 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'')

Now I have an optimization question.  Before inserting my documents, I specified a property and index in order to speed-up query.

        OClass clz = db.getMetadata().getSchema().createClass("VR_Email_History_Contact__c");
        db.command(new OCommandSQL("CREATE PROPERTY VR_Email_History_Contact__c.vr__Mail_Date__c DATETIME"));
        db.command(new OCommandSQL("CREATE INDEX VR_Email_History_Contact__c.vr__Mail_Date__c NOTUNIQUE"));

Is the above query using the index?
By having to wrap the field vr__Mail_Date__c in the date() function, am I losing the ability to use the index?  My guess is that this would slow things down tremendously.  I tried removing the date() function around my vr_Mail_Date__c field, but I receive exception about 'error on conversion of date '2010-01-25T00:00:00.000Z' using the SimpleDateFormat''.

I'm doing some queries to isolate records by dates (in my where clause) and they seem a little slow.  It's taking about 15 secs to retrieve the 4 records that match the above criteria, from 100,000 records.  Is there a better way to optimize this query?  I'm not even positive I need to convert to dateTime fields since sorting by String values might be sufficient for this date format.

Thanks,

Andrew

[Luca Garulli]

Hi,

that query will not use index.

Can you try this?

select from vr__VR_Email_History_Contact__c Where vr__Mail_Date__c < date('2009-02-19T22:30:01.000Z', 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'')

Or a way that works at 100% is to convert the date in UTC format by your application and just make:

select from vr__VR_Email_History_Contact__c Where vr__Mail_Date__c < 38653262156132567

Supposing that 38653262156132567 is the long UTC representation of the date you want. This is super fast.

Lvc@

[Sfinx]

Hi,

Is it must be true for 1.3.0 too ?

........

OrientDB console v.1.3.0-SNAPSHOT (build @BUILD@) www.orientechnologies.com

Type 'help' to display all the commands supported.

Installing extensions for GREMLIN language v.2.2.0-SNAPSHOT

orientdb> connect remote:/sfinx admin admin                      

Connecting to database [remote:/sfinx] with user 'admin'...OK

orientdb> select sysdate('yyyy-MM-dd'T'HH:mm:ss.SSS'Z'') from 0:1

---+---------+--------------------

  #| RID     |sysdate             

---+---------+--------------------

  0|    #-2:0|2012-10-17 12:03:51 

---+---------+--------------------

1 item(s) found. Query executed in 0.006 sec(s).

orientdb> 

...........

Seems like do not work here.

Rus

суббота, 3 декабря 2011 г., 12:25:36 UTC+2 пользователь Lvc@ написал:

[Luca Garulli]

Fixed in SVN r6989.

Lvc@

--
 
 
 

[Sfinx]

Thanks !

среда, 17 октября 2012 г., 12:28:29 UTC+3 пользователь Lvc@ написал: