Question 89097 in PostgreSQL Question posted in
The official documentation can be found here.

Order by attribute that contains a date – Postgresql

I have a graph with about 20 vertices and 60 edges. There are two labels for the vertices: one is labelled as Person and the other Merchant. The edges between them represent a transactions and it stores the status of it and the date of the transaction as "mm/dd/yyyy" (month/day/year). I wanted to retrieve the date of each transaction in descending order, but when I query for it, it doesn’t show the dates in descending order:

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

What is the proper way to retrieve the data in descending order?

Tags:

3

Answers


  1. 0

    AGE Cypher does not seem to have a datetime data type, even though it exists in neo4j. Instead of storing the dates as a string, you could perhaps map them to a small custom version of datetime that does arithmetic comparisons. Since you’re only storing the day, month and year, this should be doable.

    Login or Signup to reply.
  2. 0

    I asked the same question before, and i got following answer:

    Agtype cannot store a datetime datatype yet. There is an experimental branch to add datetime support, although its using the Postgres format right now

    https://github.com/AgeDB-Enterprise/agedb/tree/timestamp

    Use this documentation, instead of Neo4Js

    https://www.postgresql.org/docs/current/datatype-datetime.html
    https://www.postgresql.org/docs/current/functions-datetime.html

    Login or Signup to reply.
  3. 0

    You should stores the date of the transaction as "yyyy-MM-DD" (year-month-day), the ISO date format (ISO 8601). This way you could order by transaction_time in descending order.

    My test case:
    Order by transaction time in descending order

    Additionally, you could format the date string like this to get the result formatted as month/day/year instead of year-month-day:

    SELECT *
FROM cypher('graph_name', $$
MATCH (v:Label)
WITH v.transaction_time as date_string, split(v.transaction_time, "-") as date_parts
WITH date_string, date_parts[1] + "/" + date_parts[2] + "/" + date_parts[0] as date_formatted
RETURN date_formatted  ORDER BY date_string DESC
$$) AS (date_formatted agtype);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search