Date and time types

This topic will describe the DATE, TIME, DATETIME, TIMESTAMP, and DURATION types.

Precautions

• While inserting time-type property values with DATE, TIME, and DATETIME, NebulaGraph transforms them to a UTC time according to the timezone specified with the timezone_name parameter in the configuration files.

  Note

  To change the timezone, modify the timezone_name value in the configuration files of all NebulaGraph services.

• date(), time(), and datetime() can convert a time-type property with a specified timezone. For example, datetime("2017-03-04 22:30:40.003000+08:00") or datetime("2017-03-04T22:30:40.003000[Asia/Shanghai]").

• date(), time(), datetime(), and timestamp() all accept empty parameters to return the current date, time, and datetime.

• date(), time(), and datetime() all accept the property name to return a specific property value of itself. For example, date().month returns the current month, while time("02:59:40").minute returns the minutes of the importing time.

• For time operations it is recommended to use duration() to calculate the offset of the moment. Addition and subtraction of date() and date(), timestamp() and timestamp() are also supported.

• When setting the year of the time as a negative number, you need to use Map type data.

OpenCypher Compatibility

In nGQL:

• Year, month, day, hour, minute, second, millisecond, and microsecond are supported, while the nanosecond is not supported.

• localdatetime() is not supported.

• Most string time formats are not supported. The exceptions are YYYY-MM-DDThh:mm:ss and YYYY-MM-DD hh:mm:ss.

• The single-digit string time format is supported. For example, time("1:1:1").

DATE

The DATE type is used for values with a date part but no time part. Nebula Graph retrieves and displays DATE values in the YYYY-MM-DD format. The supported range is -32768-01-01 to 32767-12-31.

The properties of date() include year, month, and day. date() supports the input of YYYYY, YYYYY-MM or YYYYY-MM-DD, and defaults to 01 for an untyped month or day.

nebula> RETURN DATE({year:-123, month:12, day:3});
+------------------------------------+
| date({year:-(123),month:12,day:3}) |
+------------------------------------+
| -123-12-03                         |
+------------------------------------+

nebula> RETURN DATE("23333");
+---------------+
| date("23333") |
+---------------+
| 23333-01-01   |
+---------------+

nebula> RETURN DATE("2023-12-12") - DATE("2023-12-11");
+-----------------------------------------+
| (date("2023-12-12")-date("2023-12-11")) |
+-----------------------------------------+
| 1                                       |
+-----------------------------------------+

TIME

The TIME type is used for values with a time part but no date part. Nebula Graph retrieves and displays TIME values in hh:mm:ss.msmsmsususus format. The supported range is 00:00:00.000000 to 23:59:59.999999.

The properties of time() include hour, minute, and second.

DATETIME

The DATETIME type is used for values that contain both date and time parts. Nebula Graph retrieves and displays DATETIME values in YYYY-MM-DDThh:mm:ss.msmsmsususus format. The supported range is -32768-01-01T00:00:00.000000 to 32767-12-31T23:59:59.999999.

• The properties of datetime() include year, month, day, hour, minute, and second.

• datetime() can convert TIMESTAMP to DATETIME. The value range of TIMESTAMP is 0~9223372036.

• datetime() supports an int argument. The int argument specifies a timestamp.

# To get the current date and time.
nebula> RETURN datetime();
+----------------------------+
| datetime()                 |
+----------------------------+
| 2022-08-29T06:37:08.933000 |
+----------------------------+

# To get the current hour.
nebula> RETURN datetime().hour;
+-----------------+
| datetime().hour |
+-----------------+
| 6               |
+-----------------+

# To get date time from a given timestamp.
nebula> RETURN datetime(timestamp(1625469277));
+---------------------------------+
| datetime(timestamp(1625469277)) |
+---------------------------------+
| 2021-07-05T07:14:37.000000      |
+---------------------------------+

nebula> RETURN datetime(1625469277);
+----------------------------+
| datetime(1625469277)       |
+----------------------------+
| 2021-07-05T07:14:37.000000 |
+----------------------------+

TIMESTAMP

The TIMESTAMP data type is used for values that contain both date and time parts. It has a range of 1970-01-01T00:00:01 UTC to 2262-04-11T23:47:16 UTC.

TIMESTAMP has the following features:

• Stored and displayed in the form of a timestamp, such as 1615974839, which means 2021-03-17T17:53:59.

• Supported TIMESTAMP querying methods: timestamp and timestamp() function.

• Supported TIMESTAMP inserting methods: timestamp, timestamp() function, and now() function.

• timestamp() function accepts empty arguments to get the current timestamp. It can pass an integer arguments to identify the integer as a timestamp and the range of passed integer is: 0~9223372036。

• timestamp() function can convert DATETIME to TIMESTAMP, and the data type of DATETIME should be a string.

• The underlying storage data type is int64.

# To get the current timestamp.
nebula> RETURN timestamp();
+-------------+
| timestamp() |
+-------------+
| 1625469277  |
+-------------+

# To get a timestamp from given date and time.
nebula> RETURN timestamp("2022-01-05T06:18:43");
+----------------------------------+
| timestamp("2022-01-05T06:18:43") |
+----------------------------------+
| 1641363523                       |
+----------------------------------+

# To get a timestamp using datetime().
nebula> RETURN timestamp(datetime("2022-08-29T07:53:10.939000"));
+---------------------------------------------------+
| timestamp(datetime("2022-08-29T07:53:10.939000")) |
+---------------------------------------------------+
| 1661759590                                        |
+---------------------------------------------------+    

Note

The date and time format string passed into timestamp() cannot include any millisecond and microsecond, but the date and time format string passed into timestamp(datetime()) can include a millisecond and a microsecond.

DURATION

The DURATION data type is used to indicate a period of time. Map data that are freely combined by years, months, days, hours, minutes, and seconds indicates the DURATION.

DURATION has the following features:

• Creating indexes for DURATION is not supported.

• DURATION can be used to calculate the specified time.

Examples

1. Create a tag named date1 with three properties: DATE, TIME, and DATETIME.

   nebula> CREATE TAG IF NOT EXISTS date1(p1 date, p2 time, p3 datetime);
   

2. Insert a vertex named test1.

   nebula> INSERT VERTEX date1(p1, p2, p3) VALUES "test1":(date("2021-03-17"), time("17:53:59"), datetime("2017-03-04T22:30:40.003000[Asia/Shanghai]"));
   

3. Query whether the value of property p1 on the test1 tag is 2021-03-17.

   nebula> MATCH (v:date1)  RETURN v.date1.p1 == date("2021-03-17");
   +----------------------------------+
   | (v.date1.p1==date("2021-03-17")) |
   +----------------------------------+
   | true                             |
   +----------------------------------+
   

4. Return the content of the property p1 on test1.

   nebula> CREATE TAG INDEX IF NOT EXISTS date1_index ON date1(p1);
   nebula> REBUILD TAG INDEX date1_index;
   nebula> MATCH (v:date1) RETURN v.date1.p1;
   +------------------+
   | v.date1.p1.month |
   +------------------+
   | 3                |
   +------------------+
   

5. Search for vertices with p3 property values less than 2023-01-01T00:00:00.000000, and return the p3 values.

   nebula> MATCH (v:date1)  \
   WHERE v.date1.p3 < datetime("2023-01-01T00:00:00.000000") \
   RETURN v.date1.p3;
   +----------------------------+
   | v.date1.p3                 |
   +----------------------------+
   | 2017-03-04T14:30:40.003000 |
   +----------------------------+
   

6. Create a tag named school with the property of TIMESTAMP.

   nebula> CREATE TAG IF NOT EXISTS school(name string , found_time timestamp);
   

7. Insert a vertex named DUT with a found-time timestamp of "1988-03-01T08:00:00".

   # Insert as a timestamp. The corresponding timestamp of 1988-03-01T08:00:00 is 573177600, or 573206400 UTC.
   nebula> INSERT VERTEX school(name, found_time) VALUES "DUT":("DUT", 573206400);
   
   # Insert in the form of date and time.
   nebula> INSERT VERTEX school(name, found_time) VALUES "DUT":("DUT", timestamp("1988-03-01T08:00:00"));
   

8. Insert a vertex named dut and store time with now() or timestamp() functions.

   # Use now() function to store time
   nebula> INSERT VERTEX school(name, found_time) VALUES "dut":("dut", now());
   
   # Use timestamp() function to store time
   nebula> INSERT VERTEX school(name, found_time) VALUES "dut":("dut", timestamp());
   

You can also use WITH statement to set a specific date and time, or to perform calculations. For example:

nebula> WITH time({hour: 12, minute: 31, second: 14, millisecond:111, microsecond: 222}) AS d RETURN d;
+-----------------+
| d               |
+-----------------+
| 12:31:14.111222 |
+-----------------+

nebula> WITH date({year: 1984, month: 10, day: 11}) AS x RETURN x + 1;
+------------+
| (x+1)      |
+------------+
| 1984-10-12 |
+------------+

nebula> WITH date('1984-10-11') as x, duration({years: 12, days: 14, hours: 99, minutes: 12}) as d \
        RETURN x + d AS sum, x - d AS diff;
+------------+------------+
| sum        | diff       |
+------------+------------+
| 1996-10-29 | 1972-09-23 |
+------------+------------+

---

Last update: July 14, 2023