difference between two datetime parameters

[Rubens Ballabio]

Hi guys,

do you know if there is any way to do a simple difference between two datetime parameters?

i can't find the way to make it work.

thank you in advance,

Rubens

[Tobie Morgan Hitchcock]

Hi Rubens,

There should be a couple of ways of making it work...

1. This uses eval to give you the time difference in milliseconds

SELECT eval('deleted_at - created_at') AS diff FROM Friends

2. This uses eval to give you the time difference in seconds

SELECT eval('(deleted_at - created_at) / 1000') AS diff FROM Friends

3. Create a function and use that to give you the difference in milliseconds

INSERT INTO OFunction SET name='diff', parameters=["datetime1", "datetime2"], language='groovy', code="return datetime1.getTime() - datetime2.getTime();"

SELECT diff(deleted_at, created_at) AS diff from Friends

[Rubens Ballabio]

Hi Tobie,

the query i am doing is not retrieving datetime... that's the real issue.

This is my query:

select outE().timestamp as created_time, inE().timestamp as closed_time

from Ticket

where id='1'

the output i see is something like this:

["2014-10-24 09:14:17"], ["2014-10-26 19:31:23"]

basically i am getting the timestamps from two different edges connected to one Vertex.

Maybe this query is not the best option.

Can You advice me?

Best

Rubens

[Tobie Morgan Hitchcock]

Hi Rubens,

The thing about in(), out(), both(), inE(), outE(), inV(), outV() is that it should return an array of records (edges/vertices) and not a single value.

To get the first edge from the query and ignore the other edges you could use the following...

select outE()[0].timestamp as created_time, inE()[0].timestamp as closed_time

from Ticket

where id='1'

That will take the first item in the array of returned edges, giving you the timestamp field as a datetime.

Maybe there is another better way that I don't know of, but that should work I think.

Tobie

[Tobie Morgan Hitchcock]

You could also use

select first( outE().timestamp ) as created_time, first( inE().timestamp ) as closed_time

http://www.orientechnologies.com/docs/last/orientdb.wiki/SQL-Functions.html#first

Tobie

[Rubens Ballabio]

Worked!

Thank you Tobie!