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

Postgresql – What would be the best result type for dates in AGE?

I’m adding temporal Cypher functions to AGE, and I’ve implemented the date() Cypher function. Currently, it works, and my result is printed in the output as a string.

SELECT * FROM cypher('graph', $$
    RETURN date()
$$) as (date agtype);
     date     
--------------
 "13-08-2023"
(1 row)

However, I’m concerned that functions like this may by used in SQL expressions.

SELECT t.name FROM employees AS t
WHERE t.created_date > (
    SELECT a FROM cypher('graph_name', $$
        RETURN date()
    $$) as (created agtype) - 7);    -- Created at least 7 days ago.

But PostgreSQL would treat them as strings instead of dates.

Will a new return type, agtype_value_type.AGTV_DATE, need to be created? Or is it possible to work with strings which could be cast to PostgreSQL’s date type?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I found a solution to this, thanks to Adrian's comment and ahmed_131313's answer.

    We have to convert the returned record from AGE into one of PostgreSQL's character types (char, varchar, or text), and then convert them to dates.

    The following SQL scripts work.

    SELECT TO_DATE(date, 'DD/MM/YYYY') FROM cypher('graph', $$
    RETURN date()
$$) as (date text);

SELECT age_date::date FROM cypher('graph', $$
    RETURN date()
$$) as (age_date varchar(225));

  2. 0

    I think there is no need to create a data type to deal with such issue, you can simply convert the string you get from the cypher function into date or timestamp just by using TO_DATE or TO_TIMESTAMP function as follows:

    postgres=# SELECT TO_DATE('13-08-2023', 'DD/MM/YYYY');
  to_date
------------
 2023-08-13
(1 row)

postgres=# SELECT TO_TIMESTAMP('13-08-2023', 'DD/MM/YYYY');
      to_timestamp
------------------------
 2023-08-13 00:00:00+03
(1 row)

    and then after the conversion, use it in any calculations you wish.
    I hope that was helpful, let me know how it goes with you

    Login or Signup to reply.
  3. 0

    You can pretty much convert the string date returned by the cypher query into the PostgreSQL traditional date data type. Using the TO_DATE(date_text, format) function.. check out this article for more info as to what functions you can use and how:.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search