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.
(Since v26.10.2) a value outside the range its precision can represent is refused with an error instead of being stored as a different instant. Each type stores a 64-bit count of its unit since the epoch, so DATETIME_NANOS holds instants from 1677-09-21 to 2262-04-11 and DATETIME_MICROS about 292,000 years either way. Before, an instant past the range - for example a "no expiration" date such as 9999-12-31 written to a DATETIME_NANOS property - was stored as 2262-04-11T23:47:16.854775807. Comparing values is unaffected: a date past the range still compares after every date inside it.

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)

Datetimes in the JSON of a Record

record.toJSON() writes a DATETIME and a DATETIME_SECOND property as a number of epoch milliseconds, and a DATETIME_MICROS and a DATETIME_NANOS property as formatted text that keeps all its digits (for example "2020-01-02 03:04:05.123456789"), and fromJSON() of that text gives the value back for every precision. A number is epoch milliseconds whichever the precision of the property is, as it is in SQL and over HTTP. Before 26.11.1 (issue #10486) toJSON() wrote a DATETIME_SECOND, DATETIME_MICROS or DATETIME_NANOS property as a count in its own unit, which fromJSON() read as milliseconds, so a 2020 timestamp came back in 1970, in the year 51972 or in the year 50004715; the remote client sends a record as this JSON, so it stored such a property wrong on the server too. A reader of the JSON that took those counts as seconds, microseconds or nanoseconds, for example a WebSocket subscriber of the change events, must read the milliseconds and the text instead. (Available since v26.11.1)

A DATE in JSON is a number of epoch milliseconds at UTC midnight, as long as that number fits a 64-bit integer, which is a date within about 292 million years of 1970 (up to +292278994-08-17 and from -292275055-05-17). A date beyond it (LocalDate.MAX, LocalDate.MIN) is written as its ISO text (for example "+999999999-12-31"), where toJSON() and the jsonl export used to fail with an arithmetic overflow, so a reader of the JSON of such a record sees text instead of a number for that value. (Available since v26.11.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.10.2, 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. (Since v26.10.2) openCypher reads a property declared DATE as a Cypher Date under every arcadedb.dateImplementation (java.util.Date, java.util.Calendar and java.time.LocalDateTime included), and a DATETIME as a datetime under every arcadedb.dateTimeImplementation, so WHERE n.d = date('2024-02-29'), inline patterns, ranges and toString(n.d) (2024-02-29) behave the same whichever Java class the setting picks. In v26.10.1 and earlier a DATE read through one of those classes matched no date() predicate, and a java.util.Calendar DATETIME matched no Cypher comparison.
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.10.2, 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`
(Since v26.10.2) a REGEXP on a DATE or DATETIME property is matched against the text of the class the value is held as, so it depends on these settings: 2024-01-15 with java.time.LocalDate, Mon Jan 15 00:00:00 CET 2024 with java.util.Date. Changing arcadedb.dateImplementation or arcadedb.dateTimeImplementation is refused when a pattern already declared would match no value of the new class, naming the properties: change or remove the pattern first (ALTER PROPERTY …​ REGEXP null). In the same way, changing arcadedb.dateFormat or arcadedb.dateTimeFormat is refused when an existing MIN or MAX of a date property could no longer be read with the new format. (Since v26.11.1) the same check runs when the class changes any other way, such as a -Darcadedb.dateImplementation=java.util.Date on the server command line or the Java API: opening the database cannot be refused, so it logs a WARNING naming each property whose REGEXP can no longer match any value. Remove the pattern with ALTER PROPERTY …​ REGEXP null, or set the setting back.

Time of Day, Zoned Datetime and Duration Types

(Available since v26.10.2) 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

A DURATION given as text, to duration('…​') or to a DURATION property, is read with the ISO 8601 grammar Neo4j applies: P[nY][nM][nW][nD][T[nH][nM][nS]], or the date-based PYYYY-MM-DDThh:mm:ss. Each component is a signed integer, only the last one present may carry a fraction (P1.5Y, PT1,5S), a sign before the P negates the whole duration (-P1Y2D), and the text must carry at least one component: P, PT and P1YT are refused (since v26.10.2).

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 (on a database upgraded from v26.10.1 or earlier, only once it is migrated, see below). Before 26.10.2 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 compare as in openCypher and Neo4j: the same instant in two zones is two different values, so 12:00:00+01:00 does not equal 11:00:00Z, with or without an index, and a unique index accepts both. Values are ordered by instant, then by UTC offset from west to east, then by zone name.

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.10.2 it kept the local wall clock of its zone.

In JSON, these four types are written as their ISO text, for example "2026-01-01T00:30+02:00" for a ZONED_DATETIME and "00:30+02:00" for an OFFSET_TIME, also inside a LIST or a MAP. This is what the HTTP API always returned, and (since v26.10.2) Result.toJSON(), the SQL asJSON() method and the PostgreSQL wire protocol agree with it. Before 26.10.2 they wrote a ZONED_DATETIME as epoch milliseconds, which lost the zone, and an OFFSET_TIME as null.

Migrating Cypher Temporals Stored as Strings

A database written by v26.10.1 or earlier holds these values as Strings in undeclared properties. Such a database keeps the old reading after the upgrade: openCypher still turns a String with the shape of a time, a zoned datetime or a duration on an undeclared property into a temporal value when it reads it, as before, and a new database created with v26.10.2 does not. The legacy reading ends when you run the one-off migration:

CALL db.migrateLegacyTemporals() YIELD records, properties

The procedure rewrites every String with a temporal shape on a property the schema does not declare into its native type, in batches of 1,000 records each committed in its own transaction, and then marks the database native, so from then on a String is a String. It yields the number of records and properties it rewrote. A declared property and the elements of a list are left alone. If it fails part way the database stays legacy, and running it again picks up what is left; on a database that is already native it converts nothing and yields 0 and 0.

  • It is one-way, and it converts every String with a temporal shape on an undeclared property, including one that was meant as text. Take a backup first, and quiesce writers while it runs: a String written meanwhile is converted too.

  • It changes a database setting, so the user needs the updateDatabaseSettings permission.

  • Under HA, run it on the leader. A follower refuses the schema change, while a request sent to a follower over HTTP is forwarded to the leader. The rewritten records and the native flag replicate to every follower.

To convert only some properties, or a property you want to keep declared, declare the property with its type and copy the String into it instead. 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 string is stored as the UTC wall clock of the instant it names, exactly as the same instant is when a client sends it typed (a Java OffsetDateTime or ZonedDateTime, a Bolt DateTime with an offset or a zone id), so 13:45:10+01:00 is stored as 12:45:10. A string with no offset, and one in UTC (Z or +00:00), keeps its wall clock, so 13:45:10Z 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 DATE has no time of day to move: the day written is the day stored, whatever the offset.

Before 26.11.1 a string with an offset was stored with the offset dropped (13:45:10+01:00 as 13:45:10) while the same instant typed as an OffsetDateTime was converted to UTC, so a client writing over HTTP or SQL and one writing over Bolt stored values hours apart. Values written before are not changed; rewrite them if they came from a client in another time zone. The date() function and the .asDatetime() method read a string the same way.

The rule holds when the caller names the pattern, too: a format that reads an offset or a zone out of the text (XXX, Z, VV, z) answers the instant it names, so '2020-01-02T03:04:05+02:00'.asDateTime("yyyy-MM-dd’T’HH:mm:ssXXX") and date('2020-01-02T03:04:05+02:00', "yyyy-MM-dd’T’HH:mm:ssXXX") are 2020-01-02T01:04:05, the value the schema’s own pattern gives for the same text. .asDate() keeps the day written. The third argument of date(), a time zone, is where a text that names none was written, and changes nothing for one that does. (Since v26.11.1; before, a named pattern dropped the offset it had read, and the time zone argument was ignored.) The functions date.parse() and date.fields() and the datetimeFormat of the importer read an offset the same way.

The database’s own time zone (setZoneId()) plays no part in where an instant is stored: the same rule holds on every write path, the bulk ones included (GraphBatch, the gRPC service, a TimeSeries column). (Since v26.11.1) the conversion the gRPC service and the other callers of DateUtils.dateTimeToTimestamp apply to a string no longer moves an offset onto the database’s zone first; before, in a database whose zone was not UTC, a string such as 2020-01-02T03:04:05+02:00 was stored hours away from the same instant written through SQL, and 2020-01-02T03:04:05Z shifted by the zone’s offset. A UTC database stores the same value as before.

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 unparsable date string was silently stored as NULL and the write reported success.

Every format, built in or configured, and every pattern passed to .asDate(), .asDatetime() or date(), is read strictly: a day or an hour that does not exist is refused, never moved to one that does. '2021-02-30' and '2021-04-31' are not dates, and '2021-02-03 24:00:00' is not a datetime: a write of one fails, and .asDate(), .asDatetime() and date() answer NULL for it. A comparison with such a literal, as in WHERE day = '2021-02-30', matches no record.

In a pattern, yyyy is the proleptic year, the year the ISO formats read, unless the pattern also names the era (G): so '0000-01-01'.asDate('yyyy-MM-dd') is the year 0 (1 BC), exactly as '0000-01-01'.asDate(), and formats back as 0000. A pattern with a 12-hour field (hh, KK) reads the AM/PM marker a when it has one, and the morning when it does not.

Before 26.11.1 an impossible date was moved to the last day of its month (2021-02-30 read as 2021-02-28) and 24:00:00 to midnight of the next day, a pattern with yyyy could not read the year 0, and no pattern with hh …​ a could be parsed at all.

Comparing and Subtracting Dates

A DATE compared with a DATETIME (of any precision) is that day at midnight, as SQL compares a date with a timestamp: with day = 2021-02-03 and time = 2021-02-03 12:00, day < time is true and day = time is false, whichever side of the operator each is on, and with an index on day or without one. Before 26.11.1 the datetime was narrowed to its day when it was on the right of the operator, so day = time was true there and false the other way round.

A DATETIME (of any precision) compared with a number reads the number as epoch milliseconds, and compares it exactly, whichever side of the operator it is on: with time at 2,000 milliseconds after the epoch, time = 2000 and 2000 = time are true, time < 2000.5 and 2000.5 > time are true, time = 2000.5 is false, and time ⇐ 1e19, time > -1e19 are true (a number beyond the range of a long is above or below every datetime; a NaN is above every datetime, as it is above every number). An index on the DATETIME follows the same rule, so the indexed and the unindexed query return the same rows. BETWEEN and a TIMESERIES timestamp bound follow the same rule, so DELETE FROM sensor WHERE ts >= 2000.5 removes exactly the samples SELECT FROM sensor WHERE ts >= 2000.5 returns. Before 26.11.1 a number on the left of the operator matched no datetime (2000 <> time was true for the datetime at 2,000 milliseconds, 2000 = time false), a fractional number was read as the millisecond it falls in (time = 2000.5 and time >= 2000.5 were true for the datetime at 2,000 milliseconds), and a number beyond the range of a long matched nothing. In a TIMESERIES type, ts >= 2000.5 selected the sample at 2,000 milliseconds, DELETE …​ WHERE ts = 2000.5 removed it, and ts ⇐ 1e19 selected nothing. (Available since v26.11.1)

Subtracting two dates answers the exact duration between them, whatever their precision: day - time with a DATETIME_NANOS operand three centuries away no longer overflows.

A time field in a .format() pattern formats a DATE as its midnight: day.format('yyyy-MM-dd HH:mm:ss') answers 2021-02-03 00:00:00, where it used to throw.

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'