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).

Starting from v21.1.1, ArcadeDB sully support 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.

If you’re working with the Java API, the class used for both DATE and DATETIME is java.util.Date. With DATETIME values, if you’re using higher precision than millisecond you need to use the new Java Time API:

  • java.time.LocalDateTime

  • java.time.ZonedDateTime

  • java.time.Instant

If you’re starting a new project with ArcadeDB, using the new Java Time API is strongly advised. For example, if you want to use LocalDateTime as default class that represent DATETIME, set it in the binary serializer at startup and ArcadeDB will always return a LocalDateTime for DATETIME types:

alter database `arcadedb.dateTimeImplementation` `java.time.LocalDateTime`

You can do the same for DATE types using the new Java Time API for dates java.time.LocalDate:

alter database `arcadedb.dateImplementation` `java.time.LocalDate`

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

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.

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'