Over a million developers have joined DZone.

Comparing Oracle and N1QL Support for the Date-Time Feature (Part 1)

DZone's Guide to

Comparing Oracle and N1QL Support for the Date-Time Feature (Part 1)

If you are dealing with date-time formats and datatypes, you know that different databases deal with them differently. Let's compare date-time supprt in N1QL and Oracle!

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

Date and time formats/types are very different for different databases. In this article, we will compare Couchbase N1QL date-time functions with Oracle's date-time support.

Oracle contains multiple data types associated with date-time support — namely DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The TIMESTAMP data type is an extension of the DATE type.

Date values can be represented either as literals or as numeric values. The literal needs to be in a specified format. The format for the date times can be set using the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, and NLS_DATE_LANGUAGE parameters. (See the table in the article linked below for detailed comparison with examples!)

With Couchbase, date-time is done a little differently. All dates and times should be stored as strings that follow the ISO 8601 extended date time format. N1QL contains DATETIME functions that can be used to and extract these formatted strings. The DATE and TIME for a specific timezone can also be represented as a Unix timestamp in milliseconds. This essentially means that unlike Oracle, where the format of the input Date and time can change based on the values of the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT, the format for dates in Couchbase follows a strict set. (Again, see the table in the article linked below for detailed comparison with examples.)

For example, in Oracle, '2008-DEC-25 17:30' is a valid date given NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI'.

However, to represent the same value in Couchbase, the user needs to use one of the given formats (see here). This will be 2008-12-25 17:30:00.

In Oracle, fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND date-time field. It can be a number (0 to 9) with the default being 6.

For example, in Oracle, the timestamp format can be given as TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FFF'. Using this, we can define the timestamp to be '2006-01-02 15:04:05.999'.

N1QL has support for fractional seconds similar to Oracle. This is seen when using the format "2006-01-02T15:04:05.999". However, N1QL supports three-digit precision (nanosecond precision) and Oracle supports up to nine-digit fractional second precision.

This means that if we specify the date "2006-01-02T15:04:05.999123456", N1QL will round off to three digits and return "2006-01-02T15:04:05.999".

For N1QL:

SELECT STR_TO_TZ("2006-01-02T15:04:05.999123456", 'America/Los_Angeles') as west;
    "results": [
            "west": "2006-01-02T15:04:05.999"

For Oracle:

SELECT TO_TIMESTAMP('25-DEC-2008 01:00:00.336123456', 'dd-mon-yyyy hh:mi:ss.ff') as D from dual;

For N1QL, if we specify a more than nine digits, the date-time function returns null.

SELECT STR_TO_TZ("2006-01-02T15:04:05.9991234567", 'America/Los_Angeles') as west;
   "results": [
            "west": null

For Oracle, if you give more than nine digits for the fractional seconds part, it throws an error "ORA-01830: date format picture ends before converting entire input string."

You can see a comparison of the Couchbase N1QL and Oracle date-time support in the table in this article. Important notes from this table include that:

  • Both Oracle and N1QL automatically determine whether daylight saving time is in effect for a specified time zone and return the corresponding local time.

  • When dealing with the date formats in N1QL, it is important to remember that each component of the date-time string needs to be represented by a valid numeric value. So, when passing in the date format to any N1QL functions, we need to pass the date as "2001-12-12" instead of "YYYY-MM-DD". N1QL only supports the listed formats. Also, the date component of the date-time string has to be separated by a "-" and the time components need to be separated by a ":". Otherwise, it is not a valid date.

For any date/time types, both Oracle and N1QL store extra information in different fields for the input date. These allow the user to extract specific information about the date.

Oracle's date-time fields are CENTURY, YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. The TIMESTAMP data types represent seconds as fractional seconds and its precision is determined by the fractional_seconds_precision parameter. It also includes the fields TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR. It internally converts the above character values into date values. The default time for the time component is midnight and the default date for the date component is the first day of the current month. A DATE datatype stores both the date and time information.

In addition to the fields that Oracle supports for its DATE and TIME datatypes, N1QL also supports MILLENNIUM, DECADE, QUARTER, WEEK, and MILLISECOND. The value of these fields is computed internally using basic arithmetic. N1QL does not support TIMEZONE_REGION and TIMEZONE_ABBR fields.

A detailed comparison of each field is given here.

Let's consider the following sample row for our examples in Oracle:

create table t1 (date_purchased  timestamp with time zone );
insert into t1 values (TIMESTAMP '2008-12-25 01:00:00.336 PST');

And let's consider the corresponding Couchbase document:

create primary index on bucket1;
Insert into bucket1 values ("23", {"date_purchased":"2008-12-25T01:00:00.336-08:00"});

Bucket1 - Document id : 23

The TO_CHAR ('CC') (with a date as the first arg) and EXTRACT function are used in Oracle to retrieve the date-time field values in Oracle. For N1QL, there are two functions — DATE_PART_STR or DATE_PART_MILLIS — depending on whether the date is represented as a JSON string or a numeric millisecond. We will use these functions to give examples for each date time field listed below.

For N1QL, within the date time format, TIMEZONE_REGION and TIMEZONE_ABBR are not supported. (But these are passed into the time zone-specific N1QL functions, which we shall see in Part 2 of this series.)

As we can see above when it comes to representing the TIMESTAMP within N1QL dates, there are additional fields supported. These are ISO_YEAR, ISO_WEEK, DAY_OF_YEAR (DOY), DAY_OF_WEEK (DOW) and TIMEZONE which is the offset from UTC.

In the absence of a time zone indicator, the current local timezone is used (where the Couchbase server is installed).

Let's dive a little deeper into the TIMEZONE comparisons between N1QL and Oracle. The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes in Oracle are variants of the TIMESTAMP datatype. The former includes the time zone information, which is the time zone offset, which is the time relative to UTC or time zone region name in its value, and the latter includes the current session time zone. TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified. (See here for more details).


TIMESTAMP '2017-01-31 03:49:30.612 -08:00' "2017-01-31T03:49:30.612-08:00"



For Oracle, if two dates being compared represent the same value in UTC, then they are equal.


TIMESTAMP '2017-01-15 8:00:00 -8:00' == TIMESTAMP '2017-01-15 10:00:00 -6:00'

In N1QL, in order to compare full date values, we need to convert them to milliseconds.


STR_TO_MILLIS("2017-01-31T05:49:30.612-06:00") == STR_TO_MILLIS("2017-01-31T03:49:30.612-08:00")

Value: 1485863370612

For Oracle, we can replace this offset with the time zone region (TZR) and the abbreviation. The abbreviation TZD is used in the event the region value is ambiguous (when the US switches to daylight saving time).

However, in N1QL, the timezone component of the date is always represented as a UTC offset. For example:

TIMESTAMP '2017-01-15 8:00:00 -8:00' can also be TIMESTAMP '2017-01-15 8:00:00 US/Pacific PDT'

Oracle also supports interval data types INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. These store time durations. The former stores the duration using the year and month fields and the latter using the days, hours, minutes, and second fields. With N1QL, computing an interval is made easy using date time functions and the "part" component. (These functions will be explored more in Part 2.)

There are multiple ways to insert date or time data into Oracle. The user can insert a formatted string based on the NLS format value, or a literal with explicit conversion using the TO_DATE/TO_TIMESTAMP or TO_TIMESTAMP_TZ functions or implicit conversion.

For N1QL, all dates are added to a document as a string in the format specified above, or as a number representing a Unix timestamp in milliseconds. This makes handling dates very easy since the functions perform all the necessary arithmetic for the user. One drawback, though, is that the date has to exactly match one of the formats in the date formats table. This restricts the user to use only a subset of available formats.

One workaround for this limitation with N1QL is to use the string functions and message the input date to reflect the format you want.

For example:

Convert 2016-09-23T18:48:11.000+00:00 into "YYYY/MM/DD"
SELECT  to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "year" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "month" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "day" ));

"results": [
           "$1": "2016/9/23"

As we can see, N1QL simplifies manipulating date and time by representing it as a string or a number when compared to Oracle. But this means that the user is restricted to use only specified date-time formats and does not have the freedom to manipulate these formats, which Oracle does very easily with its format parameters.

Coming up in the date-time article series:

  1. Date-time and interval arithmetic
  2. Conversion between different date-time formats/data types
  3. How Oracle and N1QL handle daylight savings
  4. Time zone-related functions and how N1QL expects its time zone strings
  5. General SQL/N1QL functions to retrieve timestamps

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

database ,oracle ,n1ql ,tutorial ,date-time ,couchbase

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}