Date and time functions in apache-age database

Question

I was designing a database for a social network. So needed a time stamp and Date when a user creates a post or comments.

Is there any function using the built-in function provided by apache-age to fetch the data and time from the system?

Answer 1

There is a function name timestamp() under the scalar function section of Apache age documentation. According to documentation, timestamp() returns the difference, measured in milliseconds, between the current time and midnight, January 1, 1970, UTC

SELECT *
FROM cypher('graph_name', $$  
RETURN timestamp()  
$$) as (t agtype);

The time in milliseconds is returned.

Results:

t 
1613496720760 
1 row(s) returned

You can look at this documentation for more details.

Answer 2

Adding to Pratik's answer:

Since you need a timestamp and date

SELECT *
FROM cypher('graph_name', $$  
RETURN to_char(to_timestamp(timestamp() / 1000), 'Month DD, YYYY, HH:MI:SS')
$$) as (t agtype);

As you can see, you can define your preferred format with this code.

Answer 3

There are some builtin functions in PostgreSQL that return date and time from the system, you could try that:

test=# SELECT now(), current_timestamp, date(now());

              now              |       current_timestamp       |    date    
-------------------------------+-------------------------------+------------
 2023-03-20 10:41:05.086692-03 | 2023-03-20 10:41:05.086692-03 | 2023-03-20
(1 row)

Does it help?

Answer 4

Needed a Time Stamp and Date when a user creates a post or comments.

Built-in function provided by Apache AGE to fetch the current data and time from the system:

There is no build in function in Apache AGE that provides this functionality.

The only built in function is timestamp().

SELECT *
    FROM cypher('graph_name', $$  
    RETURN timestamp()  
    $$) as (t agtype);

It gives milliseconds, from midnight, January 1, 1970, UTC up till now. This is not effective way to store time stamp.

My solution is:

• Create a Function to get current time stamp according to your need.
• Call that function to get current time stamp correctly in your post.

Function for Current time stamp:

CREATE OR REPLACE FUNCTION pg_catalog.current_timestamp_components()
RETURNS TEXT AS $$
DECLARE
  ts_text TEXT;
BEGIN
  SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS') INTO ts_text;
  RETURN ts_text;
END;
$$ LANGUAGE plpgsql;

Calling that function to store current time stamp correctly:

SELECT *
FROM cypher('graph', $$
    CREATE (:Person {name: 'Time', title: 'Developer', time: pg_catalog.current_timestamp_components()})
$$) as (n agtype);

Checking Results:

 SELECT *
FROM cypher('graph', $$
    Match (p:Person {name: 'Time'}) Return p
$$) as (Person agtype);

Output:

 {"id": 844424930131976, "label": "Person", "properties": {"name": "Time", "time": "2023-03-25 02:16:31", "title": "Developer
"}}::vertex
(1 row)

According to my research, this is the best way to achieve your desired functionality of storing Time Stamp and Date.