0

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?

Emmanuel Allison
  • 56
  • 1
  • 6
  • 2
    Well the double quotes would be an issue: `select "13-08-2023"::date; ERROR: column "13-08-2023" does not exist`. Single quotes would work: `select '13-08-2023'::date; 2023-08-13` assuming `show datestyle ; ISO, DMY` supports it. You would probably be better off with a more ISO standard `2023-08-13`. – Adrian Klaver Aug 13 '23 at 19:39
  • AGE, by default, prints strings using double quotes. They can be cast to the `text` type, however. This will print them without the double quotes. I'll also consider using the ISO standard. Thanks. – Emmanuel Allison Aug 13 '23 at 21:03

12 Answers12

1

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

ahmed_131313
  • 142
  • 6
  • 1
    Thank you. Using `TO_DATE` works. I had to cast the returned record from the Cypher query to a `text`, however. Here's the code that worked: `SELECT TO_DATE(date, 'DD/MM/YYYY') from cypher('graph_name', $$ return date() $$) as (date text);` – Emmanuel Allison Aug 13 '23 at 21:07
  • Glad it worked! – ahmed_131313 Aug 13 '23 at 21:54
1

In addition to all of these, you can equally modify your original query to be

SELECT t.name FROM employees AS t
WHERE t.created_date > (
    SELECT created::date - interval '7 days' FROM cypher('graph_name', $$
        RETURN date()
    $$) as (created varchar));

by casting the results from the inner cypher query to date type and then subtracting the 7 days interval.

Nnaemeka Daniel John
  • 333
  • 1
  • 7
1

You can use cast technique to convert the string representation.

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

In the above snippet, CAST(a::text AS date) is converting the string representation of date into actual date datatype.

adil shahid
  • 125
  • 4
1

Use this:

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 text);
Vinay-Agens
  • 15
  • 2
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));
Emmanuel Allison
  • 56
  • 1
  • 6
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:.

Peter
  • 43
  • 4
0

Any option works well though, it would be a good add to AGE if the AGTV_DATE type is added. However, you can also convert to PostgreSQL date type using the ::date cast operator.

Tito
  • 289
  • 8
0

You can modify the return of the function for the ::date cast operator of PostgreSQL.

Or just use the TO_DATE() function for convert a string to a date.

reference: TO_DATE Function

Marcos Silva
  • 115
  • 5
0

SELECT TO_DATE('2023-08-28', 'YYYY-MM-DD'); try this query Using TO_DATE() provides more flexibility in specifying the date format, and it's often preferred when dealing with date conversions in PostgreSQL.

fahad ullah
  • 3
  • 2
-1

You have two options for handling date conversion in PostgreSQL:

  1. Custom Function: Change the function's return for the ::date cast operator.
  2. TO_DATE() Function: Use TO_DATE() to convert a string into a date.
Zeeshan
  • 3
  • 1
-1

You can modify the return type of the function for the ::date cast operator of PostgreSQL or just use the TO_DATE() function for convert a string to a date.

Ammar Baig
  • 1
  • 1
-1

As per the apache age docs you can indeed work with strings that can be cast to PostgreSQL's date type. This approach is reasonable and avoids the need to create a new return type (agtype_value_type.AGTV_DATE) for dates.

you can modify your query like this:

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