1

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 ...

bashaus
  • 1,614
  • 1
  • 17
  • 33

3 Answers3

0

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

Ducaz035
  • 3,054
  • 2
  • 25
  • 45
0

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
Vlad Andronache
  • 325
  • 3
  • 6
0

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

bashaus
  • 1,614
  • 1
  • 17
  • 33