CODEWITHSUNDEEP
sqldebuggingdatetimeinsertorientdb
Garrett
Garrett

Reputation: 11725

DateTime conversion error on insert into OrientDB

Error on conversion of date '2014-03-09T02:42:09.893' using the format: yyyy-MM-dd'T'HH:mm:ss.SSS

The type of error here is com.orientechnologies.orient.core.exception.OQueryParsingException.

I set the DateTime format using:

ALTER DATABASE DATETIMEFORMAT yyyy-MM-dd'T'HH:mm:ss.SSS

Further, I know this is a bug with Orient vs the Node API I use because this also fails in the console:

INSERT INTO User ("settings", "acceptedTerms", "activitiesCount", "appFirstUseDate", "birthday", "email", "equipment", "feedOption", "followerCount", "followingCount", "followingFeedLastReadAt", "gender", "goal", "height_unit", "height_val1", "height_val2", "kitchenSinkBadgesSeen", "lastRatePrompt", "lastVersionUsed", "level", "numReferrals", "platform", "popularFeedLastReadAt", "postCount", "seenHamburgerInstructions", "seenRatePrompt", "stream", "timezone", "unsubscribedFromWorkoutEmails", "username", "weight", "weight_unit", "createdAt", "updatedAt", "objectId", "ACL", "sessionToken") VALUES ({"__type":"Pointer","className":"Settings","objectId":"K9X5P470hF"}, true, 10, {"__type":"Date","iso":"2014-03-09T03:37:53.270Z"}, {"__type":"Date","iso":"2000-03-29T00:00:00.000Z"}, "[email protected]", {"1":[6,8,4]}, 1, 0, 0, {"__type":"Date","iso":"2014-03-12T00:32:11.245Z"}, 2, 2, 1, 5, 3, ["activity","schedule","findfriends","log","share","workouts/new","profile/l8QOwljKWh","editProfile","progress","workouts/today"], {"__type":"Date","iso":"2014-04-08T22:23:47.799Z"}, "2.4.3", 2, 0, 1, {"__type":"Date","iso":"2014-04-08T22:23:45.070Z"}, 10, true, true, "b", "America/Havana", true, "hannah00329", 128, 1, "2014-03-09T02:42:09.893", "2014-04-08T22:43:27.086", "l8QOwljKWh", {"*":{"read":true},"l8QOwljKWh":{"read":true,"write":true}}, "t7h4bpx5ri8oyd3vfdt1l7e0b")

With the same error:

Error: com.orientechnologies.orient.core.exception.OQueryParsingException: Error on conversion of date '2014-03-09T02:42:09.893' using the format: yyyy-MM-dd'T'HH:mm:ss.SSS

Most other DateTimes work, but for some reason 1 in every 10000 fails with this error.

The other DateTimes in my table look exactly the same:

2013-06-28T04:50:35.717
2013-07-03T08:16:10.713
2012-12-27T19:56:52.030

EDIT:

Upon further investigation, it looks like 2pm on March 9th is doomsday...

SUCCESS:
2013-06-28T04:50:35.717Z
2013-07-03T08:16:10.713Z
2012-12-27T19:56:52.030Z

ERROR:
2014-03-09T02:42:09.893Z
2014-03-09T02:11:49.764Z
2014-03-09T02:02:43.987Z
2014-03-09T02:10:39.408Z
2014-03-09T02:37:10.196Z
2014-03-09T02:40:46.492Z
2014-03-09T02:22:26.589Z

EDIT:

Here are my OrientDB settings

--------------------------------+----------------------------------------------------+
 NAME                           | VALUE                                              |
--------------------------------+----------------------------------------------------+
 Name                           | null                                               |
 Version                        | 9                                                  |
 Date format                    | yyyy-MM-dd                                         |
 Datetime format                | yyyy-MM-dd HH:mm:ss                                |
 Timezone                       | America/Toronto                                    |
 Locale Country                 | US                                                 |
 Locale Language                | en                                                 |
 Charset                        | UTF-8                                              |
 Schema RID                     | #0:1                                               |
 Index Manager RID              | #0:2                                               |
 Dictionary RID                 | null                                               |
--------------------------------+----------------------------------------------------+

Upvotes: 0

Views: 2326

Answers (1)

Garrett
Garrett

Reputation: 11725

2014-03-09T02:00:00 -> 2014-03-09T03:00:00 does not exist when incorporating Daylight Savings Time (DST).

Because that specific time is impossible when using DST, the error was in transferring the data from a database that allowed it to one that didn't. The reason Parse allows it is because they use Coordinated Universal Time (UTC), which does not change with DST.

The fix in OrientDB is as follows:

-- set timezone to utc
ALTER DATABASE TIMEZONE UTC

Upvotes: 2

Related Questions