SQL – Etienne ZINZINDOHOUE

Internal Date-Heure

Dans ce billet nous allons voir comment les données de type date-heure sont stockées sur les pages de données (sur le disque dur). Préparez-vous, nous allons faire une plongée dans les profondeurs de SQL SERVER

Nous allons utiliser la table ci-dessous :

CREATE TABLE date_heure (

a char(1),

dt datetime,

b char(1),

dt2 datetime2(2),

c char(1));

Discussion du choix des colonnes de cette table
—————————————————————

L’objectif est de décortiquer les données du type date et heure écrit par SQL SERVER 2008 sur les pages de données.
C’est pour des raisons de délimitation (bornage) que nous avons choisi les colonnes ‘a’, ‘b’, et ‘c’.
Ces colonnes ‘a’, ‘b’, et ‘c’ sont facilement identifiables sur la page de données et vont nous permettre de délimiter les données de type datetime et datetime2.
Gardons à l’esprit le tableau de correspondance suivant :

Caractere Hexadecimal

——– ——–

a 61

b 62

c 63

Insérons une ligne dans la table date_heure

INSERT INTO date_heure

VALUES ('a','2011-01-12 19:38:41.250','b','2011-01-12 19:38:41.250','c')

Pour analyser ce qui est écrit sur la page de données, nous allons utiliser les commandes :

DBCC IND – DBCC TRACEON – DBCC PAGE

le DBCC IND sur la table date_heure donne

DBCC IND('MaBase',date_heure,-1)

GO

Un extrait du résultat
———————–

PageFID PagePID PageType

1 9719 10
1 9718 1

montre que la PagePID = 9718 correspond à la page de donées où est stockée notre ligne de données puisque PageType = 1

Ouvrons les traces avec DBCC TRACEON(3604) et lisons la page avec la commande DBCC PAGE(‘MaBase’,1,9718,3)

DBCC TRACEON(3604)

GO

DBCC PAGE('MaBase',1,9718,3)

GO

Un extrait du Memory Dump donne
————————–

Memory Dump @0x608EC060

00000000: 10001500 6177bc43 01699e00 00627de9 †….aw¼C.iž..b}é
00000010: 6bc4330b 63050000 †††††††††††††††††††kÄ3.c…

Nous voyons (en rouge) les valeurs ‘a’ (61) , ‘b’ (62) et ‘c’ (63) stockées dans les colonnes a, b et c. ceci nous permet d’extraire les valeurs hexadécimales des colonnes dt et dt2

Colonne type valeur (hexadecimale)

——- —— ——————–

dt datetime 77bc4301699e0000

dt2 datetime2 7de96bc4330b

Pour datetime2(2) le nombre d’octets utilisés pour l’heure (time) est 3 [ voir billet précédent intitulé : DATETIME2 : What You See Is Not What Is Stored ]

Colonne type valeur (hexadecimale) date (hexadecimal) heure (hexadecimal)

——- —— ——————– —————— ——————–

dt datetime 77bc4301699e0000 699e0000 77bc4301

dt2 datetime2 7de96bc4330b c4330b 7de96b

Pour retrouver les valeurs stockées dans les colonnes dt et dt2 il faut faire une translation des valeurs hexadécimales.

celà consiste à lire deux à deux les caractères de droite à gauche. Ce qui donne :

Colonne type date (hexadecimal) translation date heure (hexadecimal) translation heure

——- —— ——————– —————— ——————– ——————–

dt datetime 699e0000 00009e69 77bc4301 0143bc77

dt2 datetime2 c4330b 0b33c4 7de96b 6be97d

Convertissons en décimal ces valeurs translatées à l’aide de la requête suivante :

SELECT CONVERT(int,0x00009e69) AS [colonne dt date(decimal)]

,CONVERT(int,0x0b33c4) AS [colonne dt2 date(decimal)]

,CONVERT(int,0x0143bc77) AS [colonne dt heure(decimal)]

,CONVERT(int,0x6be97d) AS [colonne dt2 heure(decimal)]

On obtient :

Colonne type date trans(hexadecimal) date (decimal) heure trans (hexadecimal) heure (decimal)

——- —— ——————– —————— ——————– ——————–

dt datetime 00009e69 40553 0143bc77 21216375

dt2 datetime2 0b33c4 734148 6be97d 7072125

Pour le calcul des dates il suffit de faire :

SELECT DATEADD(dd,40553,CAST('1900-01-01' AS datetime)) AS [colonne dt date ]

, DATEADD(dd,734148,CAST('0001-01-01' AS datetime2(2))) AS [colonne dt2 date ]

Résultat
————————

Colonne type date

——- —— ——————–

dt datetime 2011-01-12 00:00:00.000

dt2 datetime2 2011-01-12 00:00:00.00

Pour la partie heure les méthodes sont différentes pour les types DATETIME et DATETIME2

–> Pour DATETIME, la valeur stockée dans la partie heure est égale aux nombre de ticks (tick-clock) après minuit.

1 tick —-> 1/300 seconde
21216375 tick —-> X

X = 21216375 * 1/300 = 70721,25

70721,25 secondes = 70721 secondes + 0.25 seconde

70721 secondes correspond à quelle heure ?

DECLARE @Secondes INT

SET @Secondes = 70721

SELECT Heure = @Secondes / 3600, Minute = (@Secondes%3600) / 60, Seconde = (@Secondes%60)

Résultat
———————

Heure Minute Seconde

——- —— ——–

19 38 41

En tenant compte des 0.25 restant on peut donc écrire

Heure Minute Seconde

——- —— ——–

19 38 41.25

–> Pour DATETIME2, la valeur stockée dans la partie heure est égale au nombre de seconde.

Donc la valeur 7072125 affichée dans la colonne heure (colonne dt2) correspond au nombre de secondes.
Et puisque la précision sur cette colonne est 2 [datetime(2)] on en déduit la valeur : 70721.25 secondes

De la même manière que précédemment on retrouve l’heure 19:38:41.25 stockée dans la colonne dt2 (DATETIME2).

Que peut-on retenir ?
————————————–
On peut retenir qu’en fonction du type de données date/heure, SQL SERVER écrit différente chose sur la page de données pour une même valeur affichée (‘2011-01-12 19:38:41.250′).

What You See Is Not What Is Stored

/!\ PS : Microsoft déconseille l’utilisation de DATETIME sous SQL SERVER 2008 +
——————————————————–
Etienne ZINZINDOHOUE
——————————————————–