Managing Dates

ArcadeDB treats dates as first class citizens. Internally, it saves dates in the Unix time format. Meaning, it stores dates as a long variable, which contains the count in milliseconds since the Unix Epoch, (that is, 1 January 1970).

ArcadeDB fully supports the new Java Time API with a custom precision from second to nanoseconds. The data types to manage date/time are:

  • DATE, to handle dates without a time. Example: 01/01/2023

  • DATETIME_SECOND, to handle datetime with the precision of the second. Example: 01/01/2023 10:30:00

  • DATETIME, to handle datetime with the precision of the millisecond. Example: 01/01/2023 10:30:00.333

  • DATETIME_MICROS, to handle datetime with the precision of the microsecond. Example: 01/01/2023 10:30:00

  • DATETIME_NANOS, to handle datetime with the precision of the nanosecond. Example: 01/01/2023 10:30:00

using high precision types increase the space used on disk. For example, using nanosecond precision instead of millisecond increase the space requested to store the datetime of a factor of 2X.

Dates Inside Lists and Maps over HTTP

A datetime stored inside a LIST or a MAP is returned by the HTTP API as a formatted string that keeps its full precision (for example "2026-10-03 12:34:56.123457"), exactly like a scalar datetime property. Before 26.10.1 list items were returned as epoch milliseconds, and map values lost their fractional seconds. (Available since v26.10.1)

Precision on Undeclared Properties

If a property is not declared in the schema, ArcadeDB stores every datetime at the precision of the value itself, so a microsecond value keeps its microseconds without any declaration.

Creating an index or a constraint on such a property with Cypher (CREATE INDEX, CREATE CONSTRAINT) declares it in the schema, and the type is inferred from the data already stored: ArcadeDB picks the highest precision it finds, so existing microsecond values give DATETIME_MICROS. Milliseconds are the minimum, so a property holding only whole seconds is declared DATETIME and not DATETIME_SECOND.

To choose the precision yourself rather than relying on the inference, declare the property before creating the index:

CREATE PROPERTY Event.created_at DATETIME_MICROS

If you’re working with the Java API, by default ArcadeDB uses the new Java Time API on deserialization: DATE values are returned as java.time.LocalDate and DATETIME values as java.time.LocalDateTime. These defaults are controlled by the arcadedb.dateImplementation and arcadedb.dateTimeImplementation settings.

The supported implementations are:

  • for DATE: java.time.LocalDate (default), java.util.Date, java.util.Calendar, java.time.LocalDateTime (midnight of the day)

  • for DATETIME: java.time.LocalDateTime (default), java.util.Date, java.util.Calendar, java.time.ZonedDateTime, java.time.Instant

java.util.Date and java.util.Calendar can only handle precision down to the millisecond. When arcadedb.dateTimeImplementation is set to one of them, DATETIME_MICROS and DATETIME_NANOS values are returned as java.time.LocalDateTime, so no precision is lost; DATETIME and DATETIME_SECOND values are returned as the configured class. Before 26.10.1 such DATETIME_MICROS/DATETIME_NANOS values were read back as NULL (the data on disk was intact and reads correctly after the upgrade).
before 26.11.1, arcadedb.dateImplementation=java.time.LocalDateTime read every DATE as a timestamp in January 1970 and the value read could not be written back. A DATE now reads as midnight of its day, and writing a LocalDateTime to a DATE property keeps only the day. The data on disk was never affected.
stored DATETIME values keep no time zone. In an openCypher comparison a stored value takes the zone of the datetime it is compared with, so e.ts = datetime('2026-01-01T11:00:00Z') and e.ts = datetime('2026-01-01T12:00:00+01:00') both match the same instant, whatever arcadedb.dateTimeImplementation is set to. Before 26.11.1, with java.time.ZonedDateTime a stored value did not equal the Z literal for its own instant, and with java.util.Date or java.time.Instant it matched Z but not the same instant in another zone.
before 26.10.1, setting arcadedb.dateImplementation to java.util.Date or java.util.Calendar was not applied to DATE properties: values were stored as a full timestamp and read back as a java.time.LocalDateTime. If you used that setting, DATE properties written by an earlier version hold a timestamp rather than a day, and the extra time of day is still there when you read them.

If you prefer the legacy java.util.Date (for example, for compatibility with existing code), you can change the defaults at the database level:

alter database `arcadedb.dateTimeImplementation` `java.util.Date`
alter database `arcadedb.dateImplementation` `java.util.Date`

Time of Day, Zoned Datetime and Duration Types

(Available since v26.11.1) Four more types hold what the openCypher time(), localtime(), datetime() and duration() functions return, without losing the offset, the zone or the months/days parts:

  • OFFSET_TIME, a time of day with a UTC offset (java.time.OffsetTime). Example: 09:00:00-05:00

  • LOCAL_TIME, a time of day without an offset (java.time.LocalTime). Example: 10:15:30

  • ZONED_DATETIME, a datetime with the zone or offset it was written with (java.time.ZonedDateTime). Example: 2024-01-01T10:00:00+01:00[Europe/Rome]

  • DURATION, an openCypher duration with months, days, seconds and nanoseconds (DURATION values read back as com.arcadedb.query.opencypher.temporal.CypherDuration). Example: P1M2DT3S

openCypher stores these four natively, so a value always reads back as what it was written as, with no guessing from its text. A String that merely looks temporal, such as the opening hours 09:00-17:00 or a code like P100D, is a String and stays a String. Before 26.11.1 these four types were stored as Strings and openCypher guessed their type when reading an undeclared property, so 09:00-17:00 came back as a time and WHERE l.hours = '09:00-17:00' matched nothing.

All four can be indexed, with both LSM_TREE and HASH. A ZONED_DATETIME and an OFFSET_TIME index by the instant, so 12:00:00+01:00 and 11:00:00Z are the same key, and a unique index refuses the second one.

If a property is declared with one of these types and a record holds a String for it, the String is converted when the record is read and saved in the native type the next time the record is written. A String that cannot be converted is read as it is.

a zoned datetime() written to a property declared DATETIME is stored as an instant and reads back as the UTC wall clock, like any other java.time value. Before 26.11.1 it kept the local wall clock of its zone.
Migrating Cypher Temporals Stored as Strings

A database written by an earlier version holds these values as Strings in undeclared properties, and they are now read as Strings. To get native values back, declare the property with its type and copy the String into it. UPDATE Place SET hours = hours does not do it: with the property declared the read has already converted the String, so the value looks unchanged and ArcadeDB skips the write.

The script parks the String in a scratch property, declares the property, and copies the String back. Run it with sqlscript, once per property, replacing Place, hours and OFFSET_TIME (use LOCAL_TIME, ZONED_DATETIME or DURATION for the other types):

BEGIN;
UPDATE Place SET hours_old = hours;
UPDATE Place REMOVE hours;
COMMIT;

CREATE PROPERTY Place.hours OFFSET_TIME;

BEGIN;
UPDATE Place SET hours = hours_old WHERE hours_old IS NOT NULL;
UPDATE Place REMOVE hours_old;
COMMIT;

Only run it on a property that holds temporal Strings, and take a backup first. Do not remove hours_old until you have checked that every record has its hours value (SELECT count(*) FROM Place WHERE hours_old IS NOT NULL AND hours IS NULL must be 0). On a large type, run the UPDATE statements in batches with LIMIT.

Date and Datetime Formats

In order to make the internal count from the Unix Epoch into something human readable, ArcadeDB formats the count into date and datetime formats. By default, these formats are ISO 8601:

  • Date Format: yyyy-MM-dd

  • Datetime Format: yyyy-MM-dd HH:mm:ss

A DATE is a calendar day, not an instant, so it is always rendered as the day it was stored: the result does not change with the time zone of the server that answers the request. Only DATETIME values carry a time of day.

When a DATE is written as a JSON number (for example in the result of a projection such as SELECT day FROM Event), it is the milliseconds of that day at midnight UTC, whatever the time zone of the server. Before 26.10.1 the midnight was taken in the server’s own time zone, so the number changed from one server to another.

Accepted Input Strings

The configured formats above are what ArcadeDB renders. On input, a string assigned to a DATE or DATETIME* property is accepted in any of these spellings, tried in order:

  1. ISO 8601 without a zone, e.g. 2024-02-29T13:45:10.123456;

  2. ISO 8601 with an offset or Z, e.g. 2024-02-29T13:45:10.123456+01:00;

  3. the database’s DATETIMEFORMAT;

  4. the database’s DATEFORMAT;

  5. the SQL timestamp spelling: an ISO date, a space, an optional time with an optional fractional second of any precision, and an optional offset - e.g. 2024-02-29 13:45:10.123456, 2024-02-29 13:45:10.123456+01, 2024-02-29 13:45, 2024-02-29. This is what PostgreSQL prints and what psqlodbc binds a timestamp as.

The database’s own formats are tried before the last one, so customizing them can never be overridden by it.

How an offset in the string is treated depends on the type the value is stored as, which is set by arcadedb.dateTimeImplementation. With the default java.time.LocalDateTime the time is kept as written and the offset is not applied, so 13:45:10+01:00 is stored as 13:45:10. With an instant-bearing type such as java.time.Instant or java.util.Date the offset is honoured and the value is stored as the instant it names.

A string that matches none of these is rejected: the INSERT or UPDATE fails with an error naming the property. It is never stored as NULL. The same applies to the date() function and the .asDatetime() method when called without an explicit format, except that date() keeps its documented NULL answer for a value it cannot read.

Before 26.10.1 an unparseable date string was silently stored as NULL and the write reported success.

In the event that these default formats are not sufficient for the needs of your application, you can customize them through ALTER DATABASE …​ DATEFORMAT and DATETIMEFORMAT commands. For instance,

arcadedb> ALTER DATABASE DATEFORMAT "dd MMMM yyyy"

This command updates the current database to use the English format for dates. That is, 14 Febr 2015.

SQL Functions and Methods

To simplify the management of dates, ArcadeDB SQL automatically parses dates to and from strings and longs. These functions and methods provide you with more control to manage dates:

SQL Description

date()

Function converts dates to and from strings and dates, also uses custom formats.

sysdate()

Function returns the current date.

.format()

Method returns the date in different formats.

.asDate()

Method converts any type into a date.

.asDatetime()

Method converts any type into datetime.

.asLong()

Method converts any date into long format, (that is, Unix time).

.precision()

Method modifies the precision of a datetime property. For example, .precision('millisecond') returns the datetime property with millisecond precision

For example, consider a case where you need to extract only the years for date entries and to arrange them in order. You can use the .format method to extract dates into different formats.

arcadedb> SELECT @RID, id, date.format('yyyy') AS year FROM Order
+--------+----+------+
| @RID   | id | year |
+--------+----+------+
| #31:10 | 92 | 2015 |
| #31:10 | 44 | 2014 |
| #31:10 | 32 | 2014 |
| #31:10 | 21 | 2013 |
+--------+----+------+

In addition to this, you can also group the results. For instance, extracting the number of orders grouped by year.

arcadedb> SELECT date.format('yyyy') AS Year, COUNT(*) AS Total FROM Order ORDER BY Year
+------+--------+
| Year |  Total |
+------+--------+
| 2015 |      1 |
| 2014 |      2 |
| 2013 |      1 |
+------+--------+

Dates before 1970

While you may find the default system for managing dates in ArcadeDB sufficient for your needs, there are some cases where it may not prove so. For instance, consider a database of archaeological finds, a number of which date to periods not only before 1970 but possibly even before the Common Era. You can manage this by defining an era or epoch variable in your dates.

For example, consider an instance where you want to add a record noting the date for the foundation of Rome, which is traditionally referred to as April 21, 753 BC. To enter dates before the Common Era, first run the [ALTER DATABASE DATETIMEFORMAT] command to add the GG variable to use in referencing the epoch.

arcadedb> ALTER DATABASE DATETIMEFORMAT "yyyy-MM-dd HH:mm:ss GG"

Once you’ve run this command, you can create a record that references date and datetime by epoch.

arcadedb> CREATE VERTEX V SET city = "Rome", date = DATE("0753-04-21 00:00:00 BC")
arcadedb> SELECT @RID, city, date FROM V
+-------+------+------------------------+
| @RID  | city | date                   |
+-------+------+------------------------+
| #9:10 | Rome | 0753-04-21 00:00:00 BC |
+-------+------+------------------------+

Using .format() on Insertion

In addition to the above method, instead of changing the date and datetime formats for the database, you can format the results as you insert the date.

arcadedb> CREATE VERTEX V SET city = "Rome", date = DATE("yyyy-MM-dd HH:mm:ss GG")
arcadedb>  SELECT @RID, city, date FROM V
+------+------+------------------------+
| @RID | city | date                   |
+------+------+------------------------+
| #9:4 | Rome | 0753-04-21 00:00:00 BC |
+------+------+------------------------+

Here, you again create a vertex for the traditional date of the foundation of Rome. However, instead of altering the database, you format the date field in CREATE VERTEX command.

Viewing Unix Time

In addition to the formatted date and datetime, you can also view the underlying count from the Unix Epoch, using the asLong() method for records. For example,

arcadedb> SELECT @RID, city, date.asLong() FROM #9:4
+------+------+------------------------+
| @RID | city | date                   |
+------+------+------------------------+
| #9:4 | Rome | -85889120400000        |
+------+------+------------------------+

Meaning that, ArcadeDB represents the date of April 21, 753 BC, as -85889120400000 in Unix time. You can also work with dates directly as longs.

Since v26.10.1, a number with a fractional part is rounded down to the unit that contains it (for a datetime in milliseconds, -1.5 is millisecond -2), and a number that is not a real instant (NaN, Infinity, or a value outside the supported range) is refused with an error instead of being stored as 1970-01-01 or as an unrelated date.

arcadedb> CREATE VERTEX V SET city = "Rome", date = DATE(-85889120400000)
arcadedb> SELECT @RID, city, date FROM V
+-------+------+------------------------+
| @RID  | city | date                   |
+-------+------+------------------------+
| #9:11 | Rome | 0753-04-21 00:00:00 BC |
+-------+------+------------------------+

Use ISO 8601 Dates

According to ISO 8601, Combined date and time in UTC: 2014-12-20T00:00:00. To use this standard change the date time format in the database:

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

Arithmetic with dates

Dates can be added and subtracted. If you want to know the difference in terms of seconds between two dates, you can use the - (minus) operator. Example:

SELECT sysdate() - lastActivity as secondsFromLastActivity FROM UserActivity

Returns 1212113.232000000.

if the date supports the fractional part of the second, then it’s returned as nanoseconds as decimal part. In the example above, sysdate() function returns a datetime with, by default, precision to the millisecond.

Time Units

The units of time used in the duration() function and precision() method are the following strings:

  • 'year'

  • 'month'

  • 'week'

  • 'day'

  • 'hour'

  • 'minute'

  • 'second'

  • 'millisecond'

  • 'microsecond'

  • 'nanosecond'