Date vs datetime datatype: differences

[lmattioli1]

After a bit of testing, I think I found that there's only one difference: in Share edit/visualization mode the datetime shows/asks for the time.
In the DB the value is always stored as a datetime WITH a Timezone.

Here's an example. I've three properties associated to the same node_id, the first is a datetime, the other two are date

string_value
2017-12-22T14:56:00.000Z
2017-05-22T22:00:00.000Z
2017-05-17T22:00:00.000Z

Tomcat is setted with Rome Timezone, so +2 hours in SummerTime and +1 hour in Winter.
I've tried both by browser and via CMIS: you get the same result.

Here's the respective values that I've typed

2017-12-22 15:56 (+1 hour)
2017-05-23 (+2 hours)
2017-05-18 (+2 hours)

This mean that if you want to make a query via CMIS on a date field, you've to worry about Timezone.
Is this correct? I don't know, I think that there's no right response to the question.
At least now we're warned.

Personally, I don't like have a Timezone in a Date field, I'd prefer something like the LocalDate object implemented in Java 8 https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html

[afaust · Accepted solution]

I am seriously confused - I believe we had this question first on IRC, then on this platform a couple of days ago or at the end of April and I responded to that, but it can no longer be found. I hope the one who created it did not delete it after getting the answer.

Yes, you do have to take care of the timezone in a CMIS query. The CMIS specification only defines support for datetime values - not date-only values - and the TIMESTAMP utiltiy for CMIS queries always includes a timezone identifier as part of the ISO 8601 notation.

Storing a LocalDate only makes sense if you store some accompanying information about where that local date is valid or hard-code your solution to a very specific place (like in your case Roma). Since Alfresco must support multi-national, multi-regional use cases it cannot do that, so it includes the timezone with a date.

Technically speaking Alfresco supports adding custom data types for properties, so e.g. a "d:localdate" (for very specific use cases where timezone really does not matter) could be implemented. Unfortunately the support for this in other parts of the application stack (e.g. SOLR for search) is insufficient to make this an easy endeavor.