Want to do date arithmetic in Cypher

[Alan Robertson]

Hi,

I'd like to be able to put a timestamp in an attribute (like last time
some other attribute was updated) and then do arithmetic on it to say
how long it has been since it was last updated.

Here's a query that doesn't do what I want... :-D.

START root=node(0)
MATCH drone-->type-->root
WHERE type.name = "Drone" and drone.status = "dead" and drone.reason <> "HBSHUTDOWN"
RETURN drone.name, drone.lastupdate / 3600.0


That will print the number of hours since Jan 1, 1970 - which is not
what I wanted... But I'd like to do something like this...

RETURN drone.name, (now - drone.lastupdate) / 3600.0

That would be perfect.

I could put the date in ASCII format, but unless you're in the time stamp that the date is in,
that it will be unintuitive - and even if you are, it will require mental time arithmetic, which
isn't ideal either...

Better suggestions would be much appreciated!


--
Alan Robertson <al...@unix.sh> - @OSSAlanR

"Openness is the foundation and preservative of friendship... Let me claim from you at all times your undisguised opinions." - William Wilberforce

[Wes Freeman]

That would be awesome. And easy enough that I may even do a pull request for it if I remember after a couple of Guinnesses tonight. You could pass in a parameter {now}.

Wes

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Wes Freeman]

So, I remembered... I called it "gettimestamp", for lack of a better name. Any better ideas?

neo4j-sh (?)$ create (n {ts:gettimestamp});

+-------------------+

| No data returned. |

+-------------------+

Nodes created: 1

Properties set: 1

3 ms

neo4j-sh (?)$ start n=node(*) return (getTimestamp - n.ts) / 1000;

+------------------------------+

| (getTimestamp - n.ts) / 1000 |

+------------------------------+

| 29                           |

+------------------------------+

1 row

16 ms

Anyway, I submitted it... https://github.com/neo4j/neo4j/pull/634

Wes

[Peter Neubauer]

That is pretty neat!

Andres, Stefan, what say thee? Looks like a great addition to 2.0

/peter

Cheers,

/peter neubauer

G: neubauer.peter
S: peter.neubauer
P: +46 704 106975
L: http://www.linkedin.com/in/neubauer
T: @peterneubauer

The authoritative book on graph databases - http://graphdatabases.com
Neo4j questions? Please use SO - http://stackoverflow.com/search?q=neo4j

[Michael Hunger]

Only the name: Probably rather a function like now()

I'd love if it could be one of the first of the UDFs.

Cheers

Michael

(neo4j.org) <-[:WORKS_ON]- (@mesirii) -[:TAKES_CARE_OF]-> (you) -[:WORKS_WITH]->(@Neo4j)

[Alan Robertson]

Awesome!

I know I don't have the function to do the computation now, but I could
go ahead and change my code (before this first release is finalized) to
add the attribute to the relevant nodes now. If I wanted to do that,
what format (and units) would it have to be in?

I'm guessing it's a 64-bit integer in microseconds since the UNIX epoch?

In case it wasn't obvious what I was trying to compute in my query was
"How long has this machine been down"?

[Wes Freeman]

It calls the scala function System.currentTimeMillis (so it's milliseconds since epoch).

Wes

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.

To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+unsubscribe@googlegroups.com.

[Alan Robertson]

On 03/18/2013 08:09 AM, Wes Freeman wrote:
> It calls the scala function System.currentTimeMillis (so it's
> milliseconds since epoch).

I can always rescale, but getting the epoch wrong would be annoying...
Just to clarify - you mean the UNIX epoch - 1 January 1970 - 00:00 UTC.

Right?

[Wes Freeman]

Yeah, that one. Divide by 1000 to get seconds.

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.

To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.

[Wes Freeman]

Andres and I hashed it out a bit and decided to call it currentTimeMillis() (so it's more obvious what it is). now() will be reserved for real date/time datatypes in future. I'll work on a new pull request tonight with the documentation pieces and such along with it. Hopefully it will make it into 1.9.

Wes

[Michael Hunger]

I think this name is just Java API leaking into Cypher.

perhaps just millisSinceEpoch() / millisSince1970()

Not sure if we need millis at all? Perhaps have a timeunit parameter.

timeSinceEpoch(Millis), timeSinceEpoch(Seconds), timeSinceEpoch(Minutes)

Cheers

Michael

(neo4j.org) <-[:WORKS_ON]- (@mesirii) -[:TAKES_CARE_OF]-> (you) -[:WORKS_WITH]->(@Neo4j)

[Wes Freeman]

Ok, after some more hashing with Mr. Hunger I'm actually going to start coding it as now('ms') to allow future formatting options for now() and not steal the date/time reservation. If you have a better idea speak now or I'll make you rename all my code. :)

Wes

[Wes Freeman]

Ok, doing the docs now. One caveat I noticed is that the time doesn't get cached for the whole query, just the row. So if you create multiple nodes, they'll have different times.

neo4j-sh (0)$ create (n {ts1:now('ms')})-[r:TEST {ts:now('ms')}]->(m {ts1:now('ms')}) return *;

+------------------------------------------------------------------------------------------------+

| m                              | n                              | r                            |

+------------------------------------------------------------------------------------------------+

| Node[20203]{ts1:1363656550293} | Node[20204]{ts1:1363656550294} | :TEST[13] {ts:1363656550294} |

+------------------------------------------------------------------------------------------------+

1 row

Nodes created: 2

Relationships created: 1

Properties set: 3

3 ms

And if you do a query like this, it can be different per row. Not sure if this is ideal, but I imagine it's good enough for most use cases--I'll ask Andres tomorrow what he thinks. Any opinions?

neo4j-sh (0)$ start n=node(*) return now('ms'), now(''+'ms'), now('m'+'s') limit 15;

+-----------------------------------------------+

| now('ms')     | now(''+'ms')  | now('m'+'s')  |

+-----------------------------------------------+

| 1363653318071 | 1363653318071 | 1363653318071 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318075 | 1363653318075 | 1363653318075 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

| 1363653318076 | 1363653318076 | 1363653318076 |

+-----------------------------------------------+

15 rows

[Alan Robertson]

From my perspective, I'm probably mainly interested in seconds or even minutes or hours since it was last updated.

So, I don't care.

I thank all of you for taking my request seriously and seeing how nice it could be for purposes like mine (and no doubt many others).

[Wes Freeman]

Andrés is holding on the merge until he can fix it to be the same across a query, since I'm not sure how to do that. I agree that that probably makes the most sense, and also means fewer calls to check the time for many result rows.

Wes

[Alan Robertson]

That's fine by me.  I agree it makes the most sense and it will make it a little faster.  It just doesn't matter to me personally ;-).