cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to convert dates with apoc

raulprivate
Node Link

Hi,

I'm trying to build a subgraph with times and dates in my recommender system of players. For example, I created 24 nodes (for each hour of the day) with the aim of classifying the users that connect a specific time. This way I'd be able recommend easily to a player another players that play at the same time.
My graph has 2 main type of nodes: Players and Game. The nodes type "Game" have a date property with the creation date of the game.

However, I found myself stuck with the following problems:

  1. I'm not able to create the date property with a datetime format. It always turn into a string
  2. Due to issue 1 I'm not able to extract the time from the property

Here is my code for inserting (note that "batch_df" is a pandas datafram with my data):

for index, row in batch_df.iterrows():
        tx.evaluate('''
        WITH apoc.date.parse($created_at) as dates
        MERGE (p:Player {playerId:$playerId})
        MERGE (g:Game {gameId:$gameId, created_at: apoc.date.format(dates, 'ms', 'yyyy-MM-dd HH:mm:ss')})
        MERGE (p)-[:played_in]->(g);
        ''', parameters = {'playerId': row['player_name'],'gameId': row['gameId'],'created_at': row['created_at']})

Here is a sample of a date from my dataframe: 2019-01-25 14:49:58

When trying any operation on the property I get the following error: Text cannot be parsed to a Time

Queries that I'm trying:

match (g:Game)
return g.gameId, apoc.date.fields(g.created_at, 'yyyy/MM/dd HH:mm:ss')
match (g:Game)
return g.gameId, time(g.created_at)

Here I'm retrieving the type:

It's obvious that I'm not fully understanding how apoc works but I'm not able to figure out with the documentation I read. Not even sure if I really need apoc or there's a better way. Could anyone please help?!

Many thanks!
Raul.

2 REPLIES 2

raulprivate
Node Link

I think I found the solution but doesn't seem optimal.
This is how I changed my inserting query:

    for index, row in batch_df.iterrows():
        tx.evaluate('''
        MERGE (p:Player {playerId:$playerId, platformId:$platform})
        MERGE (g:Game {gameId:$gameId, created_at:datetime({ epochSeconds:apoc.date.parse($created_at,'ms','yyyy-MM-dd HH:mm:ss')/ 1000, nanosecond: 23 })})
        MERGE (p)-[:played_in{win:$win}]->(g);
        ''', parameters = {'playerId': row['summoner_name'],'win': row['win'],'gameId': row['gameId'],'platform': row['matchplatform'],'created_at': row['created_at']})

2X_b_b0fe7cf2db1382fa8edefa2a5e5aba5b63eb4dd3.png

Is this then the best way to convert from string to datetime?

datetime({ epochSeconds:apoc.date.parse($created_at,'ms','yyyy-MM-dd HH:mm:ss')/ 1000, nanosecond: 23 })

Try this:

RETURN apoc.date.format(apoc.date.parse("2019-01-25 14:49:58", 'ms', 'yyyy-MM-dd HH:mm:ss'), 'ms', 'yyyy-MM-dd HH:mm:ss')

Result: "2019-01-25 14:49:58"

RETURN apoc.date.fields (apoc.date.format(apoc.date.parse("2019-01-25 14:49:58", 'ms', 'yyyy-MM-dd HH:mm:ss'), 'ms', 'yyyy-MM-dd HH:mm:ss'))

Result:
2X_4_4ab0c52f977ab609eeb9e927fd0169f3cae0ded5.png

You need to parse first and then format.