Las funciones y formatos de Fecha y Hora pueden variar para diferentes bases de datos. En este artículo, compararemos las funciones Fecha-Hora basadas en ISO de Couchbase con el formato Fecha de Oracle. Los formatos de fecha varían entre bases de datos relacionales y NoSQL debido a la naturaleza de los formatos NoSQL JSON. Para saber más en este webcast sobre cómo evitar sacrificar sus conocimientos de SQL y al mismo tiempo mantener la flexibilidad en sus consultas a bases de datos NoSQL.
Tipos de fecha/hora de Oracle
En Oracle, el fecha y el formato zona horaria incluye múltiples tipos de datos, entre ellos FECHA, TIMESTAMP, MARCA HORARIA CON ZONA HORARIA y MARCA HORARIA CON ZONA HORARIA LOCAL. En TIMESTAMP es una extensión del tipo de datos FECHA tipo.
El formato de fecha en Oracle puede representarse como literales o como numérico valores. El literal debe tener un formato determinado. El formato de las fechas y horas puede establecerse mediante la función NLS_FORMATO_FECHA, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT y el NLS_FECHA_IDIOMA parámetros. La tabla siguiente presenta una comparación detallada con ejemplos prácticos.
Formatos de fecha de Couchbase
Con Couchbase, Fecha-Hora se hace un poco diferente. Todas las fechas y horas deben almacenarse como cadenas que siguen al Formato de fecha y hora ISO 8601 ampliado. N1QL contiene FECHA que pueden utilizarse para extraer estas cadenas con formato de fecha de Oracle. La dirección FECHA y TIEMPO para una zona horaria específica también puede representarse como una marca de tiempo Unix en milisegundos.
Esto significa esencialmente que, a diferencia de Oracle, donde el formato de la fecha y la hora de entrada puede cambiar en función de los valores de los campos NLS_FORMATO_FECHA y NLS_TIMESTAMP_FORMATel formato de las fechas en Couchbase sigue un conjunto estricto.
Por ejemplo, en formato timestamp de Oracle:
2008-DEC-25 17:30 es una fecha válida dada la NLS_DATE_FORMAT='AAAA-MMA-DD HH24:MI'
Sin embargo, para representar el mismo valor en Couchbase, el usuario debe utilizar uno de los métodos siguientes formatos de la función fecha.
Por ejemplo, 2008-12-25 17:30:00
Precisión horaria
En Oracle, precisión_segundos_fraccionarios es opcional y especifica el número de dígitos en la parte fraccionaria del SEGUNDO campo datetime. Puede ser un número (0-9), siendo 6 el valor por defecto.
Por ejemplo, en Oracle el formato fecha-hora puede darse como:
TIMESTAMP 'AAAA-MM-DD HH24:MI:SS.FFF'
Usando esto podemos definir el timestamp a ser: ‘2006-01-02 15:04:05.999’
Couchbase N1QL soporta segundos fraccionarios de forma similar a Oracle. Esto se ve cuando se utiliza el formato: “2006-01-02T15:04:05.999”. Sin embargo, N1QL admite una precisión de 3 dígitos (precisión de nanosegundos) y Oracle admite una precisión de segundos fraccionarios de hasta 9 dígitos.
Esto significa que si especificamos la fecha “2006-01-02T15:04:05.999123456”N1QL redondeará a 3 dígitos y devolverá: “2006-01-02T15:04:05.999”.
Para N1QL,
|
1 2 3 4 5 6 |
SELECCIONE STR_TO_TZ("2006-01-02T15:04:05.999123456", América/Los Ángeles) como oeste; "resultados": [ { "oeste": "2006-01-02T15:04:05.999" } ] |
Para Oracle,
|
1 2 3 4 |
SELECCIONE TO_TIMESTAMP('25-DEC-2008 01:00:00.336123456', dd-mon-aaaa hh:mi:ss.ff') como D de doble; > D > 2008-12-25 01:00:00.336123456 |
Usando N1QL, si especificamos más de 9 dígitos, la función Fecha-Hora devuelve null.
|
1 2 3 4 5 6 |
SELECCIONE STR_TO_TZ("2006-01-02T15:04:05.9991234567", América/Los Ángeles) como oeste; "resultados": [ { "oeste": null } ] |
Para el formato Oracle Date-Time, si da más de 9 dígitos para la parte de los segundos fraccionarios arroja un error: ORA-01830: la imagen de formato de fecha termina antes de convertir toda la cadena de entrada
Comparación de los enfoques de fecha/hora de Oracle y Couchbase
En la siguiente tabla se ofrece una comparación del soporte de fecha-hora de Couchbase N1QL y Oracle:
| Tipos de datos de fecha y hora de Oracle | Compatibilidad con el formato de fecha y hora de Couchbase | ||||||
| FECHA tipo de datos format se define por:
NLS_FORMATO_FECHA NLS_FECHA_IDIOMA Por ejemplo: En NLS_FORMATO_FECHA es necesario establecer el parámetro en el archivo de parámetros de inicialización: NLS_DATE_FORMAT='AAAA-M-M-DD' Fecha de la muestra: FECHA '2008-DEC-25' Para ajustar el NLS_FECHA_IDIOMA parámetro que puede utilizar el ALTERAR SESIÓN declaración.
|
En N1QL no hay una fecha/hora específica/tipo de datos timestamp. Todos ellos están representados por cadenas JSON con formatos extendidos ISO 8601 y se manipulan utilizando las funciones de manipulación de fecha-hora y aritmética.
Véase el documentación para ver la lista completa. Formato de fecha permitido*: AAAA-MM-DD Por ejemplo:
En Couchbase/N1QL las fechas necesitan ser representadas en formatos específicos con día, mes y año en forma numérica. Para todas las fechas que no coinciden con los formatos de entrada, devolvemos la fecha de entrada en el formato por defecto: AAAA-MM-DDThh:mm:ss.sTZD Utiliza el FORMATO_FECHA_STR para convertir el formato de fecha de AAAA-MM-DD a AAAA-LUN-DD
Couchbase sólo admite ISO Formatos de fecha ampliados. No admite fechas no numéricas en varios idiomas. |
||||||
| TIMESTAMP tipo de datos format se define por:
NLS_TIMESTAMP_FORMAT Puede especificar el valor de NLS_TIMESTAMP_FORMAT configurándolo en el archivo de parámetros de inicialización. NLS_TIMESTAMP_FORMAT = 'AAAA-MM-DD HH:MI:SS.FF' Podemos utilizar el TO_TIMESTAMP para convertir la entrada fecha-hora a marca de tiempo tipo de datos.
|
Las mismas funciones que date pero con un argumento de entrada de formato de fecha diferente. Es necesario indicarlo explícitamente.
El formato de fecha junto con los componentes de fecha y hora: AAAA-MM-DD hh:mm:ss Formatos basados en el tiempo: hh:mm:ss Couchbase soporta precisión de nanosegundos.
|
||||||
| Tipos de datos:
MARCA HORARIA CON ZONA HORARIA Formato definido por: NLS_TIMESTAMP_TZ_FORMAT Puede especificar el valor de NLS_TIMESTAMP_TZ_FORMAT configurándolo en el archivo de parámetros de inicialización: NLS_TIMESTAMP_TZ_FORMAT = 'AAAA-MM-DD HH:MI:SS.FF TZH:TZM' Podemos utilizar el TO_TIMESTAMP_TZ para convertir la fecha-hora de entrada en fecha-hora con tipo de datos de zona horaria. Mantendrá la zona horaria de entrada.
|
Incluye los componentes DateTime de format:
AAAA-MM-DDThh:mm:ssTZD Formatos basados en marcas de tiempo: hh:mm:ss.sTZD Con N1QL, además de los formatos específicos también tenemos funciones específicas de zona horaria, por ejemplo cadena a zona horaria STR_TO_TZ que convierte la fecha de entrada a la zona horaria especificada. Consulte la documentación sobre las funciones de manipulación de zonas horarias
|
Tabla 1 - Tipos de DateTime de Oracle frente a los formatos de Zona Horaria y Fecha ISO de N1QL
* Tanto Oracle como N1QL determinan automáticamente si el horario de verano está en vigor para una zona horaria especificada y devuelven la hora local correspondiente.
** Al tratar con los formatos de fecha en N1QL, es importante recordar que cada componente de la cadena de fecha y hora debe estar representado por un código válido. numérico valor. Además, el componente de fecha de la cadena de fecha-hora debe separarse con un guión "–"y los componentes de tiempo deben ir separados por dos puntos ":". De lo contrario, no es un objeto de fecha válido.
Recursos relacionados
La belleza de JSON es que puedes tener muchos estilos flexibles diferentes para almacenar información de fecha/hora y usar funciones simples para componer o extraer lo que se necesita en ese momento que lo necesitas. Los esquemas estrictos no son necesarios en Couchbase NoSQL, lo que lo hace más flexible que las soluciones RDBMS heredadas. Más información en nuestro libro blanco.
Componentes de fecha/hora de Oracle y Couchbase
Para cualquier tipo de fecha/hora tanto Oracle como N1QL almacenan información extra en diferentes campos para la fecha de entrada. Estos permiten al usuario extraer información específica sobre la fecha.
Los campos fecha-hora de Oracle son CENTURY, AÑO, MES, DÍA, HORA, MINUTO y SEGUNDO. En TIMESTAMP representan los segundos como segundos fraccionarios cuya precisión viene determinada por el precisión_segundos_fraccionarios parámetro. También incluye los campos TIMEZONE_HOUR, HUSO_HORARIO_MINUTO, ZONA_HORARIA_REGIÓN y TIMEZONE_ABBR. Convierte internamente la marca de tiempo de Oracle en valores de fecha. El valor predeterminado para el componente de hora es medianoche y el valor predeterminado para el componente de fecha es el primer día del mes en curso. A FECHA almacena tanto la fecha como la hora.
Además de los campos que Oracle admite para su FECHA y TIEMPO tipos de datos, N1QL también admite MILENIO, DÉCADA, TRIMESTRE, SEMANA y MILLISEGUNDO. El valor de estos campos se calcula internamente utilizando aritmética básica. N1QL no admite ZONA_HORARIA_REGIÓN y TIMEZONE_ABBR campos.
En la tabla siguiente se ofrece una comparación detallada de cada campo.
Consideremos la siguiente fila de ejemplo para nuestros ejemplos en formato timestamp en Oracle:
|
1 2 3 4 5 |
crear tabla t1 (fecha_compra marca de tiempo con tiempo zona ); insertar en t1 valores (TIMESTAMP '2008-12-25 01:00:00.336 PST'); > t1 fecha_compra > 2008-12-25,01:00:00.336-08:00 |
Consideremos el documento Couchbase correspondiente en un bucket denominado cubo1:
|
1 2 3 4 5 6 7 |
crear principal índice en cubo1; Inserte en cubo1 valores ("23", {"fecha_compra":"2008-12-25T01:00:00.336-08:00"}); Cubo1 - Documento id : 23 { "fecha_compra":"2008-12-25T01:00:00.336-08:00" } |
En TO_CHAR('CC' ), con una fecha como primer argumento, y EXTRACTO se utiliza en Oracle para recuperar los valores de los campos fecha-hora.
Para N1QL existen dos funciones DATE_PART_STR o FECHA_PARTE_MILIS dependiendo de si la fecha se representa como una cadena JSON o como un milisegundo numérico. Utilizaremos estas funciones para dar ejemplos para cada componente fecha-hora que se enumera a continuación.
Resumen de todos los componentes de fecha-hora en Oracle y Couchbase
| Componentes fecha-hora | Oracle | N1QL | ||||
| CENTURY | ✅
|
✅
|
||||
| AÑO | ✅
|
✅
|
||||
| MES | ✅
|
✅
|
||||
| DÍA | ✅
|
✅
|
||||
| HORA
(comportamiento diferente) |
✅
Oracle considera el componente de zona horaria de la marca de tiempo de entrada. |
✅
En diferencia en los resultados se debe a que N1QL no tiene en cuenta el componente de zona horaria de la marca horaria de entrada. |
||||
| MINUTO | ✅
|
✅
|
||||
| SEGUNDO | ✅
|
✅
Para la parte fraccionaria, véase MILLISEGUNDO. |
||||
| TIMEZONE_HOUR | ✅
|
✅
|
||||
| HUSO_HORARIO_MINUTO | ✅
|
✅
|
||||
| ZONA_HORARIA_REGIÓN | ✅
|
❌ | ||||
| TIMEZONE_ABBR | ✅
|
❌ | ||||
| HORA (desfase respecto a UTC) | (Tipo de datos en sí, no un campo) | ✅
El número aquí representa la zona horaria en segundos. |
||||
| MILENIO | ❌ | ✅
Milenio = (Año / 1000) + 1
|
||||
| DÉCADA | ❌ | ✅
Década = Año / 10
|
||||
| TRIMESTRE | ❌ | ✅
Cuarto = (Mes + 2) / 3
|
||||
| SEMANA | ❌ | ✅
Semana = int(math.Ceil(float64(AñoDía) / 7.0)) AñoDía devuelve el día del año especificado por la hora, en el intervalo de 1 a 365 para los años no bisiestos, y de 1 a 366 en los años bisiestos. (Véase paquete golang time)
|
||||
| MILLISEGUNDO | ❌ | ✅
Milisegundo = Nanosegundo / 106
|
||||
| AÑO_ISO | ❌ | ✅
Iso_year = año ISO 8601 para la marca de tiempo de entrada.
|
||||
| ISO_WEEK | ❌ | ✅
Iso_week = Semana ISO 8601 para la marca de tiempo de entrada. La semana suele ir del 1 al 53. Por ejemplo, del 1 al 3 de enero del año n podrían pertenecer a la semana 52 o 53 del año n-1, y del 29 al 31 de diciembre podrían pertenecer a la semana 1 del año n+1.
|
||||
| DÍA_DEL_AÑO (DOY) | ❌ | ✅
Día_del_año o doy = AñoDía Véase paquete golang time. AñoDía devuelve el día del año especificado por la hora, en el rango de 1 a 365 para años no bisiestos, y de 1 a 366 en años bisiestos.
|
||||
| DÍA_DE_LA_SEMANA (DOW) | ❌ | ✅
Día_de_la_semana o dow = Entre semana que devuelve el día de la semana de la hora indicada. Véase paquete golang time.
|
Zona horaria
Para N1QL, dentro del formato fecha-hora, ZONA_HORARIA_REGIÓN y TIMEZONE_ABBR no son compatibles. Pero pueden pasarse a la función funciones N1QL específicas de la zona horaria.
Como podemos ver más arriba a la hora de representar el TIMESTAMP dentro de las fechas N1QL, hay campos adicionales soportados. Éstos son AÑO_ISO, ISO_WEEK, DÍA_DEL_AÑO (DOY), DÍA_DE_LA_SEMANA (DOW) y HORA que es el desfase con respecto a UTC.
En ausencia de un indicador de zona horaria, se toma la zona horaria local actual de donde se encuentra el servidor Couchbase.
Profundicemos un poco más en la HORA comparaciones entre N1QL y Oracle. El sitio TIMESTAMP CON HORA y MARCA HORARIA CON ZONA HORARIA LOCAL en Oracle son variantes de los tipos de datos TIMESTAMP tipo de datos. El primero incluye la información de la zona horaria, que es el desplazamiento de la zona horaria, que es la hora relativa a UTC o el nombre de la región de la zona horaria en su valor, y el segundo incluye la zona horaria de la sesión actual. MARCA HORARIA CON ZONA HORARIA LOCAL no almacena la información de la zona horaria internamente, pero puede ver la información de la zona horaria local en la salida de SQL si la opción TZH:TZM o TZR TZD se especifican elementos de formato. Consulte la documentación de Oracle: Tipos de datos Datetime y compatibilidad con zonas horarias para más detalles.
Oráculo: TIMESTAMP '2017-01-31 03:49:30.612 -08:00'
Couchbase: “2017-01-31T03:49:30.612-08:00”
Para Oracle, si dos fechas que se comparan representan el mismo valor en UTC, entonces son iguales:
TIMESTAMP '2017-01-15 8:00:00 -8:00' == TIMESTAMP '2017-01-15 10:00:00 -6:00'
En N1QL, para comparar valores de fecha completos necesitamos convertirlos a milisegundos.
STR_TO_MILLIS(“2017-01-31T05:49:30.612-06:00”) == STR_TO_MILLIS(“2017-01-31T03:49:30.612-08:00”)
Valor: 1485863370612
En Oracle, podemos sustituir este desplazamiento por el valor Zona horaria Región (TZR) y la abreviatura. La abreviatura (TZD) se utiliza en caso de que el valor de la región sea ambiguo (cuando EE.UU. cambia al horario de verano).
Sin embargo, en N1QL el componente de zona horaria de la fecha siempre se representa como un desplazamiento UTC.
Por ejemplo:
TIMESTAMP '2017-01-15 8:00:00 -8:00'
también puede ser:
TIMESTAMP '2017-01-15 8:00:00 US/Pacific PDT'
Oracle también admite tipos de datos de intervalo INTERVALO AÑO A MES y INTERVALO DÍA A SEGUNDO. Estos almacenan duraciones de tiempo. El primero almacena la duración mediante los campos año y mes y el segundo mediante los campos días, horas, minutos y segundos. Con N1QL, el cálculo de un intervalo se facilita utilizando funciones fecha-hora y el componente "part".
Existen múltiples formas de insertar datos de fecha u hora en Oracle. El usuario puede insertar una cadena formateada basada en el valor de formato NLS, o un literal con conversión explícita utilizando la función FECHA_TO / TO_TIMESTAMP o TO_TIMESTAMP_TZ o conversión implícita.
En N1QL, todas las fechas se añaden a un documento como una cadena en el formato especificado anteriormente, o como un número que representa una marca de tiempo Unix en milisegundos. Esto hace que el manejo de fechas sea muy fácil y flexible, ya que las funciones realizan toda la aritmética necesaria para el usuario. Un inconveniente, sin embargo, es que la fecha tiene que coincidir exactamente con uno de los formatos de la tabla de formatos de fecha. Esto restringe al usuario a utilizar sólo un subconjunto de formatos disponibles.
Una solución para esta limitación con N1QL es utilizar las funciones de cadena y el masaje de la fecha de entrada para reflejar el formato que desee. Por ejemplo:
Convertir 2016-09-23T18:48:11.000+00:00 en "AAAA/MM/DD"
|
1 2 3 4 5 6 7 |
SELECCIONE to_string(fecha_parte_str("2016-09-23T18:48:11.000+00:00", "año" )) || "/" || to_string(fecha_parte_str("2016-09-23T18:48:11.000+00:00", "mes" )) || "/" || to_string(fecha_parte_str("2016-09-23T18:48:11.000+00:00", "día" )); "resultados": [ { "$1": "2016/9/23" } ] |
Como podemos ver, N1QL simplifica la manipulación de Fechas y Timestamps al representarlo como una cadena o un número cuando se compara con el formato de fecha a timestamp de Oracle. Pero esto significa que el usuario está restringido a utilizar sólo los formatos de fecha y hora especificados y no tiene la libertad de manipular estos formatos, lo que Oracle hace muy fácilmente usando sus Parámetros de Formato.
Seguir aprendiendo
¿Listo para probar la consulta de documentos JSON con el lenguaje SQL++ de Couchbase?
-
- Iniciar una Prueba de Couchbase Capella y verás lo fácil que puede ser.
- Véase el Migración de Oracle a Couchbase estrategias vídeo