cancel
Showing results for 
Search instead for 
Did you mean: 

Two questions about date conversion in cypher

haidkang
Node

I have two questions about date conversion in cypher.

First,
There is SQL in Oracle :
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;

The SQL returns a number from 1 (sun) to 7 (sat)
Does Neo4j have a cypher function to replace it?

Second
If the arguemnts is put 1 (sun) ~ 7 (sat) as below SQL to return the NEXT day of the week in Oracle
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL

Is there a cypher query to replace it?

Thank you.

1 ACCEPTED SOLUTION

glilienfield
Ninja
Ninja

Oops, the above formula for transforming the day of the week is incorrect, as Saturday gets mapped to '0', so ignore this suggestion.

If this is needed, the simplest method I can think of is a literal mapping.

with [0,2,3,4,5,6,7,1] as weekDayMapping, date().dayOfWeek as day
return weekDayMapping[day]

View solution in original post

4 REPLIES 4

glilienfield
Ninja
Ninja

Neo4j has a lot of support for date/time and duration entities. You can find a description in the following section of the cypher manual:

To address your two questions, the following should work:

  1. date() returns the current date, which is an object with many attributes. dayOfWeek is one that returns the number of the weekday. The numbers start with Monday as the beginning of a week, so if you want Sunday to be the beginning, you can use the following query to transform the day of week to start with Sunday as 1.

return (date().dayOfWeek+1)%7

  1. You can add a duration of any amount to a date to get a new date. The following works to add 1 day to the current date:

return date()+duration({days:1})

Review the manual for see all of the capabilities. There are many.

In the second case, I created the query as shown below with your information and hints.

WITH date.truncate('week', date(), {dayOfWeek: 1}) as outday,
date() as today
RETURN
CASE
WHEN outday < today then outday + duration({days:7})
ELSE outday
END AS thedate

Thank you.

glilienfield
Ninja
Ninja

Oops, the above formula for transforming the day of the week is incorrect, as Saturday gets mapped to '0', so ignore this suggestion.

If this is needed, the simplest method I can think of is a literal mapping.

with [0,2,3,4,5,6,7,1] as weekDayMapping, date().dayOfWeek as day
return weekDayMapping[day]

Thank you for your solution.