Skip to content

Latest commit

 

History

History
108 lines (77 loc) · 3.31 KB

Managing-Dates.md

File metadata and controls

108 lines (77 loc) · 3.31 KB

Managing Dates

In OrientDB dates are first class citizen. OrientDB internally saves dates in unixtime format as a long containing the milliseconds since Jan 1st 1970.

Date and Datetime formats

By default the following formats are used on a new database:

  • Date format: yyyy-MM-dd
  • Datetime format: yyyy-MM-dd HH:mm:ss

To change this global setting, use the ALTER DATABASE SQL comamnd. Example on setting dates with English format:

alter database dateformat "dd MMMM yyyy"

SQL

In order to simplify management of dates, OrientDB SQL parses automatically dates from/to strings and longs. The following functions come in rescue to have more control on how dates are managed:

  • date() to convert dates from/to string/date, also with custom format
  • sysdate() to represent current date
  • .format() to format the date using different formats
  • .asDate() to convert any type into a date
  • .asDatetime() to convert any type into a datetime
  • .asLong() to convert any date in long format (unixtime)

Extract pieces of date

By using the .format() SQL method, you can extract dates in different formats. Example to extract only the year of orders:

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 |
+--------+----+------+

You can also group them by year. This example extracts the number of orders grouped by year:

select date.format('yyyy') as year, count(*) as total from order oder by year

+------+--------+
| year |  total |
+------+--------+
| 2015 |      1 |
| 2014 |      2 |
| 2013 |      1 |
+------+--------+

Dates before 1970

To save dates before Jan 1st 1970, use negative numbers.

Example on setting the date about the fundation of Rome, Italy (April 21st 753 BC). To insert dates Before Christ, let's add the "era/epoch" as "GG" in database date and datetimes formats:

alter database datetimeformat "yyyy-MM-dd HH:mm:ss GG"

create vertex v set city = "Rome", date = date("0753-04-21 00:00:00 BC")

This is the result:

+-------+------+------------------------+
| #9:10 | Rome | 0753-04-21 00:00:00 BC |
+-------+------+------------------------+

You could also not change the database date/time format, and use the format onat insertion time:

create vertex v set city = "Rome", date = date("0753-04-21 00:00:00 BC", "yyyy-MM-dd HH:mm:ss GG")

To see the underlying long (unixtime) stored, let's convert it to long with asLong() for the record just inserted:

select date.asLong() from #9:4

This is the result:

-85889120400000

So Apr 21st 753 BC is represented as -85889120400000 in unixtime. We can also work with dates directly with longs:

create vertex v set city = "Rome", date = date(-85889120400000)

The result is identical to the previous one:

+-------+------+------------------------+
| #9:11 | Rome | 0753-04-21 00:00:00 BC |
+-------+------+------------------------+