Oracle Database
Rendez-vous
Recherche…
Génération de dates avec le composant No Time
Toutes les DATE ont un composant temps; cependant, il est d'usage de stocker des dates qui ne nécessitent pas d'inclure des informations de temps avec les heures / minutes / secondes définies à zéro (c'est-à-dire minuit).
Utilisez un littéral ANSI DATE (en utilisant le format de date ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Convertissez-le à partir d'un littéral de chaîne à l'aide de TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Vous trouverez plus d'informations sur les modèles de format de date dans la documentation Oracle.)
ou:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Si vous convertissez des termes spécifiques à une langue, tels que les noms de mois, il est nlsparam inclure le troisième paramètre nlsparam dans la fonction TO_DATE() et de spécifier la langue à laquelle vous devez vous attendre.)
Générer des dates avec un composant de temps
Convertissez-le à partir d'un littéral de chaîne à l'aide de TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Ou utilisez un littéral TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle TIMESTAMP implicitement un TIMESTAMP dans une DATE lors de son stockage dans une colonne DATE d'une table; Cependant, vous pouvez explicitement CAST() la valeur à un DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Le format d'une date
Dans Oracle, un type de données DATE n'a pas de format; Lorsque Oracle envoie une DATE au programme client (SQL / Plus, SQL / Developer, Toad, Java, Python, etc.), il envoie 7 ou 8 octets qui représentent la date.
Une DATE qui n'est pas stockée dans une table (c.-à-d. SYSDATE par SYSDATE et ayant "type 13" lors de l'utilisation de la commande DUMP() ) a 8 octets et a la structure (les chiffres à droite sont la représentation interne de 2012-11-26 16:41:09 ):
BYTE VALUE EXAMPLE
---- ------------------------------- --------------------------------------
1 Year modulo 256 220
2 Year multiples of 256 7 (7 * 256 + 220 = 2012)
3 Month 11
4 Day 26
5 Hours 16
6 Minutes 41
7 Seconds 9
8 Unused 0
Une DATE qui est stockée dans une table ("type 12" lorsque vous utilisez la commande DUMP() ) a 7 octets et a la structure (les chiffres à droite sont la représentation interne de 2012-11-26 16:41:09 ):
BYTE VALUE EXAMPLE
---- ------------------------------- --------------------------------------
1 ( Year multiples of 100 ) + 100 120
2 ( Year modulo 100 ) + 100 112 ((120-100)*100 + (112-100) = 2012)
3 Month 11
4 Day 26
5 Hours + 1 17
6 Minutes + 1 42
7 Seconds + 1 10
Si vous voulez que la date ait un format spécifique, vous devrez le convertir en quelque chose qui a un format (une chaîne). Le client SQL peut implicitement le faire ou vous pouvez convertir explicitement la valeur en une chaîne à l' aide de TO_CHAR( date, format_model, nls_params ) .
Conversion de dates en chaîne
Utilisez TO_CHAR( date [, format_model [, nls_params]] ) :
(Remarque: si un modèle de format n'est pas fourni, le paramètre de session NLS_DATE_FORMAT sera utilisé comme modèle de format par défaut ; il peut être différent pour chaque session, il ne faut donc pas s'y fier. Il est recommandé de toujours spécifier le modèle de format.)
CREATE TABLE table_name (
date_value DATE
);
INSERT INTO table_name ( date_value ) VALUES ( DATE '2000-01-01' );
INSERT INTO table_name ( date_value ) VALUES ( TIMESTAMP '2016-07-21 08:00:00' );
INSERT INTO table_name ( date_value ) VALUES ( SYSDATE );
Alors:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Les sorties:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
Et:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Les sorties:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Définition du modèle de format de date par défaut
Lorsque Oracle convertit implicitement une DATE en chaîne ou inversement (ou lorsque TO_CHAR() ou TO_DATE() sont explicitement appelés sans modèle de format), le paramètre de session NLS_DATE_FORMAT sera utilisé comme modèle de format dans la conversion. Si le littéral ne correspond pas au modèle de format, une exception sera déclenchée.
Vous pouvez revoir ce paramètre en utilisant:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Vous pouvez définir cette valeur dans votre session en cours en utilisant:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Remarque: cela ne change pas la valeur pour les autres utilisateurs.)
Si vous comptez sur NLS_DATE_FORMAT pour fournir le masque de format dans TO_DATE() ou TO_CHAR() vous ne devriez pas être surpris lorsque vos requêtes sont interrompues si cette valeur est modifiée.
Modification du mode d'affichage des dates SQL / Plus ou SQL Developer
Lorsque SQL / Plus ou SQL Developer affichent des dates, ils effectuent une conversion implicite en chaîne à l'aide du modèle de format de date par défaut (voir l'exemple de définition du modèle de format de date par défaut).
Vous pouvez modifier l'affichage d'une date en modifiant le paramètre NLS_DATE_FORMAT .
Arithmétique des dates - Différence entre les dates en jours, heures, minutes et / ou secondes
En oracle, la différence (en jours et / ou en fractions) entre deux DATE peut être trouvée en utilisant la soustraction:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Affiche le nombre de jours entre les deux dates:
DIFFERENCE
----------
89
Et:
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
Affiche la fraction de jours entre deux dates:
DIFFERENCE
----------
1.0425
La différence en heures, minutes ou secondes peut être obtenue en multipliant ce nombre par 24 , 24*60 ou 24*60*60 respectivement.
L'exemple précédent peut être modifié pour obtenir les jours, les heures, les minutes et les secondes entre deux dates en utilisant:
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);
(Remarque: TRUNC() est utilisé plutôt que FLOOR() pour gérer correctement les différences négatives.)
Les sorties:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
L'exemple précédent peut également être résolu en convertissant la différence numérique en un intervalle à l' aide de NUMTODSINTERVAL() :
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);
Arithmétique des dates - Différence entre les dates en mois ou en années
La différence en mois entre deux dates peut être trouvée en utilisant le MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Les sorties:
DIFFERENCE
----------
12
Si la différence comprend des mois partiels, la fraction du mois sera calculée en fonction de 31 jours par mois:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Les sorties:
DIFFERENCE
----------
1.4516129
En raison de MONTHS_BETWEEN supposant 31 jours par mois lorsqu'il peut y avoir moins de jours par mois, cela peut entraîner des valeurs différentes pour les différences couvrant les limites entre les mois.
Exemple:
SELECT MONTHS_BETWEEN( DATE'2016-02-01', DATE'2016-02-01' - INTERVAL '1' DAY ) AS "JAN-FEB",
MONTHS_BETWEEN( DATE'2016-03-01', DATE'2016-03-01' - INTERVAL '1' DAY ) AS "FEB-MAR",
MONTHS_BETWEEN( DATE'2016-04-01', DATE'2016-04-01' - INTERVAL '1' DAY ) AS "MAR-APR",
MONTHS_BETWEEN( DATE'2016-05-01', DATE'2016-05-01' - INTERVAL '1' DAY ) AS "APR-MAY"
FROM DUAL;
Sortie:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
La différence en années peut être trouvée en divisant la différence de mois par 12.
Extraire l'année, le mois, le jour, l'heure, la minute ou la seconde composante d'une date
Les composants année, mois ou jour d'un type de données DATE peuvent être trouvés à l'aide de la commande EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )
SELECT EXTRACT (YEAR FROM DATE '2016-07-25') AS YEAR,
EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH,
EXTRACT (DAY FROM DATE '2016-07-25') AS DAY
FROM DUAL;
Les sorties:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Les composants temps (heure, minute ou seconde) peuvent être trouvés soit par:
- Utiliser
CAST( datevalue AS TIMESTAMP )pour convertir laDATEenTIMESTAMPpuis en utilisantEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); ou - Utiliser
TO_CHAR( datevalue, format_model )pour obtenir la valeur sous forme de chaîne.
Par exemple:
SELECT EXTRACT( HOUR FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM (
SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);
Les sorties:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Fuseaux horaires et heure avancée
Le type de données DATE ne gère pas les fuseaux horaires ni les modifications de l'heure d'été.
Non plus:
- utilisez le type de données
TIMESTAMP WITH TIME ZONE; ou - gérer les modifications de la logique de votre application.
Une DATE peut être stockée en temps universel coordonné (UTC) et convertie dans le fuseau horaire de la session en cours comme suit:
SELECT FROM_TZ(
CAST(
TO_DATE( '2016-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS TIMESTAMP
),
'UTC'
)
AT LOCAL AS time
FROM DUAL;
Si vous exécutez ALTER SESSION SET TIME_ZONE = '+01:00'; alors la sortie est:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
et ALTER SESSION SET TIME_ZONE = 'PST'; alors la sortie est:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Secondes de saut
Oracle ne gère pas les secondes intercalaires . Voir My Oracle Support note 2019397.2 et 730795.1 pour plus de détails.
Obtenir le jour de la semaine
Vous pouvez utiliser TO_CHAR( date_value, 'D' ) pour obtenir le jour de la semaine.
Cependant, cela dépend du paramètre de session NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Sorties 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Sorties 4
Pour ce faire, indépendamment des paramètres NLS , vous pouvez tronquer la date à minuit du jour actuel (pour supprimer les fractions de jours) et soustraire la date tronquée au début de la semaine iso en cours (qui commence toujours le lundi):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL