dateTime() function is missing for handling datetime values with a timezone

[david-sitsky]

I wrote about this issue here: https://discourse.memgraph.com/t/support-for-datetime-function-in-cypher/327/2 but it was suggested I report it here.

I have been trying out memgraph and I have Kafka records written in JSON format. I have been able to import the data successfully and have a “transformation” that reads it and runs the Cypher query per Kafka record successfully to create my graph data.

I have JSON properties written in a common “ISO date-time” format, for example:

2003-01-16T06:59:32.000Z

I can see memgraph’s Cypher has a localDateTime() function, but not a dateTime() function like in Neo4J. Is there a reason for this?

[katarinasupe]

Thank you @david-sitsky for opening the issue here 😄

[gitbuda]

@david-sitsky when we designed the feature, all our use cases but also the current ones don't need the timezone part (I think that's the issue here?) + we actually have an optimization on the storage side which is possible because of that. I'm not saying it's not possible to add another data type, but would like to understand the actual need first 😄

Is it possible from your perspective to use the localDateTime() as an alternative to the dateTime()?

Thanks for reporting and let's figure out what's the best here 😄

[david-sitsky]

Hi Marko - worst case I can use localDateTime() for now.

…

On Fri, 18 Nov 2022 at 00:17, Marko Budiselić ***@***.***> wrote: @david-sitsky <https://github.com/david-sitsky> when we designed the feature, all our use cases but also the current ones don't need the timezone part (I think that's the issue here?) + we actually have an optimization on the storage side which is possible because of that. I'm not saying it's not possible to add another data type, but would like to understand the actual need first 😄 Is it possible from your perspective to use the localDateTime() as an alternative to the dateTime()? Thanks for reporting and let's figure out what's the best here 😄 — Reply to this email directly, view it on GitHub <#612 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABC5CAMYGXKDDWEUIRJTX6DWIYV75ANCNFSM6AAAAAARMCI6XI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[Josipmrden]

Can I close this @gitbuda ?

[PrivateGER]

The thing I'm facing here is that it's impossible to migrate Neo4j data that used DateTime() without an external python convert script.

This issue should really be mentioned in your https://memgraph.com/blog/how-to-migrate-from-neo4j-to-memgraph article.

[katarinasupe]

Hi @PrivateGER, thanks for pointing this out. We will create a migration guide on our documentation to avoid such issues. The blog post talked about one way of migrating your data, and I recommend you join our Discord server, if you haven't, so that we can better help you in the process of migration. Currently, users reported that the best way to migrate their data was to either have their dataset in CSV files and import it with LOAD CSV clause in Memgraph, or by exporting all data from Neo4j in a file that consist of cypher queries (cypherl file), changing it a bit (a bit different syntax for constraints and indexes) and importing it into Memgraph via mgconsole or Memgraph Lab.

Kudos to the community member for sharing these tips:

To migrate, use this command on Neo4j:
call apoc.export.cypher.all("file_name.cypherl", {format:"plain", useOptimizations:{type:"NONE"}, writeNodesProperties: true})

Go into the generated file and change the constraint creation queries, from:
CREATE CONSTRAINT <constraintName> FOR (node:<Label>) REQUIRE (node.<prop>) IS UNIQUE;

to
CREATE CONSTRAINT ON (node:<Label>) ASSERT node.<prop> IS UNIQUE

Since both engines use a different syntax.
After that, import the .cypherl file into Memgraph.

Now, some changes might be required if you have multiple databases or models. But then you should just make some changes in the apoc.export.cypher.all configuration parameters.

The user only had uniqueness constraints. Here are the docs for the existence constraint syntax in Memgraph.
Be careful, and please also create indexes before importing data into Memgraph. If you are using cypherl file, add lines at the beginning with index creation. Here are the tips on when to create indexes.

[Josipmrden]

Hey @PrivateGER, to get to the issue again. Is it the case that Neo4j supports a timezone part in its database types and we don't?

And what does your external script do in order to mitigate the issue?

And additional questions, is it ever the case that in Neo4j you will have additional characters that are in other timezone, other than "Z"?

[katarinasupe]

The issue is there is no temporal type in Memgraph which supports timezone. For example, here are the steps to reproduce the issue if we try creating a relationship with a localDateTime property:

Run query:
CREATE (u:User)-[:CREATED {createdAt: localDateTime("2003-01-16T06:59:32.000")}]->(p:Post)

Here is what you get in Memgraph:

This happens because localDateTime type does not support ending Z which indicates that that time is UTC.

[PrivateGER]

@katarinasupe got it right.

My script simply parsed the CSV file, took the RFC 3339 timestamps, and converted them. I didn't go with Memgraph for different reasons this time, but may in the future.

[coryvirok]

I'll add my support for including non-local datetime. Without it, we need to store the datetime as a UNIX timestamp alongside a timezone field. Having Memgraph assume the format is local time is a non-starter especially in a cloud environment where we do not know what the server's local timezone is set to.

Thanks

[katarinasupe]

Hi @coryvirok, thanks for adding your support. As you can see, we added a higher impact to this feature. Did you maybe try some workaround for now? Are you streaming data into the database? I am just trying to figure out how large of a blocker this is for you to prioritize better. Btw. feel free to join our Discord server if you'd like to talk more since the communication should be easier there 😄

[coryvirok]

I'm still in early phases of deciding between Graph DBs but I ran into this while building a test schema. I can work around it for now, but if I was already using Neo4J this would have been a blocker to using Memgraph as a "drop in replacement" like I see messaged on the website. Thanks for your response.

[Josipmrden]

@antoniofilipovic please read this so we can see what to do, it seems not at all times one is able to know what's the server local timezone
@gitbuda also

[matea16]

+1 from the community