4.9.3. DateTime: datetime()

Details for using the datetime() function.

4.9.3.1. Getting the current DateTime

datetime() returns the current DateTime value. If no time zone parameter is specified, the default time zone will be used.

Syntax: datetime([ {timezone} ])

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, datetime() must be invoked (datetime({}) is invalid).

Query.  

RETURN datetime() AS currentDateTime

The current date and time using the local time zone is returned.

Table 4.104. Result
currentDateTime

1 row

2019-09-02T12:40:23.629Z

Try this query live.  none RETURN datetime() AS currentDateTime

Query.  

RETURN datetime({ timezone: 'America/Los Angeles' }) AS currentDateTimeInLA

The current date and time of day in California is returned.

Table 4.105. Result
currentDateTimeInLA

1 row

2019-09-02T05:40:23.637-07:00[America/Los_Angeles]

Try this query live.  none RETURN datetime( {timezone: 'America/Los Angeles'} ) AS currentDateTimeInLA

datetime.transaction()

datetime.transaction() returns the current DateTime value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: datetime.transaction([ {timezone} ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query.  

RETURN datetime.transaction() AS currentDateTime

Table 4.106. Result
currentDateTime

1 row

2019-09-02T12:40:23.638Z

Try this query live.  none RETURN datetime.transaction() AS currentDateTime

Query.  

RETURN datetime.transaction('America/Los Angeles') AS currentDateTimeInLA

Table 4.107. Result
currentDateTimeInLA

1 row

2019-09-02T05:40:23.646-07:00[America/Los_Angeles]

Try this query live.  none RETURN datetime.transaction('America/Los Angeles') AS currentDateTimeInLA

datetime.statement()

datetime.statement() returns the current DateTime value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: datetime.statement([ {timezone} ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query.  

RETURN datetime.statement() AS currentDateTime

Table 4.108. Result
currentDateTime

1 row

2019-09-02T12:40:23.661Z

Try this query live.  none RETURN datetime.statement() AS currentDateTime

datetime.realtime()

datetime.realtime() returns the current DateTime value using the realtime clock. This value will be the live clock of the system.

Syntax: datetime.realtime([ {timezone} ])

Returns:

A DateTime.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query.  

RETURN datetime.realtime() AS currentDateTime

Table 4.109. Result
currentDateTime

1 row

2019-09-02T12:40:23.668Z

Try this query live.  none RETURN datetime.realtime() AS currentDateTime

4.9.3.2. Creating a calendar (Year-Month-Day) DateTime

datetime() returns a DateTime value with the specified year, month, day, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, month, day, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The month component will default to 1 if month is omitted.

The day of the month component will default to 1 if day is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, month, day, hour, minute, and second may be omitted; i.e. it is possible to specify only year, month and day, but specifying year, month, day and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query.  

UNWIND [
datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789 }),
datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 645, timezone: '+01:00' }),
datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, timezone: '+01:00' }),
datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14 }),
datetime({ year:1984, month:10, day:11, hour:12, minute:31, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, month:10, day:11, hour:12, timezone: '+01:00' }),
datetime({ year:1984, month:10, day:11, timezone: 'Europe/Stockholm' })
] AS theDate
RETURN theDate

Table 4.110. Result
theDate

8 rows

1984-10-11T12:31:14.123456789Z

1984-10-11T12:31:14.645+01:00

1984-10-11T12:31:14.645876123+01:00[Europe/Stockholm]

1984-10-11T12:31:14+01:00

1984-10-11T12:31:14Z

1984-10-11T12:31+01:00[Europe/Stockholm]

1984-10-11T12:00+01:00

1984-10-11T00:00+01:00[Europe/Stockholm]

Try this query live.  none UNWIND [ datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 123, microsecond: 456, nanosecond: 789}), datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 645, timezone: '+01:00'}), datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm'}), datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, timezone: '+01:00'}), datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14}), datetime({year:1984, month:10, day:11, hour:12, minute:31, timezone: 'Europe/Stockholm'}), datetime({year:1984, month:10, day:11, hour:12, timezone: '+01:00'}), datetime({year:1984, month:10, day:11, timezone: 'Europe/Stockholm'}) ] as theDate RETURN theDate

4.9.3.3. Creating a week (Year-Week-Day) DateTime

datetime() returns a DateTime value with the specified year, week, dayOfWeek, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, week, dayOfWeek, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

year

An expression consisting of at least four digits that specifies the year.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The week component will default to 1 if week is omitted.

The day of the week component will default to 1 if dayOfWeek is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, week, dayOfWeek, hour, minute, and second may be omitted; i.e. it is possible to specify only year, week and dayOfWeek, but specifying year, week, dayOfWeek and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query.  

UNWIND [
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, millisecond: 645 }),
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00' }),
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14 }),
datetime({ year:1984, week:10, dayOfWeek:3, hour:12, timezone: '+01:00' }),
datetime({ year:1984, week:10, dayOfWeek:3, timezone: 'Europe/Stockholm' })
] AS theDate
RETURN theDate

Table 4.111. Result
theDate

7 rows

1984-03-07T12:31:14.645Z

1984-03-07T12:31:14.645876+01:00

1984-03-07T12:31:14.645876123+01:00[Europe/Stockholm]

1984-03-07T12:31:14+01:00[Europe/Stockholm]

1984-03-07T12:31:14Z

1984-03-07T12:00+01:00

1984-03-07T00:00+01:00[Europe/Stockholm]

Try this query live.  none UNWIND [ datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, millisecond: 645}), datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}), datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: 'Europe/Stockholm'}), datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14, timezone: 'Europe/Stockholm'}), datetime({year:1984, week:10, dayOfWeek:3, hour:12, minute:31, second:14}), datetime({year:1984, week:10, dayOfWeek:3, hour:12, timezone: '+01:00'}), datetime({year:1984, week:10, dayOfWeek:3, timezone: 'Europe/Stockholm'}) ] as theDate RETURN theDate

4.9.3.4. Creating a quarter (Year-Quarter-Day) DateTime

datetime() returns a DateTime value with the specified year, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, quarter, dayOfQuarter, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

year

An expression consisting of at least four digits that specifies the year.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The quarter component will default to 1 if quarter is omitted.

The day of the quarter component will default to 1 if dayOfQuarter is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, quarter, dayOfQuarter, hour, minute, and second may be omitted; i.e. it is possible to specify only year, quarter and dayOfQuarter, but specifying year, quarter, dayOfQuarter and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query.  

UNWIND [
datetime({ year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, microsecond: 645876 }),
datetime({ year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, timezone: '+01:00' }),
datetime({ year:1984, quarter:3, dayOfQuarter: 45, hour:12, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, quarter:3, dayOfQuarter: 45 })
] AS theDate
RETURN theDate

Table 4.112. Result
theDate

4 rows

1984-08-14T12:31:14.645876Z

1984-08-14T12:31:14+01:00

1984-08-14T12:00+02:00[Europe/Stockholm]

1984-08-14T00:00Z

Try this query live.  none UNWIND [ datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, microsecond: 645876}), datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, minute:31, second:14, timezone: '+01:00'}), datetime({year:1984, quarter:3, dayOfQuarter: 45, hour:12, timezone: 'Europe/Stockholm'}), datetime({year:1984, quarter:3, dayOfQuarter: 45}) ] as theDate RETURN theDate

4.9.3.5. Creating an ordinal (Year-Day) DateTime

datetime() returns a DateTime value with the specified year, ordinalDay, hour, minute, second, millisecond, microsecond, nanosecond and timezone component values.

Syntax: datetime({year [, ordinalDay, hour, minute, second, millisecond, microsecond, nanosecond, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

year

An expression consisting of at least four digits that specifies the year.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

The ordinal day of the year component will default to 1 if ordinalDay is omitted.

The hour component will default to 0 if hour is omitted.

The minute component will default to 0 if minute is omitted.

The second component will default to 0 if second is omitted.

Any missing millisecond, microsecond or nanosecond values will default to 0.

The timezone component will default to the configured default time zone if timezone is omitted.

If millisecond, microsecond and nanosecond are given in combination (as part of the same set of parameters), the individual values must be in the range 0 to 999.

The least significant components in the set year, ordinalDay, hour, minute, and second may be omitted; i.e. it is possible to specify only year and ordinalDay, but specifying year, ordinalDay and minute is not permitted.

One or more of millisecond, microsecond and nanosecond can only be specified as long as second is also specified.

Query.  

UNWIND [
datetime({ year:1984, ordinalDay:202, hour:12, minute:31, second:14, millisecond: 645 }),
datetime({ year:1984, ordinalDay:202, hour:12, minute:31, second:14, timezone: '+01:00' }),
datetime({ year:1984, ordinalDay:202, timezone: 'Europe/Stockholm' }),
datetime({ year:1984, ordinalDay:202 })
] AS theDate
RETURN theDate

Table 4.113. Result
theDate

4 rows

1984-07-20T12:31:14.645Z

1984-07-20T12:31:14+01:00

1984-07-20T00:00+02:00[Europe/Stockholm]

1984-07-20T00:00Z

Try this query live.  none UNWIND [ datetime({year:1984, ordinalDay:202, hour:12, minute:31, second:14, millisecond: 645}), datetime({year:1984, ordinalDay:202, hour:12, minute:31, second:14, timezone: '+01:00'}), datetime({year:1984, ordinalDay:202, timezone: 'Europe/Stockholm'}), datetime({year:1984, ordinalDay:202}) ] as theDate RETURN theDate

4.9.3.6. Creating a DateTime from a string

datetime() returns the DateTime value obtained by parsing a string representation of a temporal value.

Syntax: datetime(temporalValue)

Returns:

A DateTime.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for dates, times and time zones.

datetime(null) returns the current date and time.

The timezone component will default to the configured default time zone if it is omitted.

temporalValue must denote a valid date and time; i.e. a temporalValue denoting 30 February 2001 is invalid.

Query.  

UNWIND [
datetime('2015-07-21T21:40:32.142+0100'),
datetime('2015-W30-2T214032.142Z'),
datetime('2015T214032-0100'),
datetime('20150721T21:40-01:30'),
datetime('2015-W30T2140-02'),
datetime('2015202T21+18:00'),
datetime('2015-07-21T21:40:32.142[Europe/London]'),
datetime('2015-07-21T21:40:32.142-04[America/New_York]')
] AS theDate
RETURN theDate

Table 4.114. Result
theDate

8 rows

2015-07-21T21:40:32.142+01:00

2015-07-21T21:40:32.142Z

2015-01-01T21:40:32-01:00

2015-07-21T21:40-01:30

2015-07-20T21:40-02:00

2015-07-21T21:00+18:00

2015-07-21T21:40:32.142+01:00[Europe/London]

2015-07-21T21:40:32.142-04:00[America/New_York]

Try this query live.  none UNWIND [ datetime('2015-07-21T21:40:32.142+0100'), datetime('2015-W30-2T214032.142Z'), datetime('2015T214032-0100'), datetime('20150721T21:40-01:30'), datetime('2015-W30T2140-02'), datetime('2015202T21+18:00'), datetime('2015-07-21T21:40:32.142[Europe/London]'), datetime('2015-07-21T21:40:32.142-04[America/New_York]') ] AS theDate RETURN theDate

4.9.3.7. Creating a DateTime using other temporal values as components

datetime() returns the DateTime value obtained by selecting and composing components from another temporal value. In essence, this allows a Date, LocalDateTime, Time or LocalTime value to be converted to a DateTime, and for "missing" components to be provided.

Syntax: datetime({datetime [, year, …​, timezone]}) | datetime({date [, year, …​, timezone]}) | datetime({time [, year, …​, timezone]}) | datetime({date, time [, year, …​, timezone]})

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

datetime

A DateTime value.

date

A Date value.

time

A Time value.

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

hour

An integer between 0 and 23 that specifies the hour of the day.

minute

An integer between 0 and 59 that specifies the number of minutes.

second

An integer between 0 and 59 that specifies the number of seconds.

millisecond

An integer between 0 and 999 that specifies the number of milliseconds.

microsecond

An integer between 0 and 999,999 that specifies the number of microseconds.

nanosecond

An integer between 0 and 999,999,999 that specifies the number of nanoseconds.

timezone

An expression that specifies the time zone.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of datetime, date and/or time.

datetime(dd) may be written instead of datetime({datetime: dd}).

Selecting a Time or DateTime value as the time component also selects its time zone. If a LocalTime or LocalDateTime is selected instead, the default time zone is used. In any case, the time zone can be overridden explicitly.

Selecting a DateTime as the datetime component and overwriting the time zone will adjust the local time to keep the same point in time.

Selecting a DateTime or Time as the time component and overwriting the time zone will adjust the local time to keep the same point in time.

The following query shows the various usages of datetime({date [, year, …​, timezone]})

Query.  

WITH date({ year:1984, month:10, day:11 }) AS dd
RETURN datetime({ date:dd, hour: 10, minute: 10, second: 10 }) AS dateHHMMSS,
datetime({ date:dd, hour: 10, minute: 10, second: 10, timezone:'+05:00' }) AS dateHHMMSSTimezone,
datetime({ date:dd, day: 28, hour: 10, minute: 10, second: 10 }) AS dateDDHHMMSS,
datetime({ date:dd, day: 28, hour: 10, minute: 10, second: 10, timezone:'Pacific/Honolulu' }) AS dateDDHHMMSSTimezone

Table 4.115. Result
dateHHMMSS dateHHMMSSTimezone dateDDHHMMSS dateDDHHMMSSTimezone

1 row

1984-10-11T10:10:10Z

1984-10-11T10:10:10+05:00

1984-10-28T10:10:10Z

1984-10-28T10:10:10-10:00[Pacific/Honolulu]

Try this query live.  none WITH date({year:1984, month:10, day:11}) AS dd RETURN datetime({date:dd, hour: 10, minute: 10, second: 10}) AS dateHHMMSS, datetime({date:dd, hour: 10, minute: 10, second: 10, timezone:'+05:00'}) AS dateHHMMSSTimezone, datetime({date:dd, day: 28, hour: 10, minute: 10, second: 10}) AS dateDDHHMMSS, datetime({date:dd, day: 28, hour: 10, minute: 10, second: 10, timezone:'Pacific/Honolulu'}) AS dateDDHHMMSSTimezone

The following query shows the various usages of datetime({time [, year, …​, timezone]})

Query.  

WITH time({ hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00' }) AS tt
RETURN datetime({ year:1984, month:10, day:11, time:tt }) AS YYYYMMDDTime,
datetime({ year:1984, month:10, day:11, time:tt, timezone:'+05:00' }) AS YYYYMMDDTimeTimezone,
datetime({ year:1984, month:10, day:11, time:tt, second: 42 }) AS YYYYMMDDTimeSS,
datetime({ year:1984, month:10, day:11, time:tt, second: 42, timezone:'Pacific/Honolulu' }) AS YYYYMMDDTimeSSTimezone

Table 4.116. Result
YYYYMMDDTime YYYYMMDDTimeTimezone YYYYMMDDTimeSS YYYYMMDDTimeSSTimezone

1 row

1984-10-11T12:31:14.645876+01:00

1984-10-11T16:31:14.645876+05:00

1984-10-11T12:31:42.645876+01:00

1984-10-11T01:31:42.645876-10:00[Pacific/Honolulu]

Try this query live.  none WITH time({hour:12, minute:31, second:14, microsecond: 645876, timezone: '+01:00'}) AS tt RETURN datetime({year:1984, month:10, day:11, time:tt}) AS YYYYMMDDTime, datetime({year:1984, month:10, day:11, time:tt, timezone:'+05:00'}) AS YYYYMMDDTimeTimezone, datetime({year:1984, month:10, day:11, time:tt, second: 42}) AS YYYYMMDDTimeSS, datetime({year:1984, month:10, day:11, time:tt, second: 42, timezone:'Pacific/Honolulu'}) AS YYYYMMDDTimeSSTimezone

The following query shows the various usages of datetime({date, time [, year, …​, timezone]}); i.e. combining a Date and a Time value to create a single DateTime value:

Query.  

WITH date({ year:1984, month:10, day:11 }) AS dd,
localtime({ hour:12, minute:31, second:14, millisecond: 645 }) AS tt
RETURN datetime({ date:dd, time:tt }) AS dateTime,
datetime({ date:dd, time:tt, timezone:'+05:00' }) AS dateTimeTimezone,
datetime({ date:dd, time:tt, day: 28, second: 42 }) AS dateTimeDDSS,
datetime({ date:dd, time:tt, day: 28, second: 42, timezone:'Pacific/Honolulu' }) AS dateTimeDDSSTimezone

Table 4.117. Result
dateTime dateTimeTimezone dateTimeDDSS dateTimeDDSSTimezone

1 row

1984-10-11T12:31:14.645Z

1984-10-11T12:31:14.645+05:00

1984-10-28T12:31:42.645Z

1984-10-28T12:31:42.645-10:00[Pacific/Honolulu]

Try this query live.  none WITH date({year:1984, month:10, day:11}) AS dd, localtime({hour:12, minute:31, second:14, millisecond: 645}) AS tt RETURN datetime({date:dd, time:tt}) as dateTime, datetime({date:dd, time:tt, timezone:'+05:00'}) AS dateTimeTimezone, datetime({date:dd, time:tt, day: 28, second: 42}) AS dateTimeDDSS, datetime({date:dd, time:tt, day: 28, second: 42, timezone:'Pacific/Honolulu'}) AS dateTimeDDSSTimezone

The following query shows the various usages of datetime({datetime [, year, …​, timezone]})

Query.  

WITH datetime({ year:1984, month:10, day:11, hour:12, timezone: 'Europe/Stockholm' }) AS dd
RETURN datetime({ datetime:dd }) AS dateTime,
datetime({ datetime:dd, timezone:'+05:00' }) AS dateTimeTimezone,
datetime({ datetime:dd, day: 28, second: 42 }) AS dateTimeDDSS,
datetime({ datetime:dd, day: 28, second: 42, timezone:'Pacific/Honolulu' }) AS dateTimeDDSSTimezone

Table 4.118. Result
dateTime dateTimeTimezone dateTimeDDSS dateTimeDDSSTimezone

1 row

1984-10-11T12:00+01:00[Europe/Stockholm]

1984-10-11T16:00+05:00

1984-10-28T12:00:42+01:00[Europe/Stockholm]

1984-10-28T01:00:42-10:00[Pacific/Honolulu]

Try this query live.  none WITH datetime({year:1984, month:10, day:11, hour:12, timezone: 'Europe/Stockholm'}) AS dd RETURN datetime({datetime:dd}) AS dateTime, datetime({datetime:dd, timezone:'+05:00'}) AS dateTimeTimezone, datetime({datetime:dd, day: 28, second: 42}) AS dateTimeDDSS, datetime({datetime:dd, day: 28, second: 42, timezone:'Pacific/Honolulu'}) AS dateTimeDDSSTimezone

4.9.3.8. Creating a DateTime from a timestamp

datetime() returns the DateTime value at the specified number of seconds or milliseconds from the UNIX epoch in the UTC time zone.

Conversions to other temporal instant types from UNIX epoch representations can be achieved by transforming a DateTime value to one of these types.

Syntax: datetime({ epochSeconds | epochMillis })

Returns:

A DateTime.

Arguments:

Name Description

A single map consisting of the following:

  

epochSeconds

A numeric value representing the number of seconds from the UNIX epoch in the UTC time zone.

epochMillis

A numeric value representing the number of milliseconds from the UNIX epoch in the UTC time zone.

Considerations:

epochSeconds/epochMillis may be used in conjunction with nanosecond

Query.  

RETURN datetime({ epochSeconds:timestamp()/ 1000, nanosecond: 23 }) AS theDate

Table 4.119. Result
theDate

1 row

2019-09-02T12:40:24.000000023Z

Try this query live.  none RETURN datetime({epochSeconds:timestamp() / 1000, nanosecond: 23}) AS theDate

Query.  

RETURN datetime({ epochMillis: 424797300000 }) AS theDate

Table 4.120. Result
theDate

1 row

1983-06-18T15:15Z

Try this query live.  none RETURN datetime({epochMillis: 424797300000}) AS theDate

4.9.3.9. Truncating a DateTime

datetime.truncate() returns the DateTime value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the DateTime returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, day — with some value x — may be provided when the truncation unit is year in order to ensure the returned value has the day set to x instead of the default day (which is 1).

Syntax: datetime.truncate(unit, temporalInstantValue [, mapOfComponents ])

Returns:

A DateTime.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {millennium, century, decade, year, weekYear, quarter, month, week, day, hour, minute, second, millisecond, microsecond}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Date}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit. During truncation, a time zone can be attached or overridden using the key timezone.

Considerations:

temporalInstantValue cannot be a Date value if unit is one of {hour, minute, second, millisecond, microsecond}.

The time zone of temporalInstantValue may be overridden; for example, datetime.truncate('minute', input, {timezone:'+0200'}).

If temporalInstantValue is one of {Time, DateTime} — a value with a time zone — and the time zone is overridden, no time conversion occurs.

If temporalInstantValue is one of {LocalDateTime, Date} — a value without a time zone — and the time zone is not overridden, the configured default time zone will be used.

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'day', mapOfComponents cannot contain information pertaining to a month.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

Query.  

WITH datetime({ year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+03:00' }) AS d
RETURN datetime.truncate('millennium', d, { timezone:'Europe/Stockholm' }) AS truncMillenium,
datetime.truncate('year', d, { day:5 }) AS truncYear,
datetime.truncate('month', d) AS truncMonth,
datetime.truncate('day', d, { millisecond:2 }) AS truncDay,
datetime.truncate('hour', d) AS truncHour,
datetime.truncate('second', d) AS truncSecond

Table 4.121. Result
truncMillenium truncYear truncMonth truncDay truncHour truncSecond

1 row

2000-01-01T00:00+01:00[Europe/Stockholm]

2017-01-05T00:00+03:00

2017-11-01T00:00+03:00

2017-11-11T00:00:00.002+03:00

2017-11-11T12:00+03:00

2017-11-11T12:31:14+03:00

Try this query live.  none WITH datetime({year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+03:00'}) AS d RETURN datetime.truncate('millennium', d, {timezone:'Europe/Stockholm'}) AS truncMillenium, datetime.truncate('year', d, {day:5}) AS truncYear, datetime.truncate('month', d) AS truncMonth, datetime.truncate('day', d, {millisecond:2}) AS truncDay, datetime.truncate('hour', d) AS truncHour, datetime.truncate('second', d) AS truncSecond