Problem:

When using the date and time pattern for formatting and parsing out the date it provides an incorrect result and returned unexpected date.

For e,g:

Query  "formatDate(_messagetime, "YYYYMMddHHmmss") as formattedDate" returns incorrect results and obtained an unexpected date.

If the message time is "2017-12-31 23:59:59.000 +0900", it returned the result incorrectly as "20181231235959" in place of "20171231235959".

Resolution:  

This unexpected date in the query is due to the wrong format used for the year.

Please use lower case yyyy for the year.

formatDate "YYYYMMddHHmmss"  should be changed to "yyyyMMddHHmmss" which should resolve the issue.

You can follow this link: SimpleDateFormat that can help in choosing the correct format.