Document.Query and timezone trouble

[chrisd_]

I'm having trouble getting my head around date-time handling. I have a Nuxeo server, with postgresql backend that i'm accessing using REST and Automation APIs. When I create a document using REST, the returning JSON gives me the lastModified datetime of the document. However, using that exact datetime in Document.Query doesn't get me the document back. The details:

The JSON response on my document creation:

{
    "versionLabel": "0.0", 
    "changeToken": "1385603086000", 
    "uid": "6b371332-6022-4f50-a2c9-8c744d32901f", 
    "repository": "default", 
    "title": "null", 
    "lastModified": "2013-11-28T01:44:58.00Z", 
    "facets": [
        "Downloadable", 
        "Commentable", 
        "Asset", 
        "SuperSpace", 
        "Versionable", 
        "Publishable", 
        "HasRelatedText"
    ], 
    "entity-type": "document", 
    "state": "project", 
    "isCheckedOut": true, 
    "contextParameters": {}, 
    "path": "/default-domain/workspaces/amp/imports/Administrator_2013-11-28/null", 
    "type": "Plate"
}

Since I my local time is GMT+11, and I created this document at 12:44 localtime, I assume what I got back is in UTC. Which kind of surprised me. Now I have a look at the database using psql:

nuxeo=> select id,modified from dublincore where id='6b371332-6022-4f50-a2c9-8c744d32901f';
                  id                  |        modified         
--------------------------------------+-------------------------
 6b371332-6022-4f50-a2c9-8c744d32901f | 2013-11-28 12:44:58.018
(1 row)

So in the database, it seems local time was stored, which again surprised me since I thought Nuxeo would store UTC.

Then I run a query trying to get the document:

curl -H 'Content-Type:application/json+nxrequest' -X POST \
	-d '{"params":{"query":"SELECT * FROM Document WHERE dc:modified BETWEEN TIMESTAMP \"2013-11-28T01:44:57.00Z\" AND TIMESTAMP \"2013-11-28T01:44:59.00Z\""}}' \
	-u Administrator:Administrator http://192.168.0.125:8080/nuxeo/site/automation/Document.Query

Which returns an empty list. However, running the query with the time as it is stored:

curl -H 'Content-Type:application/json+nxrequest' -X POST \
	-d '{"params":{"query":"SELECT * FROM Document WHERE dc:modified BETWEEN TIMESTAMP \"2013-11-28T12:44:57.00Z\" AND TIMESTAMP \"2013-11-28T12:44:59.00Z\""}}' \
	-u Administrator:Administrator http://192.168.0.125:8080/nuxeo/site/automation/Document.Query

returns the document. Now for the modified date it's not really a problem to me, but I am also pushing in a custom datetime field and that gets the same behaviour. I find it very inconsistent that when I create a document with a date-field, 11 hours get added to that field but when I query on the same field, i'm supposed to add the 11 hours manually especially since the client may not be aware of the server's timezone...

I am probably missing something here. How can I get the Document.Query to work consistently for me in terms of what get's communicated over the interface?

Thanks in advance, Chris

[Florent_Guillau · Accepted solution]

Yes there is an inconsistency right now, as indeed the client has to be aware of the server timezone when doing queries. This is due, as you found out, to the fact that the dates are stored in localtime without timezone in the database. This, in turn, is due to a design decision dating back to the early days of Nuxeo.

There is a ticket open to improve this, NXP-12220, but it's not currently scheduled.

[chrisd_]

Thanks Florent, at least now I know this is intended behaviour. Is it possible for the client to easily retrieve the server's timezone? At least that would let me work around this in a more or less consistent way.

[Florent_Guillau]

I don't believe we have such a feature right now.