Skip to main content
Version: 3.12.0

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