图数据库Cypher查询中如何按日期属性正确实现降序排序？

我有一个包含约20个顶点、60条边的图，顶点分为Person和Merchant两类，边代表交易，存储了交易状态和格式为"mm/dd/yyyy"的交易日期。尝试通过以下Cypher查询获取状态为"Disputed"的交易并按交易日期降序返回，但结果未正确排序——12月的数据排在了最后。

原查询语句及结果：

SELECT * FROM cypher('FraudDetection', $$
        MATCH (victim :Person)-[r :HAS_BOUGHT_AT]->(merchant)
        WHERE r.status = "Disputed"
        RETURN victim.name, merchant.name, r.amount, r.time ORDER BY r.time DESC
$$) AS (Customer agtype, Store agtype, Amount agtype, Transaction_Time agtype);

 customer  |       store        | amount | transaction_time 
-----------+--------------------+--------+------------------
 "Olivia"  | "RadioShack"       | "1884" | "8/1/2014"
 "Olivia"  | "Urban Outfitters" | "1152" | "8/10/2014"
 "Madison" | "Apple Store"      | "1925" | "7/18/2014"
 "Olivia"  | "Apple Store"      | "1149" | "7/18/2014"
 "Marc"    | "Apple Store"      | "1914" | "7/18/2014"
 "Paul"    | "Apple Store"      | "1021" | "7/18/2014"
 "Madison" | "RadioShack"       | "1368" | "7/1/2014"
 "Madison" | "Urban Outfitters" | "1374" | "7/10/2014"
 "Marc"    | "Urban Outfitters" | "1424" | "5/10/2014"
 "Paul"    | "Urban Outfitters" | "1732" | "5/10/2014"
 "Paul"    | "RadioShack"       | "1415" | "4/1/2014"
 "Marc"    | "RadioShack"       | "1721" | "4/1/2014"
 "Madison" | "Macys"            | "1816" | "12/20/2014"
 "Olivia"  | "Macys"            | "1790" | "12/20/2014"
 "Marc"    | "Macys"            | "1003" | "12/20/2014"
 "Paul"    | "Macys"            | "1849" | "12/20/2014"
(16 rows)

-- The month of December is the last to appear...

问题原因 #

r.time是字符串类型，排序时按字典序而非实际日期逻辑比较。字符串中"12/20/2014"以"1"开头，会比以"4""5""7""8"开头的日期字符串小，因此降序排序时会排在末尾。

正确的查询方式 #

需要将字符串格式的日期转换为日期类型后再排序，使用Cypher的date()函数并指定日期格式（"MM/dd/yyyy"，其中大写MM代表月份）：

SELECT * FROM cypher('FraudDetection', $$
        MATCH (victim :Person)-[r :HAS_BOUGHT_AT]->(merchant)
        WHERE r.status = "Disputed"
        RETURN victim.name, merchant.name, r.amount, r.time 
        ORDER BY date(r.time, "MM/dd/yyyy") DESC
$$) AS (Customer agtype, Store agtype, Amount agtype, Transaction_Time agtype);

说明 #

通过date(r.time, "MM/dd/yyyy")将"mm/dd/yyyy"格式的字符串转换为标准日期类型，排序时会按照年→月→日的逻辑进行降序排列，12月的数据会正确排在最前面。

内容的提问来源于stack exchange，提问作者Matheus Farias