Date Functions

Date Functions

N1QL date functions return the system clock value or manipulate the date string.

The following table contains a list of the date functions that you can use in N1QL statements.

Table 1. Date functions
Function Description
CLOCK_MILLIS()

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

CLOCK_STR([fmt])

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query. Supported formats:

  • "2006-01-02T15:04:05.999Z07:00": Default format. (ISO8601 / RFC3339)
  • "2006-01-02T15:04:05Z07:00" (ISO8601 / RFC3339)
  • "2006-01-02T15:04:05.999"
  • "2006-01-02T15:04:05"
  • "2006-01-02 15:04:05.999Z07:00"
  • "2006-01-02 15:04:05Z07:00"
  • "2006-01-02 15:04:05.999"
  • "2006-01-02 15:04:05"
  • "2006-01-02"
  • "15:04:05.999Z07:00"
  • "15:04:05Z07:00"
  • "15:04:05.999"
  • "15:04:05"

DATE_ADD_MILLIS(expression, n, part)

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result. Parts:

  • "millennium"
  • "century"
  • "decade"
  • "year"
  • "quarter"
  • "month"
  • "week"
  • "day"
  • "hour"
  • "minute"
  • "second"
  • "millisecond"

DATE_ADD_STR(expression, n, part)

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

DATE_DIFF_MILLIS(expression1, expression2, part)

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

  • "millennium"
  • "century"
  • "decade"
  • "year"
  • "week"
  • "day"
  • "hour"
  • "minute"
  • "second"
  • "millisecond"

DATE_DIFF_STR(expression1, expression2, part)

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

DATE_PART_MILLIS(expression, part)

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

  • "millennium"
  • "century"
  • "decade": Floor(year / 10)
  • "year"
  • "quarter": Valid values: 1 to 4.
  • "month": Valid values: 1 to 12.
  • "day": Valid values: 1 to 31.
  • "hour": Valid values: 0 to 23.
  • "minute": Valid values: 0 to 59.
  • "second": Valid values: 0 to 59.
  • "millisecond": Valid values: 0 to 999.
  • "week": Valid values: 1 to 53; ceil(day_of_year / 7.0)
  • "day_of_year", "doy": Valid values: 1 to 366.
  • "day_of_week", "dow": Valid values: 0 to 6.
  • "iso_week": Valid values: 1 to 53. Use with "iso_year".
  • "iso_year": Use with "iso_week".
  • "iso_dow":> Valid values: 1 to 7.
  • "timezone": Offset from UTC in seconds.
  • "timezone_hour": Hour component of timezone offset.
  • "timezone_minute": Minute component of timezone offset. Valid values: 0 to 59.

DATE_PART_STR(expression, part)

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

DATE_TRUNC_MILLIS(expression, part)

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

DATE_TRUNC_STR(expression, part)

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

DURATION_TO_STR(expression)

Converts duration, specified as a floating point number followed by a time unit such as 1.3ms, 3.25ns, or 7.6666s, to a query timing string. The conversion function supports nano- (ns) , micro- ( μs), milli- (ms), and second (s) time units.

MILLIS(expression), STR_TO_MILLIS(expression)

Returns date that has been converted in a supported format to UNIX milliseconds.

MILLIS_TO_STR(expression [, fmt ])

Returns the string in the supported format to which the UNIX milliseconds has been converted.

MILLIS_TO_UTC(expression [, fmt ])

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

MILLIS_TO_ZONE_NAME(expression, tz_name [, fmt ])

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

NOW_MILLIS()

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

NOW_STR([ fmt ])

Returns statement time stamp as a string in a supported format; does not vary during a query.

STR_TO_DURATION(expression)

Converts a string to duration, which is a floating point number followed by a time unit such as s, μs, ms, or ns. The conversion function supports nano- (ns) , micro- ( μs), milli- (ms), and second (s) time units.

STR_TO_MILLIS(expression), MILLIS(expression)

Converts date in a supported format to UNIX milliseconds.

STR_TO_UTC(expression)

Converts the ISO 8601 time stamp to UTC.

STR_TO_ZONE_NAME(expression, tz_name)

Converts the supported time stamp string to the named time zone.

Example

The following query retrieves purchase information for an e-commerce report. The report lists unique customers that purchased something from the company website in the last month. This information is used to identify user activity and growth.


SELECT distinct count(purchases.customerId)
  FROM purchases
  WHERE STR_TO_MILLIS(purchases.purchasedAt)
    BETWEEN STR_TO_MILLIS("2014-02-01") AND STR_TO_MILLIS("2014-03-01")       
        
      

Returns:


{
   "results": [
     {
       "$1": 3831
     }
   ]
 }