Magnolia JCR-SQL2 order by Date

1
votes

In the JCR, I've noticed that dates are stored in the format Feb 19, 2015 12:00:00 AM. This means that when you try and order a query by a date, it doesn't seem to work:

SELECT * FROM [mgnl:pages] ORDER BY articlePublishedDate

Will return:

  • Apr 1, 2015 12:00:00 AM
  • Dec 1, 2015 12:00:00 AM
  • Feb 1, 2015 12:00:00 AM

Is there any way to make the ORDER BY clause act as a integer? I've tried CAST(articlePublishedDate AS LONG) but it appears my content repository doesn't like it ...

3
magnoliajcr-sql2

3 Answers

0
votes

This is more issue of JCR than Magnolia , however, one may do the following for working this around.

SELECT p.* FROM [mgnl:page] AS p
WHERE p.[mgnl:lastModified] > CAST('2016-06-10T07:24:50.233Z' AS DATE)

I assume order by also should work the same way.

Cheers

0
votes

Make sure articlePublishedDate node property is of type Date, not String. For example, the following JCR2 query returned the results in the correct order when executed on the website repository:

select p.* from [mgnl:page] as p order by p.[jcr:created] desc
0
votes

Ended up sorting in code, as it wasn't supported by my implementation of JCR.