[OrientDB] Datetime milliseconds are ignored

[Salvatore Piccione]

Hello,

I've defined a simple class that has got a DATETIME property and I've inserted some data (below you can find the console script I run):

CREATE DATABASE remote:localhost:2426/datetime-precision-test root pippo local graph;
CREATE CLASS TestClass EXTENDS OGraphVertex;
CREATE PROPERTY TestClass.Issuer STRING;
CREATE PROPERTY TestClass.IssueInstant DATETIME;
CREATE PROPERTY TestClass.ID STRING;
CREATE PROPERTY TestClass.resourceID STRING;
INSERT INTO TestClass (Issuer, IssueInstant, ID, resourceID) VALUES ('pi...@cartoonia.com', '2012-05-14 13:41:23:214', 'adafq3wr367364rfbeq', 'http://www.iot-at-work.eu/resourceA');
INSERT INTO TestClass (Issuer, IssueInstant, ID, resourceID) VALUES ('pi...@cartoonia.com', '2012-05-14 13:41:23:147', 'f243erd3wr367364rfb', 'http://www.iot-at-work.eu/resourceB'); 

In order to test whether millisecond precision works in SQL WHERE clause, I've executed the queries below that select nothing:

select from TestClass where IssueInstant = date( '2012-05-14 13:41:23:214', 'yyyy-MM-dd HH:mm:ss:SSS' )

select from TestClass where IssueInstant = '2012-05-14 13:41:23:214'

Indeed, both queries below select the records I defined. It seems that datetime milliseconds are ignored!

select from TestClass where IssueInstant = date( '2012-05-14 13:41:23:000', 'yyyy-MM-dd HH:mm:ss:SSS' )

select from TestClass where IssueInstant = '2012-05-14 13:41:23:000'

Affected versions: 1.0rc9 and the latest 1.0-SNAPSHOT (downloaded from https://oss.sonatype.org/content/groups/public/com/orientechnologies/orientdb/1.0-SNAPSHOT/orientdb-1.0-20120505.121554-24-distribution.zip).
The results are the same even if the SQL INSERT statements are run through the Java APIs.

Any idea?

TIA,

Salvatore

[Luca Garulli]

Ciao Salvatore,

it's a matter of configuration:

Set this right after you've open the database:

db.getStorage().getConfiguration().dateTimeFormat = "yyyy-MM-dd HH:mm:ss:SSS";

Lvc@

[Salvatore Piccione]

Hello Luca,

your reply implies that, on the server side, the java.util.Date instances preserve the milliseconds, right?
I've some further questions: is there a way to set that configuration parameter without using the Java API? If not, this implies that it is not possible to perform queries with milliseconds precision through the web GUI... Could it be possible to enable the milliseconds precision also for non-Java clients?

TIA,

Salvatore

2012/5/8 Luca Garulli <l.ga...@gmail.com>

[Luca Garulli]

Hi Salvatore,

unfortunately this is not yet supported but you can save this setting once for all at the beginning:

db.getStorage().getConfiguration().dateTimeFormat = "yyyy-MM-dd HH:mm:ss:SSS";

db.getStorage().getConfiguration().update();

Then close and reopen the database (or stop and restart the server) and the settings should be updated for all the clients.

Lvc@

[Luca Garulli]

You can use also:

alter database DATETIMEFORMAT yyyy-MM-dd HH:mm:ss:SSS

And the same with DATEFORMAT.

Lvc@

[ste...@activitystream.com]

Hi,

what is the property name for this setting?

(I want to put it in the orentdb-server.config file)

It's not listed here:

https://github.com/orientechnologies/orientdb/wiki/Configuration

Regards,

  -Stefán

[David J Lima]

Hello,

I have the same question, can this be included in the orentdb-server.config file?

 

[Russ Jackson]

ditto

[Eric24]

Luca--

After making this change, does OrientDB actually save the ms? I was under the impression that datetimes were stored internally as LONGs using the standard Unix Epoch format (seconds since...).

--Eric

[Eric24]

I tried a test, and apparently, the ms are saved in a DATETIME-type property after setting the DATETIMEFORMAT as suggested. So I guess that means it's not Unix Epoch format. Maybe it's "ms since..." stored as a 64-bit integer?

[scott molinari]

ODB stores the datetime values as Unix Epoch. You can use the .asLong() to see the full datetime (long) value. 

http://orientdb.com/docs/last/Managing-Dates.html

Oh, and I'd personally always use only the Unix Epoch value (based on UTC) for storing and retrieving any datetimes and use the programming language to reformat the datetimes and to correct for timezones. 

Scott

[Eric Lenington]

Aha! This statement on that page "which contains the count in milliseconds since the Unix Epoch", which I didn't see when I read through it the last time, is the answer to my question. Just knowing that the date is stored as the Unix epoch doesn't necessarily define whether it "seconds since" or "milliseconds since", so that was the clarification I was looking for. Thanks!

On Mon, Jun 6, 2016 at 1:19 AM, 'scott molinari' via OrientDB <orient-...@googlegroups.com> wrote:

ODB stores the dates as Unix epoch. You can use the .asLong() to see the full date (long) value. 

http://orientdb.com/docs/last/Managing-Dates.html

Scott

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/PTJ0aKF7f5o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[scott molinari]

Cool. Glad I could help.

Scott