{"id":1168,"date":"2017-09-22T21:36:20","date_gmt":"2017-09-23T04:36:20","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/dates-timestamps-timezones-comparative-study-oracle-n1ql-support-date-time-feature-part-1\/"},"modified":"2017-09-22T21:36:20","modified_gmt":"2017-09-23T04:36:20","slug":"dates-timestamps-timezones-comparative-study-oracle-n1ql-support-date-time-feature-part-1","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/dates-timestamps-timezones-comparative-study-oracle-n1ql-support-date-time-feature-part-1\/","title":{"rendered":"Oracle Date Format: N1QL and Support for Date-Time Functions"},"content":{"rendered":"\n

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<\/a> about how to avoid sacrificing your SQL knowledge while also maintaining flexibility in your NoSQL database queries<\/a>.<\/span><\/p>\n\n\n\n

Oracle date\/time types<\/h2>\n\n\n\n

In Oracle, the date<\/em> format and the time zone<\/em> features include multiple data types, including: DATE<\/em>, TIMESTAMP<\/em>, TIMESTAMP WITH TIME ZONE<\/em> and TIMESTAMP WITH LOCAL TIME ZONE<\/em>. The TIMESTAMP<\/em> data type is an extension of the DATE<\/em> type. <\/span><\/p>\n\n\n\n

Date format in Oracle can be represented either as literals<\/strong> or as numeric<\/strong> values. The literal needs to be in a specified format. The format for the date-times can be set using the NLS_DATE_FORMAT<\/em>, NLS_TIMESTAMP_FORMAT<\/em>, NLS_TIMESTAMP_TZ_FORMAT<\/em> and the NLS_DATE_LANGUAGE<\/em> parameters. The table, below, presents a detailed comparison with working examples.<\/span><\/p>\n\n\n\n

Couchbase date formats<\/h2>\n\n\n\n

With Couchbase, Date-Time is done a little differently. All dates and times should be stored as strings<\/strong> that follow the Extended ISO 8601 date time format<\/strong>. N1QL contains DATETIME<\/em> functions that can be used to and extract these Oracle date formatted strings. The DATE<\/em> and TIME<\/em> for a specific timezone can also be represented as a Unix timestamp in milliseconds. <\/span><\/p>\n\n\n\n

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<\/em> and NLS_TIMESTAMP_FORMAT<\/em>, the format for dates in Couchbase follows a strict set.\u00a0<\/span><\/p>\n\n\n\n

For example, in Oracle timestamp format:<\/span><\/p>\n\n\n\n

‘2008-DEC-25 17:30’<\/em> is a valid date given the NLS_DATE_FORMAT=’YYYY-MON-DD HH24:MI’<\/em><\/p>\n\n\n\n

For example, 2008-12-25 17:30:00<\/span><\/em><\/p>\n\n\n\n

Time precision<\/h3>\n\n\n\n

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

For example, in Oracle the date-time format can be given as:<\/span><\/p>\n\n\n\n

TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FFF’<\/span> <\/em><\/p>\n\n\n\n

Using this we can define the timestamp to be: <\/span>‘2006-01-02 15:04:05.999’<\/span><\/em><\/p>\n\n\n\n

Couchbase N1QL<\/a> supports fractional seconds similar to Oracle. This is seen when using the format:<\/span>\u00a0“2006-01-02T15:04:05.999”<\/em>. <\/span>However,<\/span> N1QL supports 3 digit precision (nanosecond precision) and Oracle supports up to 9 digit fractional second precision. <\/span><\/p>\n\n\n\n

This means that if we specify the date: <\/span>“2006-01-02T15:04:05.999123456”<\/em>, N1QL will round off to 3 digits and return: “2006-01-02T15:04:05.999”<\/em>.<\/span><\/p>\n\n\n\n

For N1QL, <\/span><\/p>\n\n\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.999123456”, ‘America\/Los_Angeles’) as west;
\n \u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: “2006-01-02T15:04:05.999”
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n\n\n\n

For Oracle,<\/span><\/p>\n\n\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n\n\n\n

Using N1QL, If we specify a more than 9 digits, the Date-Time function returns null. <\/span><\/p>\n\n\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n\n\n\n

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

Comparison of Oracle and Couchbase date\/time approaches<\/h2>\n\n\n\n

A comparison of the Couchbase N1QL and Oracle’s date-time support is given in the following table:<\/span><\/p>\n\n\n\n

\n\n\n\n\n\n
Oracle Date and Time datatypes<\/strong><\/td>\nCouchbase Date Time format support<\/strong><\/td>\n<\/tr>\n
DATE <\/span><\/em>data type f<\/span>ormat is defined by:<\/span>\n

NLS_DATE_FORMAT<\/span><\/em><\/p>\n

NLS_DATE_LANGUAGE<\/span><\/em><\/p>\n

Example: <\/span><\/p>\n

The NLS_DATE_FORMAT<\/em> parameter needs to be set <\/span>in the initialization parameter file:<\/span><\/p>\n

NLS_DATE_FORMAT=’YYYY-MON-DD’<\/span><\/em><\/p>\n

Sample date:<\/span><\/p>\n

DATE ‘2008-DEC-25’<\/span><\/em><\/p>\n

To set the NLS_DATE_LANGUAGE<\/em> parameter we <\/span>can use the ALTER SESSION<\/em> statement.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n<\/p>\n

\u00a0<\/p><\/td>\n

In N1QL there is no specific date\/time\/<\/span>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. <\/span>\n

See the <\/span>documentation<\/span><\/a> for complete list.<\/span><\/p>\n

Allowed date format*:<\/span><\/p>\n

YYYY-MM-DD<\/span><\/em><\/p>\n

Example:<\/span><\/p>\n

[crayon lang=”default” decode=”true”]{
\n\u00a0“Date” : “2008-12-25”
\n}[\/crayon]<\/p>\n<\/p>\n

In Couchbase\/N1QL dates need to be represented in specific formats with day, month and year in numeric form. <\/span><\/p>\n

For all dates that do not match the input formats, we return the input date in the default format:<\/p>\n

YYYY-MM-DDThh:mm:ss.sTZD<\/em><\/p>\n

Use the DATE_FORMAT_STR<\/em> function to convert the date format from YYYY-MM-DD<\/em> to <\/span>‘YYYY-MON-DD’<\/em>\u00a0<\/span><\/p>\n

[crayon lang=”default” decode=”true”]select date_format_str(‘2008-12-25’,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘1111-DEC-12’) D;<\/p>\n

“results”: [
\n\u00a0\u00a0\u00a0\u00a0{
\n\u00a0\u00a0\u00a0\u00a0\u00a0“D”: “2008-12-25T00:00:00-08:00”
\n\u00a0\u00a0\u00a0\u00a0}
\n]
\n[\/crayon]<\/p>\n<\/p>\n

Couchbase supports only ISO Extended date formats<\/strong>. It doesn’t support non-numeric dates in multiple languages.<\/span><\/p><\/td>\n<\/tr>\n

TIMESTAMP<\/em> data type f<\/span>ormat is defined by:<\/span>\n

NLS_TIMESTAMP_FORMAT<\/span><\/em><\/p>\n

You can specify the value of <\/span>NLS_TIMESTAMP_FORMAT<\/span><\/em> by setting it in the initialization parameter file. <\/span><\/p>\n

NLS_TIMESTAMP_FORMAT \u00a0= ‘YYYY-MM-DD HH:MI:SS.FF’<\/span><\/em><\/p>\n

We can use the TO_TIMESTAMP<\/em> function to convert input date-time<\/em> to timestamp<\/em> data type.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]SELECT
\nTO_TIMESTAMP(’25-DEC-2008 01:00:00.336′,
\n‘dd-mon-yyyy hh:mi:ss.ff’) from dual;<\/p>\n

TO_TIMESTAMP(’25-DEC-200801:00:00.336′,
\n‘DD-MON-YYYYHH:MI:SS.FF’)
\n———————————
\n2008-12-25 01:00:00.336[\/crayon]<\/p>\n<\/p>\n

\u00a0<\/p><\/td>\n

Same functions as date but with a different input date format input argument. This needs to be explicitly provided.\u00a0<\/span>\n

The date format along with timestamp components:<\/span><\/p>\n

YYYY-MM-DD hh:mm:ss
\n<\/span><\/em>YYYY-MM-DDThh:mm:ss
\n<\/span><\/em>YYYY-MM-DD hh:mm:ss.s
\n<\/span><\/em>YYYY-MM-DDThh:mm:ss.s<\/em><\/p>\n

Time based formats:<\/span><\/p>\n

hh:mm:ss
\n<\/span>Hh:mm:ss.s<\/span><\/em><\/p>\n

Couchbase supports nanosecond precision.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]select
\ndate_format_str(‘2008-12-25T01:00:00.336’,
\n‘1111-DEC-12 01:00:00.00’) D;<\/p>\n

“results”: [
\n\u00a0\u00a0\u00a0\u00a0{
\n\u00a0\u00a0\u00a0\u00a0\u00a0“D”: “2008-12-25T01:00:00.336-08:00”
\n\u00a0\u00a0\u00a0\u00a0}
\n\u00a0][\/crayon]<\/p>\n<\/p>\n<\/td>\n<\/tr>\n

Data types:<\/span>\n

TIMESTAMP WITH TIMEZONE
\n<\/span><\/em>TIMESTAMP WITH LOCAL TIME ZONE<\/span><\/em><\/p>\n

Format defined by:<\/span><\/p>\n

NLS_TIMESTAMP_TZ_FORMAT<\/span><\/em><\/p>\n

You can specify the value of <\/span>NLS_TIMESTAMP_TZ_FORMAT<\/span><\/em> by setting it in the initialization parameter file:<\/span><\/p>\n

NLS_TIMESTAMP_TZ_FORMAT \u00a0= ‘YYYY-MM-DD HH:MI:SS.FF TZH:TZM’<\/span><\/em><\/p>\n

We can use the TO_TIMESTAMP_TZ<\/em> function to convert input date-time to timestamp with time zone data type. It will maintain the input time zone.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]SELECT TO_CHAR(TO_TIMESTAMP_TZ
\n(‘2008-12-25, 01:00:00.336 -08:00’,
\n‘yyyy-mm-dd hh:mi:ss.ff TZR’)) FROM DUAL;<\/p>\n

TO_CHAR(TO_TIMESTAMP_TZ
\n(‘2008-12-25,01:00:00.336-08:00’,
\n‘YYYY-MM-DDHH:MI:SS.FFTZR’))
\n————————————–
\n25-DEC-08 01.00.00.336000000 AM -08:00[\/crayon]<\/p>\n<\/p>\n

\u00a0<\/p><\/td>\n

Includes the DateTime components of format:<\/span>\n

YYYY-MM-DDThh:mm:ssTZD
\n<\/span>YYYY-MM-DD hh:mm:ssTZD
\n<\/span>YYYY-MM-DDThh:mm:ss.sTZD
\n<\/span>YYYY-MM-DD hh:mm:ss.sTZD<\/span><\/em><\/p>\n

Timestamp based formats:<\/span><\/p>\n

hh:mm:ss.sTZD
\n<\/span><\/em>hh:mm:ssTZD<\/span><\/em><\/p>\n

With N1QL, in addition to the specific formats we also have specific time zone functions, for example string to timezone<\/em> STR_TO_TZ<\/em> which converts the input date to the specified time zone. See documentation for time zone manipulation functions<\/a><\/span><\/p>\n

[crayon lang=”default” decode=”true”]SELECT
\nSTR_TO_TZ(‘2008-12-25T01:00:00.336-08:00’,
\n‘America\/Los_Angeles’) as west;<\/p>\n

“results”: [
\n\u00a0\u00a0\u00a0\u00a0{
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: “2008-12-25T01:00:00.336-08:00”
\n\u00a0\u00a0\u00a0\u00a0}
\n\u00a0\u00a0\u00a0]
\n[\/crayon]<\/p>\n<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n\n

Table 1 – Oracle DateTime types vs. N1QL ISO Time Zone and Date formats<\/em><\/p>\n\n\n\n

* <\/span>Both Oracle and N1QL automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time. \u00a0<\/span><\/p>\n\n\n\n

** 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 <\/strong>value. Also the date component of the date-time string has to be separated by a dash “–<\/em>” and the time components need to be separated by a colon “:<\/em>“. Otherwise it is not a valid date object.<\/span><\/p>\n\n\n\n

\n\n\n\n

\"Moving<\/a>Related Resource<\/strong><\/p>\n\n\n\n

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<\/a>.<\/p>\n\n\n\n

\u00a0<\/p>\n\n\n\n

\n\n\n\n

Oracle and Couchbase Date\/Time Components<\/h2>\n\n\n\n

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. <\/span><\/p>\n\n\n\n

Oracle\u2019s date-time fields are CENTURY<\/em>, YEAR<\/em>, MONTH<\/em>, DAY<\/em>, HOUR,<\/em> MINUTE<\/em> and SECOND<\/em>. The TIMESTAMP<\/em> data types represent seconds as fractional seconds with its precision is determined by the fractional_seconds_precision<\/em> parameter. It also includes the fields TIMEZONE_HOUR<\/em>, TIMEZONE_MINUTE<\/em>, TIMEZONE_REGION<\/em> and TIMEZONE_ABBR<\/em>. 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<\/em> datatype stores both the date and time information. <\/span><\/p>\n\n\n\n

In addition to the fields that Oracle supports for its DATE<\/em> and TIME<\/em> data-types, N1QL also supports MILLENNIUM<\/em>, DECADE<\/em>, QUARTER<\/em>, WEEK<\/em> and MILLISECOND<\/em>. The value of these fields is computed internally using basic arithmetic. N1QL does not support TIMEZONE_REGION<\/em> and TIMEZONE_ABBR<\/em> fields. <\/span><\/p>\n\n\n\n

A detailed comparison for each field is given in the table below.<\/span><\/p>\n\n\n\n

Let us consider the following sample row for our examples in timestamp format in Oracle:<\/span><\/p>\n\n\n\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n0<\/p>\n\n\n\n

Let us consider the corresponding Couchbase document in a bucket named\u00a0bucket1<\/em>:<\/span><\/p>\n\n\n\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n1<\/p>\n\n\n\n

The TO_CHAR(\u2018CC\u2019 ), <\/em>with a date as the first argument, and EXTRACT<\/em> function is used in Oracle to retrieve the date-time field values. <\/span><\/p>\n\n\n\n

For N1QL there are two functions DATE_PART_STR<\/em> or DATE_PART_MILLIS<\/em> 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.<\/span><\/p>\n\n\n\n

Summary of all date-time components in Oracle and Couchbase<\/h3>\n\n\n\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Date-Time\u00a0 components<\/strong><\/td>\nOracle <\/strong><\/td>\nN1QL<\/strong><\/td>\n<\/tr>\n
CENTURY<\/span><\/td>\n\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n2<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n3<\/p>\n<\/td>\n<\/tr>\n

YEAR<\/span><\/td>\n\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n4<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n5<\/p>\n<\/td>\n<\/tr>\n

MONTH<\/span><\/td>\n\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n6<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n7<\/p>\n<\/td>\n<\/tr>\n

DAY<\/span><\/td>\n\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n8<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon nums=”false” lang=”default” decode=”true”]SELECT TO_TIMESTAMP(’25-DEC-2008 01:00:00.336123456′, ‘dd-mon-yyyy hh:mi:ss.ff’) as D from dual;<\/p>\n

> D
\n> 2008-12-25 01:00:00.336123456[\/crayon]<\/p>\n9<\/p>\n<\/td>\n<\/tr>\n

HOUR<\/span>\n

(different behavior)<\/b><\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n0<\/p>\n

Oracle considers the time zone component of the input timestamp.<\/span><\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n1<\/p>\n

The <\/span>difference<\/b> in results is because N1QL does not consider the time zone component of the input timestamp.<\/span><\/p><\/td>\n<\/tr>\n

MINUTE<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n2<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n3<\/p>\n<\/td>\n<\/tr>\n

SECOND<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n4<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n5<\/p>\n

For fractional part see MILLISECOND.<\/span><\/p><\/td>\n<\/tr>\n

TIMEZONE_HOUR<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n6<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n7<\/p>\n<\/td>\n<\/tr>\n

TIMEZONE_MINUTE<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n8<\/p>\n

\u00a0<\/p><\/td>\n

\u2705<\/span>\n

[crayon lang=”default” decode=”true”]SELECT STR_TO_TZ(“2006-01-02T15:04:05.9991234567”, ‘America\/Los_Angeles’) as west;
\n\u00a0\u00a0\u00a0“results”: [
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“west”: null
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n \u00a0\u00a0\u00a0][\/crayon]<\/p>\n9<\/p>\n<\/td>\n<\/tr>\n

TIMEZONE_REGION<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n0<\/p>\n

\u00a0<\/p><\/td>\n

\u274c<\/span><\/td>\n<\/tr>\n
TIMEZONE_ABBR<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n1<\/p>\n

\u00a0<\/p><\/td>\n

\u274c<\/span><\/td>\n<\/tr>\n
TIMEZONE (offset from UTC)<\/span><\/td>\n(Data type itself, not a field)<\/span><\/td>\n\u2705<\/span>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n2<\/p>\n

The number here represents the time zone in seconds.<\/span><\/p><\/td>\n<\/tr>\n

MILLENNIUM <\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Millennium<\/b> = (Year \/ 1000) + 1 \u00a0<\/span><\/em><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n3<\/p>\n<\/td>\n<\/tr>\n

DECADE<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Decade<\/b> = Year \/ 10<\/span><\/em><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n4<\/p>\n<\/td>\n<\/tr>\n

QUARTER<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Quarter<\/b> = (Month + 2) \/ 3<\/span><\/em><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n5<\/p>\n<\/td>\n<\/tr>\n

WEEK <\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Week<\/b> = int(math.Ceil(float64(YearDay) \/ 7.0))<\/em><\/span><\/p>\n

YearDay<\/em> 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. \u00a0(See <\/span>golang time package<\/span><\/a>)<\/span><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n6<\/p>\n<\/td>\n<\/tr>\n

MILLISECOND<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Millisecond<\/b> = Nanosecond \/ 10<\/span>6<\/span><\/em>
\nas an integer<\/span><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n7<\/p>\n<\/td>\n<\/tr>\n

ISO_YEAR<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Iso_year = ISO 8601 year for the input timestamp.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n8<\/p>\n<\/td>\n<\/tr>\n

ISO_WEEK<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Iso_week = ISO 8601 week for the input timestamp.<\/span><\/p>\n

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.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;<\/p>\n

SELECT TO_CHAR(SYSDATE, ‘Day:Dd Month yyyy’)
\nFROM DUAL;<\/p>\n

TO_CHAR(SYSDATE,’DAY:DDMONTHYYYY’)
\n————————————–
\nLundi :21 Ao\u00fbt \u00a0\u00a0\u00a0\u00a0\u00a02017[\/crayon]<\/p>\n9<\/p>\n<\/td>\n<\/tr>\n

DAY_OF_YEAR (DOY)<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Day_of_year or doy = YearDay<\/em><\/span><\/p>\n

See <\/span>golang time package.<\/span><\/a><\/p>\n

YearDay<\/em> 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.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]{
\n\u00a0“Date” : “2008-12-25”
\n}[\/crayon]<\/p>\n0<\/p>\n<\/td>\n<\/tr>\n

DAY_OF_WEEK (DOW)<\/span><\/td>\n\u274c<\/span><\/td>\n\u2705<\/span>\n

Day_of_week or dow = Weekday<\/em> function, which returns the day of the week for the given time. See <\/span>golang time package<\/span><\/a>.<\/span><\/p>\n

[crayon lang=”default” decode=”true”]{
\n\u00a0“Date” : “2008-12-25”
\n}[\/crayon]<\/p>\n1<\/p>\n

\u00a0<\/p><\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n\n

Time zone considerations<\/h2>\n\n\n\n

For N1QL, within the date time format, TIMEZONE_REGION<\/em> and TIMEZONE_ABBR<\/em> are not supported. But they can be passed into the time zone specific N1QL functions.<\/a><\/span><\/p>\n\n\n\n

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

In the absence of a time zone indicator, the current local time zone is take from where the Couchbase server is located. <\/span><\/p>\n\n\n\n

Let us delve a little deeper into the TIMEZONE<\/em> comparisons between N1QL and Oracle. The TIMESTAMP<\/em> WITH TIME ZONE<\/em> and TIMESTAMP WITH LOCAL TIME ZONE<\/em> data types in Oracle are variants of the TIMESTAMP<\/em> 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<\/em> does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM<\/em> or TZR TZD<\/em> format elements are specified. See Oracle documentation: Datetime Datatypes and Time Zone Support <\/a><\/span>for more details.<\/span><\/p>\n\n\n\n

Oracle:\u00a0\u00a0<\/span>TIMESTAMP ‘2017-01-31 03:49:30.612 -08:00’<\/span><\/em><\/p>\n\n\n\n

Couchbase:\u00a0<\/span>“2017-01-31T03:49:30.612-08:00”<\/span><\/em><\/p>\n\n\n\n

For Oracle, if two dates being compared represent the same value in UTC, then they are equal:<\/span><\/p>\n\n\n\n

TIMESTAMP ‘2017-01-15 8:00:00 -8:00’ == TIMESTAMP ‘2017-01-15 10:00:00 -6:00’<\/span><\/em><\/p>\n\n\n\n

In N1QL, to compare full date values we need to convert them to milliseconds.<\/span><\/p>\n\n\n\n

STR_TO_MILLIS(“2017-01-31T05:49:30.612-06:00”) == STR_TO_MILLIS(“2017-01-31T03:49:30.612-08:00”) \u00a0<\/span><\/em><\/p>\n\n\n\n

Value: 1485863370612<\/span><\/em><\/p>\n\n\n\n

In Oracle, we can replace this offset with the Time Zone Region<\/em> (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). <\/span><\/p>\n\n\n\n

However in N1QL the time zone component of the date is always represented as a UTC offset. <\/span><\/p>\n\n\n\n

For example:<\/span><\/p>\n\n\n\n

TIMESTAMP ‘2017-01-15 8:00:00 -8:00’ <\/span><\/em><\/p>\n\n\n\n

can also be: <\/span><\/em><\/p>\n\n\n\n

TIMESTAMP ‘2017-01-15 8:00:00 US\/Pacific PDT’<\/span><\/em><\/p>\n\n\n\n

Oracle also supports interval data types INTERVAL YEAR TO MONTH<\/em> and INTERVAL DAY TO SECOND<\/em>. 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 \u201cpart\u201d component.<\/span><\/p>\n\n\n\n

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<\/em> \/ TO_TIMESTAMP<\/em> or TO_TIMESTAMP_TZ<\/em> functions or implicit conversion.<\/span><\/p>\n\n\n\n

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. <\/span><\/p>\n\n\n\n

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:<\/span><\/p>\n\n\n\n

Convert 2016-09-23T18:48:11.000+00:00 into “YYYY\/MM\/DD”<\/span><\/p>\n\n\n\n

[crayon lang=”default” decode=”true”]{
\n\u00a0“Date” : “2008-12-25”
\n}[\/crayon]<\/p>\n2<\/p>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

Keep learning<\/span><\/p>\n\n\n\n

Ready to try querying JSON documents with Couchbase SQL++ language?<\/p>\n\n\n\n

However, to represent the same value in Couchbase the user needs to use one of the given date function formats<\/a>.<\/span><\/p>\n\n\n\n