In Oracle, dates have the format
|
Read the pdf version here. Show Date and Time functions and formats can vary for different databases. In the article we shall compare Couchbase N1QL Date-Time functions with Oracle ISO Date format. In Oracle, date format and timezones feature 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. (See table 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 Extended ISO 8601 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. (See table below for detailed comparison with examples) 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 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 datetime field. It can be a number (0 to 9) with the default being 6. For example, in Oracle the 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’ 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 3 digit precision (nanosecond precision) and oracle supports upto 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,
For N1QL, If we specify a more than 9 digits, the Date-Time function returns null.
For Oracle datetime 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 A comparison of the Couchbase N1QL and Oracle’s date time support is given in the following following table:
Table – Oracle DateTime types VS N1QL ISO Timezone 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. 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 “-” and the time components need to be separated by “:”. 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 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 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 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 Create bucket1 in Couchbase.
The TO_CHAR (‘CC’ ) (with a date as the first arg) and EXTRACT function is used in Oracle to retrieve the date-time field values. For N1QL there are 2 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 timezone 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 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 timezone. 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). 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. Oracle: TIMESTAMP ‘2017-01-15 8:00:00 -8:00’ == TIMESTAMP ‘2017-01-15 10:00:00 -6:00’ In N1QL currently in order to compare full date values we need to convert them to milliseconds. N1QL: 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 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 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. Coming up in the Date Time article series –
What is the date format in Oracle?Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY.
How do I display a date in YYYY MM DD format in Oracle?Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.
How do I change the date format in Oracle?Setting the Date Format. Select Preferences in the left frame, or select File, and then Preferences.. Click the Planning icon, and then select Display Options.. For Date Format, select MM-DD-YYYY, DD-MM-YYYY, YYYY-MM-DD, or Automatically Detect (to use your system's locale settings).. Click OK.. How is date stored in Oracle?For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second. You can specify a date value by: Specifying the date value as a literal. Converting a character or numeric value to a date value with the TO_DATE function.
|
