Date and Time functions and formats can vary for different databases. In this article, we shall compare Couchbase ISO-based Date-Time functions with Oracle Date format. Date formats vary between relational and NoSQL database due to the nature of NoSQL JSON formats. Learn more in this webcast about how to avoid sacrificing your SQL knowledge while also maintaining flexibility in your NoSQL database queries.

Oracle date/time types

In Oracle, the date format and the time zone features include multiple data types, including: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. The TIMESTAMP data type is an extension of the DATE type.

Date format in Oracle 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 the NLS_DATE_LANGUAGE parameters. The table, below, presents a detailed comparison with working examples.

Couchbase date formats

With Couchbase, Date-Time is done a little differently. All dates and times should be stored as strings that follow the Extended ISO 8601 date time format. N1QL contains DATETIME functions that can be used to and extract these Oracle date 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. 

For example, in Oracle timestamp format:

‘2008-DEC-25 17:30’ is a valid date given the 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 date function formats.

For example, 2008-12-25 17:30:00

Time precision

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

For example, in Oracle the date-time 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’

Couchbase N1QL supports fractional seconds similar to Oracle. This is seen when using the format: “2006-01-02T15:04:05.999”. However, N1QL supports 3 digit precision (nanosecond precision) and Oracle supports up to 9 digit fractional second precision.

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

For N1QL,

For Oracle,

Using N1QL, If we specify a more than 9 digits, the Date-Time function returns null.

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

Comparison of Oracle and Couchbase date/time approaches

A comparison of the Couchbase N1QL and Oracle’s date-time support is given in the following table:

Oracle Date and Time datatypes Couchbase Date Time format support
DATE data type format is defined by:

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

Example:

The NLS_DATE_FORMAT parameter needs to be set in the initialization parameter file:

NLS_DATE_FORMAT=’YYYY-MON-DD’

Sample date:

DATE ‘2008-DEC-25’

To set the NLS_DATE_LANGUAGE parameter we can use the ALTER SESSION statement.

 

In N1QL there is no specific date/time/timestamp data type. They are all represented by JSON strings with ISO 8601 extended formats and are manipulated using the date time manipulation and arithmetic functions.

See the documentation for complete list.

Allowed date format*:

YYYY-MM-DD

Example:

In Couchbase/N1QL dates need to be represented in specific formats with day, month and year in numeric form.

For all dates that do not match the input formats, we return the input date in the default format:

YYYY-MM-DDThh:mm:ss.sTZD

Use the DATE_FORMAT_STR function to convert the date format from YYYY-MM-DD to ‘YYYY-MON-DD’ 

Couchbase supports only ISO Extended date formats. It doesn’t support non-numeric dates in multiple languages.

TIMESTAMP data type format is defined by:

NLS_TIMESTAMP_FORMAT

You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file.

NLS_TIMESTAMP_FORMAT  = ‘YYYY-MM-DD HH:MI:SS.FF’

We can use the TO_TIMESTAMP function to convert input date-time to timestamp data type.

 

Same functions as date but with a different input date format input argument. This needs to be explicitly provided. 

The date format along with timestamp components:

YYYY-MM-DD hh:mm:ss
YYYY-MM-DDThh:mm:ss
YYYY-MM-DD hh:mm:ss.s
YYYY-MM-DDThh:mm:ss.s

Time based formats:

hh:mm:ss
Hh:mm:ss.s

Couchbase supports nanosecond precision.

Data types:

TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIME ZONE

Format defined by:

NLS_TIMESTAMP_TZ_FORMAT

You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the initialization parameter file:

NLS_TIMESTAMP_TZ_FORMAT  = ‘YYYY-MM-DD HH:MI:SS.FF TZH:TZM’

We can use the TO_TIMESTAMP_TZ function to convert input date-time to timestamp with time zone data type. It will maintain the input time zone.

 

Includes the DateTime components of format:

YYYY-MM-DDThh:mm:ssTZD
YYYY-MM-DD hh:mm:ssTZD
YYYY-MM-DDThh:mm:ss.sTZD
YYYY-MM-DD hh:mm:ss.sTZD

Timestamp based formats:

hh:mm:ss.sTZD
hh:mm:ssTZD

With N1QL, in addition to the specific formats we also have specific time zone functions, for example string to timezone STR_TO_TZ which converts the input date to the specified time zone. See documentation for time zone manipulation functions

Table 1 – Oracle DateTime types vs. N1QL ISO Time Zone and Date formats

* Both Oracle and N1QL automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns 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 need to be represented by a valid numeric value. Also the date component of the date-time string has to be separated by a dash “” and the time components need to be separated by a colon “:“. Otherwise it is not a valid date object.


Moving from relational to NoSQL: How to get started from OracleRelated Resource

The beauty of JSON is you can have many different flexible styles for storing date/time information and use simple functions to compose or extract what is needed at that time you need it. Strict schemas are not required in Couchbase NoSQL, which makes it more flexible than legacy RDBMS solutions. Learn more by reading our whitepaper.

 


Oracle and Couchbase Date/Time Components

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 with 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 Oracle timestamp to date values. The default 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 data-types, 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 for each field is given in the table below.

Let us consider the following sample row for our examples in timestamp format in Oracle:

Let us consider the corresponding Couchbase document in a bucket named bucket1:

The TO_CHAR(‘CC’ ), with a date as the first argument, and EXTRACT function is used in Oracle to retrieve the date-time field values.

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 component listed below.

Summary of all date-time components in Oracle and Couchbase

Date-Time  components Oracle N1QL
CENTURY

 

YEAR

 

MONTH

 

DAY

 

HOUR

(different behavior)

Oracle considers the time zone component of the input timestamp.

The difference in results is because N1QL does not consider the time zone component of the input timestamp.

MINUTE

 

SECOND

 

For fractional part see MILLISECOND.

TIMEZONE_HOUR

 

TIMEZONE_MINUTE

 

TIMEZONE_REGION

 

TIMEZONE_ABBR

 

TIMEZONE (offset from UTC) (Data type itself, not a field)

The number here represents the time zone in seconds.

MILLENNIUM

Millennium = (Year / 1000) + 1  

DECADE

Decade = Year / 10

QUARTER

Quarter = (Month + 2) / 3

WEEK

Week = int(math.Ceil(float64(YearDay) / 7.0))

YearDay returns the day of the year specified by the time, in the range 1 to 365 for non-leap years, and 1 to 366 in leap years.  (See golang time package)

MILLISECOND

Millisecond = Nanosecond / 106
as an integer

ISO_YEAR

Iso_year = ISO 8601 year for the input timestamp.

ISO_WEEK

Iso_week = ISO 8601 week for the input timestamp.

The week usually ranges from 1 to 53. For example, Jan 1 to Jan 3 of year n might belong to Week 52 or 53 of year n-1, and Dec 29 to 31 might belong to week 1 of year n+1.

DAY_OF_YEAR (DOY)

Day_of_year or doy = YearDay

See golang time package.

YearDay returns the day of the year specified by the time, in the range 1 to 365 for non-leap years, and 1 to 366 in leap years.

DAY_OF_WEEK (DOW)

Day_of_week or dow = Weekday function, which returns the day of the week for the given time. See golang time package.

 

Time zone considerations

For N1QL, within the date time format, TIMEZONE_REGION and TIMEZONE_ABBR are not supported. But they can be passed into the time zone specific N1QL functions.

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 time zone is take from where the Couchbase server is located.

Let us delve a little deeper into the TIMEZONE comparisons between N1QL and Oracle. The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types in Oracle are variants of the TIMESTAMP data type. 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 later 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 Oracle documentation: Datetime Datatypes and Time Zone Support for more details.

Oracle:  TIMESTAMP ‘2017-01-31 03:49:30.612 -08:00’

Couchbase: “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, 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

In 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 time zone 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 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.

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.

In 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 and flexible 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 massage the input date to reflect the format you want. For example:

Convert 2016-09-23T18:48:11.000+00:00 into “YYYY/MM/DD”

As we can see, N1QL simplifies manipulating Dates and Timestamps by representing it as a string or a number when compared to Oracle date to timestamp format. 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 using its Format Parameters.

Keep learning

Ready to try querying JSON documents with Couchbase SQL++ language?

Author

Posted by Couchbase Product Marketing

Leave a reply