As funções e os formatos de data e hora podem variar em diferentes bancos de dados. Neste artigo, compararemos as funções de data e hora baseadas em ISO do Couchbase com o formato de data do Oracle. Os formatos de data variam entre bancos de dados relacionais e NoSQL devido à natureza dos formatos JSON do NoSQL. Saiba mais sobre o assunto neste webcast sobre como evitar sacrificar seu conhecimento de SQL e, ao mesmo tempo manter a flexibilidade em suas consultas ao banco de dados NoSQL.
Tipos de data/hora Oracle
Na Oracle, o data e o formato fuso horário Os recursos incluem vários tipos de dados, inclusive: DATA, TEMPO, CARIMBO DE DATA/HORA COM FUSO HORÁRIO e CARIMBO DE DATA/HORA COM FUSO HORÁRIO LOCAL. O TEMPO é uma extensão do tipo de dados DATA tipo.
O formato de data no Oracle pode ser representado como literais ou como numérico valores. O literal precisa estar em um formato especificado. O formato para as datas e horas pode ser definido usando a função NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT e o NLS_DATE_LANGUAGE parâmetros. A tabela abaixo apresenta uma comparação detalhada com exemplos de trabalho.
Formatos de data do Couchbase
Com o Couchbase, a data e a hora são feitas de forma um pouco diferente. Todas as datas e horas devem ser armazenadas como cadeias de caracteres que seguem o Formato estendido de data e hora ISO 8601. N1QL contém DATA que podem ser usadas para extrair essas cadeias de caracteres formatadas com datas Oracle. As DATA e TEMPO para um fuso horário específico também pode ser representado como um carimbo de data/hora Unix em milissegundos.
Isso significa essencialmente que, ao contrário do Oracle, onde o formato da data e da hora de entrada pode mudar com base nos valores da variável NLS_DATE_FORMAT e NLS_TIMESTAMP_FORMATO formato das datas no Couchbase segue um conjunto rigoroso.
Por exemplo, no formato de registro de data e hora do Oracle:
'2008-DEC-25 17:30' é uma data válida, dado que o NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI'
No entanto, para representar o mesmo valor no Couchbase, o usuário precisa usar um dos formatos de função de data.
Por exemplo, 2008-12-25 17:30:00
Precisão de tempo
No Oracle, precisão_de_segundos_fracionários é opcional e especifica o número de dígitos na parte fracionária do SEGUNDO campo datetime. Pode ser um número (0-9) e o padrão é 6.
Por exemplo, no Oracle, o formato de data e hora pode ser fornecido como:
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FFF'
Com isso, podemos definir o registro de data e hora como sendo: ‘2006-01-02 15:04:05.999’
Couchbase N1QL suporta segundos fracionários semelhantes aos do Oracle. Isso é visto quando se usa o formato: “2006-01-02T15:04:05.999”. No entanto, O N1QL suporta precisão de 3 dígitos (precisão de nanossegundos) e o Oracle suporta precisão de segundos fracionários de até 9 dígitos.
Isso significa que, se especificarmos a data: “2006-01-02T15:04:05.999123456”o N1QL arredondará para 3 dígitos e retornará: “2006-01-02T15:04:05.999”.
Para N1QL,
|
1 2 3 4 5 6 |
SELECIONAR STR_TO_TZ("2006-01-02T15:04:05.999123456", 'America/Los_Angeles') como oeste; "resultados": [ { "oeste": "2006-01-02T15:04:05.999" } ] |
Para a Oracle,
|
1 2 3 4 |
SELECIONAR TO_TIMESTAMP('25-DEC-2008 01:00:00.336123456', 'dd-mon-yyyy hh:mi:ss.ff') como D de duplo; > D > 2008-12-25 01:00:00.336123456 |
Usando N1QL, se especificarmos mais de 9 dígitos, a função Date-Time retornará nulo.
|
1 2 3 4 5 6 |
SELECIONAR STR_TO_TZ("2006-01-02T15:04:05.9991234567", 'America/Los_Angeles') como oeste; "resultados": [ { "oeste": nulo } ] |
Para o formato Oracle Date-Time, se você fornecer mais de 9 dígitos para a parte de segundos fracionários, será gerado um erro: ORA-01830: a imagem do formato de data termina antes da conversão de toda a cadeia de entrada
Comparação das abordagens de data/hora do Oracle e do Couchbase
A tabela a seguir apresenta uma comparação do suporte a data e hora do Couchbase N1QL e do Oracle:
| Tipos de dados de data e hora do Oracle | Suporte ao formato de data e hora do Couchbase | ||||||
| DATA tipo de dados format é definido por:
NLS_DATE_FORMAT NLS_DATE_LANGUAGE Exemplo: O NLS_DATE_FORMAT precisa ser definido no arquivo de parâmetros de inicialização: NLS_DATE_FORMAT='YYYY-MON-DD' Data da amostra: DATA '2008-DEC-25' Para definir o NLS_DATE_LANGUAGE parâmetro que pode usar o ALTERAR SESSÃO declaração.
|
No N1QL, não há uma data/hora específica/tipo de dados de registro de data e hora. Todos eles são representados por strings JSON com formatos estendidos ISO 8601 e são manipulados usando as funções aritméticas e de manipulação de data e hora.
Veja o documentação para obter a lista completa. Formato de data permitido*: AAAA-MM-DD Exemplo:
No Couchbase/N1QL, as datas precisam ser representadas em formatos específicos com dia, mês e ano em formato numérico. Para todas as datas que não correspondem aos formatos de entrada, retornamos a data de entrada no formato padrão: AAAA-MM-DDThh:mm:ss.sTZD Use o DATE_FORMAT_STR para converter o formato de data de AAAA-MM-DD para 'YYYY-MON-DD'
O Couchbase suporta apenas Formatos de data estendidos ISO. Ele não oferece suporte a datas não numéricas em vários idiomas. |
||||||
| TEMPO tipo de dados format é definido por:
NLS_TIMESTAMP_FORMAT Você pode especificar o valor de NLS_TIMESTAMP_FORMAT definindo-o no arquivo de parâmetros de inicialização. NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF' Podemos usar o TO_TIMESTAMP função para converter a entrada data-hora para carimbo de data/hora tipo de dados.
|
As mesmas funções de date, mas com um argumento de entrada de formato de data de entrada diferente. Ele precisa ser fornecido explicitamente.
O formato da data junto com os componentes do registro de data e hora: AAAA-MM-DD hh:mm:ss Formatos baseados em tempo: hh:mm:ss O Couchbase oferece suporte à precisão de nanossegundos.
|
||||||
| Tipos de dados:
CARIMBO DE DATA/HORA COM FUSO HORÁRIO Formato definido por: NLS_TIMESTAMP_TZ_FORMAT Você pode especificar o valor de NLS_TIMESTAMP_TZ_FORMAT definindo-o no arquivo de parâmetros de inicialização: NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM' Podemos usar o TO_TIMESTAMP_TZ para converter data-hora de entrada em carimbo de data/hora com tipo de dados de fuso horário. Ela manterá o fuso horário de entrada.
|
Inclui os componentes DateTime do formato:
AAAA-MM-DDThh:mm:ssTZD Formatos baseados em carimbo de data/hora: hh:mm:ss.sTZD Com o N1QL, além dos formatos específicos, também temos funções específicas de fuso horário, por exemplo string para fuso horário STR_TO_TZ que converte a data de entrada para o fuso horário especificado. Consulte a documentação sobre as funções de manipulação de fuso horário
|
Tabela 1 - Tipos de DateTime do Oracle vs. formatos de data e fuso horário ISO do N1QL
* Tanto o Oracle quanto o N1QL determinam automaticamente se o horário de verão está em vigor em um fuso horário especificado e retornam a hora local correspondente.
** Ao lidar com os formatos de data no N1QL, é importante lembrar que cada componente da string de data e hora precisa ser representado por um numérico valor. Além disso, o componente de data da cadeia de data e hora deve ser separado por um traço "–" e os componentes de tempo precisam ser separados por dois pontos ":". Caso contrário, não é um objeto de data válido.
Recursos relacionados
A beleza do JSON é que você pode ter muitos estilos flexíveis diferentes para armazenar informações de data/hora e usar funções simples para compor ou extrair o que é necessário no momento em que você precisa. Esquemas rígidos não são necessários no Couchbase NoSQL, o que o torna mais flexível do que as soluções RDBMS legadas. Saiba mais lendo nosso whitepaper.
Componentes de data/hora do Oracle e do Couchbase
Para qualquer tipo de data/hora, tanto o Oracle quanto o N1QL armazenam informações extras em campos diferentes para a data de entrada. Isso permite que o usuário extraia informações específicas sobre a data.
Os campos de data e hora do Oracle são SÉCULO, ANO, MÊS, DIA, HORA, MINUTOS e SEGUNDO. O TEMPO Os tipos de dados representam os segundos como segundos fracionários, sendo que sua precisão é determinada pelo precisão_de_segundos_fracionários parâmetro. Ele também inclui os campos TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION e TIMEZONE_ABBR. Ele converte internamente o registro de data e hora do Oracle em valores de data. O padrão para o componente de hora é meia-noite e a data padrão para o componente de data é o primeiro dia do mês atual. A DATA armazena as informações de data e hora.
Além dos campos que a Oracle suporta para seu DATA e TEMPO tipos de dados, o N1QL também suporta MILÊNIO, DÉCADA, TRIMESTRE, SEMANA e MILISSEGUNDO. O valor desses campos é computado internamente usando aritmética básica. O N1QL não é compatível com TIMEZONE_REGION e TIMEZONE_ABBR campos.
Uma comparação detalhada de cada campo é apresentada na tabela abaixo.
Vamos considerar a seguinte linha de amostra para nossos exemplos no formato de registro de data e hora no Oracle:
|
1 2 3 4 5 |
criar tabela t1 (data_comprada carimbo de data/hora com tempo zona ); inserir em t1 valores (TEMPO '2008-12-25 01:00:00.336 PST'); > t1 data_comprada > 2008-12-25,01:00:00.336-08:00 |
Vamos considerar o documento do Couchbase correspondente em um bucket chamado balde1:
|
1 2 3 4 5 6 7 |
criar primário índice em balde1; Inserir em balde1 valores ("23", {"date_purchased" (data da compra):"2008-12-25T01:00:00.336-08:00"}); Balde1 - Documento id : 23 { "date_purchased" (data da compra):"2008-12-25T01:00:00.336-08:00" } |
O TO_CHAR('CC' ), com uma data como primeiro argumento, e EXTRATO é usada no Oracle para recuperar os valores do campo de data e hora.
Para o N1QL, há duas funções DATE_PART_STR ou DATE_PART_MILLIS dependendo do fato de a data ser representada como uma cadeia de caracteres JSON ou um milissegundo numérico. Usaremos essas funções para dar exemplos de cada componente de data e hora listado abaixo.
Resumo de todos os componentes de data e hora no Oracle e no Couchbase
| Componentes de data e hora | Oráculo | N1QL | ||||
| SÉCULO | ✅
|
✅
|
||||
| ANO | ✅
|
✅
|
||||
| MÊS | ✅
|
✅
|
||||
| DIA | ✅
|
✅
|
||||
| HORA
(comportamento diferente) |
✅
A Oracle considera o componente de fuso horário do registro de data e hora de entrada. |
✅
O diferença nos resultados é porque o N1QL não considera o componente de fuso horário do registro de data e hora de entrada. |
||||
| MINUTOS | ✅
|
✅
|
||||
| SEGUNDO | ✅
|
✅
Para parte fracionária, consulte MILLISECOND. |
||||
| TIMEZONE_HOUR | ✅
|
✅
|
||||
| TIMEZONE_MINUTE | ✅
|
✅
|
||||
| TIMEZONE_REGION | ✅
|
❌ | ||||
| TIMEZONE_ABBR | ✅
|
❌ | ||||
| TIMEZONE (deslocamento do UTC) | (Tipo de dados em si, não um campo) | ✅
O número aqui representa o fuso horário em segundos. |
||||
| MILÊNIO | ❌ | ✅
Milênio = (Ano / 1000) + 1
|
||||
| DÉCADA | ❌ | ✅
Década = Ano / 10
|
||||
| TRIMESTRE | ❌ | ✅
Trimestre = (Mês + 2) / 3
|
||||
| SEMANA | ❌ | ✅
Semana = int(math.Ceil(float64(YearDay) / 7.0)) AnoDia retorna o dia do ano especificado pela hora, no intervalo de 1 a 365 para anos não bissextos e de 1 a 366 em anos bissextos. (Consulte pacote de tempo da golang)
|
||||
| MILISSEGUNDO | ❌ | ✅
Milissegundos = nanossegundo / 106
|
||||
| ISO_YEAR | ❌ | ✅
Iso_year = ano ISO 8601 para o registro de data e hora de entrada.
|
||||
| ISO_WEEK | ❌ | ✅
Iso_week = semana ISO 8601 para o registro de data e hora de entrada. A semana geralmente varia de 1 a 53. Por exemplo, de 1º a 3 de janeiro do ano n pode pertencer à semana 52 ou 53 do ano n-1, e de 29 a 31 de dezembro pode pertencer à semana 1 do ano n+1.
|
||||
| DAY_OF_YEAR (DOY) | ❌ | ✅
Day_of_year ou doy = YearDay AnoDia retorna o dia do ano especificado pela hora, no intervalo de 1 a 365 para anos não bissextos e de 1 a 366 em anos bissextos.
|
||||
| DAY_OF_WEEK (DOW) | ❌ | ✅
Day_of_week ou dow = Dia da semana que retorna o dia da semana para o horário determinado. Veja pacote de tempo da golang.
|
Considerações sobre o fuso horário
Para N1QL, no formato de data e hora, TIMEZONE_REGION e TIMEZONE_ABBR não são compatíveis. Mas eles podem ser passados para a função funções N1QL específicas de fuso horário.
Como podemos ver acima, quando se trata de representar o TEMPO Nas datas N1QL, há suporte para campos adicionais. Esses campos são ISO_YEAR, ISO_WEEK, DAY_OF_YEAR (DOY), DAY_OF_WEEK (DOW) e FUSO HORÁRIO que é o deslocamento do UTC.
Na ausência de um indicador de fuso horário, o fuso horário local atual é considerado a partir de onde o servidor Couchbase está localizado.
Vamos nos aprofundar um pouco mais no FUSO HORÁRIO comparações entre o N1QL e o Oracle. As TEMPO COM FUSO HORÁRIO e CARIMBO DE DATA/HORA COM FUSO HORÁRIO LOCAL Os tipos de dados no Oracle são variantes do tipo TEMPO tipo de dados. O primeiro inclui as informações do fuso horário, que é o deslocamento do fuso horário, que é o horário relativo ao UTC ou ao nome da região do fuso horário em seu valor, e o último inclui o fuso horário da sessão atual. CARIMBO DE DATA/HORA COM FUSO HORÁRIO LOCAL não armazena informações de fuso horário internamente, mas você pode ver as informações de fuso horário local na saída do SQL se o parâmetro TZH:TZM ou TZR TZD são especificados. Consulte a documentação da Oracle: Suporte a tipos de dados de data e hora e fuso horário para obter mais detalhes.
Oráculo: REGISTRO DE DATA E HORA '2017-01-31 03:49:30.612 -08:00'
Couchbase: “2017-01-31T03:49:30.612-08:00”
Para o Oracle, se duas datas que estão sendo comparadas representam o mesmo valor em UTC, elas são iguais:
TIMESTAMP '2017-01-15 8:00:00 -8:00' == TIMESTAMP '2017-01-15 10:00:00 -6:00'
No N1QL, para comparar valores de data completos, precisamos convertê-los em milissegundos.
STR_TO_MILLIS(“2017-01-31T05:49:30.612-06:00”) == STR_TO_MILLIS(“2017-01-31T03:49:30.612-08:00”)
Valor: 1485863370612
No Oracle, podemos substituir esse deslocamento pelo Região do fuso horário (TZR) e a abreviação. A abreviação (TZD) é usada no caso de o valor da região ser ambíguo (quando os EUA mudam para o horário de verão).
Entretanto, no N1QL, o componente de fuso horário da data é sempre representado como um deslocamento UTC.
Por exemplo:
REGISTRO DE DATA E HORA '2017-01-15 8:00:00 -8:00'
também pode ser:
TIMESTAMP '2017-01-15 8:00:00 US/Pacific PDT'
A Oracle também oferece suporte a tipos de dados de intervalo INTERVALO DE ANO PARA MÊS e INTERVALO DE DIA PARA SEGUNDO. Esses armazenam durações de tempo. O primeiro armazena a duração usando os campos ano e mês e o segundo usando os campos dias, horas, minutos e segundos. Com o N1QL, o cálculo de um intervalo é facilitado com as funções de data e hora e o componente "part".
Há várias maneiras de inserir dados de data ou hora no Oracle. O usuário pode inserir uma string formatada com base no valor de formato NLS ou um literal com conversão explícita usando a função TO_DATE / TO_TIMESTAMP ou TO_TIMESTAMP_TZ funções ou conversão implícita.
No N1QL, todas as datas são adicionadas a um documento como uma cadeia de caracteres no formato especificado acima ou como um número que representa um carimbo de data/hora Unix em milissegundos. Isso torna o manuseio de datas muito fácil e flexível, pois as funções executam toda a aritmética necessária para o usuário. Uma desvantagem, porém, é que a data precisa corresponder exatamente a um dos formatos da tabela de formatos de data. Isso restringe o usuário a usar apenas um subconjunto de formatos disponíveis.
Uma solução alternativa para essa limitação com o N1QL é usar as funções de cadeia de caracteres e massagear a data de entrada para refletir o formato desejado. Por exemplo:
Converter 2016-09-23T18:48:11.000+00:00 em "YYYY/MM/DD"
|
1 2 3 4 5 6 7 |
SELECIONAR to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "ano" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "mês" )) || "/" || to_string(date_part_str("2016-09-23T18:48:11.000+00:00", "dia" )); "resultados": [ { "$1": "2016/9/23" } ] |
Como podemos ver, o N1QL simplifica a manipulação de datas e carimbos de data/hora, representando-os como uma cadeia de caracteres ou um número, em comparação com o formato de data e carimbo de data/hora do Oracle. Mas isso significa que o usuário está restrito a usar apenas formatos de data e hora especificados e não tem a liberdade de manipular esses formatos, o que o Oracle faz com muita facilidade usando seus parâmetros de formato.
Continue aprendendo
Pronto para experimentar a consulta de documentos JSON com a linguagem SQL++ do Couchbase?
-
- Inicie uma Avaliação do Couchbase Capella e veja como isso pode ser fácil.
- Veja o Migração de Oracle para Couchbase vídeo de estratégias