Managing Dates

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

Date and Datetime Formats

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

  • 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,

  1. orientdb> 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, OrientDB 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).

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.

  1. orientdb> SELECT @RID, id, date.format('yyyy') AS year FROM Order
  2.  
  3. --------+----+------+
  4.  @RID   | id | year |
  5. --------+----+------+
  6.  #31:10 | 92 | 2015 |
  7.  #31:10 | 44 | 2014 |
  8.  #31:10 | 32 | 2014 |
  9.  #31:10 | 21 | 2013 |
  10. --------+----+------+

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

  1. orientdb> SELECT date.format('yyyy') AS Year, COUNT(*) AS Total 
  2.           FROM Order ORDER BY Year
  3.  
  4. ------+--------+
  5.  Year |  Total |
  6. ------+--------+
  7.  2015 |      1 |
  8.  2014 |      2 |
  9.  2013 |      1 |
  10. ------+--------+

Dates before 1970

While you may find the default system for managing dates in OrientDB 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.

  1. orientdb> 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.

  1. orientdb> CREATE VERTEX V SET city = "Rome", date = DATE("0753-04-21 00:00:00 BC")
  2. orientdb> SELECT @RID, city, date FROM V
  3.  
  4. -------+------+------------------------+
  5.  @RID  | city | date                   |
  6. -------+------+------------------------+
  7.  #9:10 | Rome | 0753-04-21 00:00:00 BC |
  8. -------+------+------------------------+

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.

  1. orientdb> CREATE VERTEX V SET city = "Rome", date = DATE("yyyy-MM-dd HH:mm:ss GG")
  2. orientdb> SELECT @RID, city, date FROM V
  3.  
  4. ------+------+------------------------+
  5.  @RID | city | date                   |
  6. ------+------+------------------------+
  7.  #9:4 | Rome | 0753-04-21 00:00:00 BC |
  8. ------+------+------------------------+

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,

  1. orientdb> SELECT @RID, city, date.asLong() FROM #9:4
  2.  
  3. ------+------+------------------------+
  4.  @RID | city | date                   |
  5. ------+------+------------------------+
  6.  #9:4 | Rome | -85889120400000        |
  7. ------+------+------------------------+

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

  1. orientdb> CREATE VERTEX V SET city = "Rome", date = DATE(-85889120400000)
  2. orientdb> SELECT @RID, city, date FROM V
  3.  
  4. -------+------+------------------------+
  5.  @RID  | city | date                   |
  6. -------+------+------------------------+
  7.  #9:11 | Rome | 0753-04-21 00:00:00 BC |
  8. -------+------+------------------------+

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 datetimeformat in the database:

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