[SQLite] Problème avec les dates

Version imprimable

09/12/2020, 12h09
thewolf

[SQLite] Problème avec les dates

Bonjour.

Dans un programme écrit avec Lazarus et utilisant Sqlite, j'ai un problème de gestion des dates ... (du coup, je ne sais pas si je dois mettre mon post dans ce forum ou dans celui dédié à Sqlite).

J'ai créé par code un champ Date dans une de mes tables de type ftDate

Ce champ est alimenté en utilisant un TDbDateEdit (champ DateFormat laissé vide)

Je ne sais pas comment manipuler ce champ à l'aide d'une requête SQL :

si je fais un select Date from operations, cela m'affiche par exemple : 08/12/2020
mais si je fais select date('now'), cela renvoie : 2020-12-09

Ma question est donc : comment faire pour sélectionner toutes les opérations du 8/12/2020 par exemple ?

select * from operations where date='08/12/2020' ne renvoie rien
idem pour select * from operations where date='2020-12-08'.

A noter que SELECT strftime('%Y-%m-%d', Date) FROM operations renvoie -459-11-03 pour le 8/12/20 ou -459-08-11 pour le 15/9/2020 ...

julianday(date('now')) renvoie 2459192,5 (9/12/20 vers 12h) mais juliandate(date) renvoie 44173 pour le 8/12/20

Merci d'avance pour vos réponses.

Christian
09/12/2020, 13h15
Jon Shannow

Bonjour,

Dans SQLite tout n'est que chaine. Donc, moi, je ne m'embête pas avec les dates. J'utilise des chaines. Je mets les dates au format AAAAMMJJ et je n'ai pas de souci.

JS
09/12/2020, 13h33
thewolf

Complément :

j'ai ouvert ma base de données dans le logiciel "DB Browser for SQLite" et voila ce qu'il m'indique dans l'onglet "Structure de la base de données" pour la création de la table Operations (rappel : créée par code dans Lazarus) :

Code:

CREATE TABLE Operations (id AUTOINC_INT , id_projet INTEGER , Type VARCHAR(255) , Montant FLOAT , Date DATE)

Dans l'onglet "Parcourir les données", je constate que la date saisie comme 8/12/2020 apparaît en 44173
Il semble que cela correspond au nombre de jours écoulés depuis le 30/12/1899 ...

Exécuter le SQL :

Code:

select sum(montant) from operations WHERE type='Int' and date=44173

me renvoie bien le total de toutes les opérations effectuées le 8/12/2020
et cela fonctionne tout aussi parfaitement dans mon programme Lazarus

Donc il semblerait que je peux m'en sortir en utilisant :

Code:

select sum(montant) from operations WHERE type='Int' and date=julianday('2020-12-08')-julianday('1899-12-30')

mais tout ceci n'est pas très satisfaisant !
09/12/2020, 13h36
thewolf

Citation:

Envoyé par Jon Shannow

Bonjour,

Dans SQLite tout n'est que chaine. Donc, moi, je ne m'embête pas avec les dates. J'utilise des chaines. Je mets les dates au format AAAAMMJJ et je n'ai pas de souci.

JS

Merci pour la réponse mais est-ce qu'on ne se prive pas de l'interface avec des composants comme TDbDateEdit et de toutes les fonctions gérants des dates ?
09/12/2020, 13h51
Jon Shannow

Citation:

Envoyé par thewolf

Merci pour la réponse mais est-ce qu'on ne se prive pas de l'interface avec des composants comme TDbDateEdit et de toutes les fonctions gérants des dates ?

On ne peut pas avoir le beurre et l'argent du beurre (quant à la crémière... :oops:). Il faut quelques fonctions et procédures. Mais, ça fonctionne. Après, peut-être que je me complique inutilement la vie, mais je n'utilise SQLite et Lazarus que pour des projets perso, donc ça ne me gêne pas.

JS
10/12/2020, 01h01
jurassic pork

hello,

Citation:

Envoyé par thewolf

Donc il semblerait que je peux m'en sortir en utilisant :

Code:

select sum(montant) from operations WHERE type='Int' and date=julianday('2020-12-08')-julianday('1899-12-30')

mais tout ceci n'est pas très satisfaisant !

il y a quand même plus simple :

Code:

select sum(montant) from operations WHERE type='Int' and Date=date('2020-12-08')

voir ici

Ami calmant, J.P
10/12/2020, 10h12
thewolf

Bonjour.

Plus simple mais cela ne fonctionne pas !

Cette instruction ne renvoie aucun enregistrement. Pas étonnant car :

Code:

select date('2020-12-08') renvoie '2020-12-08'

en fait, il faudrait une instruction qui renvoie 44173 ...

hello,
ton nom de champ date est dangereux car c'est un mot réservé : essaie de changer de nom de champ.

avec cette table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE employees(employee_id integer(3) primary key not null,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date date,
job_id varchar(10) not null,
salary decimal,
commission_pct number,
manager_id integer(3),
department_id integer(3), Avg_Salary NUMERIC);
INSERT INTO employees VALUES(100,'Steven','King','SKING','515.123.4567','1987-06-17','AD_PRES',24000,0,0,90,NULL);
INSERT INTO employees VALUES(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','1987-06-18','AD_VP',17000,0,100,90,NULL);
INSERT INTO employees VALUES(102,'Lex','De Haan','LDEHAAN','515.123.4569','1987-06-19','AD_VP',17000,0,100,90,NULL);
INSERT INTO employees VALUES(103,'Alexander','Hunold','AHUNOLD','590.423.4567','1987-06-20','IT_PROG',9000,0,102,60,NULL);
INSERT INTO employees VALUES(104,'Bruce','Ernst','BERNST','590.423.4568','1987-06-21','IT_PROG',6000,0,103,60,NULL);
INSERT INTO employees VALUES(105,'David','Austin','DAUSTIN','590.423.4569','1987-06-22','IT_PROG',4800,0,103,60,NULL);
INSERT INTO employees VALUES(106,'Valli','Pataballa','VPATABAL','590.423.4560','1987-06-23','IT_PROG',4800,0,103,60,NULL);
INSERT INTO employees VALUES(107,'Diana','Lorentz','DLORENTZ','590.423.5567','1987-06-24','IT_PROG',4200,0,103,60,NULL);
INSERT INTO employees VALUES(108,'Nancy','Greenberg','NGREENBE','515.124.4569','1987-06-25','FI_MGR',12000,0,101,100,NULL);
INSERT INTO employees VALUES(109,'Daniel','Faviet','DFAVIET','515.124.4169','1987-06-26','FI_ACCOUNT',9000,0,108,100,NULL);
INSERT INTO employees VALUES(110,'John','Chen','JCHEN','515.124.4269','1987-06-27','FI_ACCOUNT',8200,0,108,100,NULL);

et cette requête

Code:

select * from employees where hire_date=date('1987-06-22')

j'obtiens bien l'enregistrement de David Austin comme résultat.

Ami calmant, J.P

10/12/2020, 12h48
thewolf
Bonjour.

Bien noté pour le fait d'éviter le nom 'Date' pour un champ ... j'y ai pensé quand j'ai voulu utiliser la fonction DATE fournie par sqlite, craignant que cela bloque mais non, cela fonctionne bien. J'essaierai quand même de modifier le nom du champ.

Je pense que le problème vient d'ailleurs : j'alimente ma table non par SQL (insert comme dans ton exemple) mais par code dans Lazarus. Dans ma fiche, j'ai un TDBNavigator et différents contrôles associés aux différents champs : cela me permet de naviguer dans la table, modifier, supprimer et surtout ajouter des enregistrements (en cliquant sur le + du TDBNavigator).

Or, le champ date est relié à un TDbDateEdit comme indiqué précédemment, ce qui doit influencer la façon dont la date est encodée dans la table. Si j'affiche le contenu de la table dans un TDbGrid, elle s'affiche sous la forme '08/12/2020'.

En revanche, dans le logiciel "DB Browser for SQLite", la même date s'affiche 44173 qui semble être le nombre de jours écoulés depuis le 30/12/1899.

Si, dans le même logiciel, je fais :
Code:

1 2 insert into operations values(400,12,'Int',1234,'2020-12-10') j'obtiens bien 2020-12-10
à noter que j'ai le même résultat avec :

Code:

insert into operations values(402,12,'Int',1234,date('now'))

et, avec :
Code:

1 2 insert into operations values(400,12,'Int',1234,'10-12-2020') j'obtiens 10-12-2020
Il est donc normal que ton exemple fonctionne : tu insères (command sql insert) '1987-06-22' et tu fais un select sur la même valeur ... mais cela ne règle pas mon problème qui est de pouvoir interroger la table par SQL alors qu'elle a été alimentée comme indiqué ci-dessus.

Comment la valeur du champ date est-elle codée "en dur" dans le fichier ? Mon programme lit bien ce champ, soit automatiquement dans un TDBGrid soit par code avec une instruction du genre :

Code:

DateOp:= FieldbyName('date').AsString;

Christian
11/12/2020, 07h16
jurassic pork

2 pièce(s) jointe(s)

hello,
il y a un truc louche dans ta base.
1 - Effectivement si on saisit la date dans un TBEditDate (j'ai rajouté ce contrôle dans mon programme de test) la date est stockée en nombre dans sqlite3.
En voici la preuve sachant que j'ai modifié les deux premiers enregistrements par le contrôle TDBEditDate :
Pièce jointe 586382 Pièce jointe 586383

Par contre si je fais :

Code:

select * from employees WHERE hire_date=julianday('1987-05-17')

cela fonctionne, j'ai bien comme résultat le premier enregistrement.

Ce qui me paraît louche c'est qu'on a pas l'air d'avoir le même genre de valeur numérique pour la date dans la base de données.

Ami calmant, J.P

Fonction de conversion TDateTime <-> MySqlDate

A toutes fins utiles

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
function DateSqlToTDateTime(const S: string): TDateTime;
var
  AAAA, MM, JJ: word;
  HH, MN, SS, MS: word;
begin
  //1234567890123456789
  //2016-08-15 01:02:03
  AAAA := StrToIntDef(Copy(S, 1, 4), 2000);
  MM   := StrToIntDef(Copy(S, 6, 2), 01);
  JJ   := StrToIntDef(Copy(S, 9, 2), 01);
  HH   := StrToIntDef(Copy(S, 12, 2), 00);
  MN   := StrToIntDef(Copy(S, 15, 2), 00);
  SS   := StrToIntDef(Copy(S, 18, 2), 00);
  MS   := 0;
  Result := EncodeDateTime(AAAA, MM, JJ, HH, MN, SS, MS);
end;
 
// encodage d'une date en format SQL
function DateTimeToDateSql(const QD: TDateTime): string;
const
  SQL_DATE_HEURE_FORMAT = '%.4d-%.2d-%.2d %.2d:%.2d:%.2d';
var
  QAnnee, QMois, QJour, QHeure, QMinute, QSeconde, QMilliseconde: word;
begin
  DecodeDate(QD, QAnnee, QMois, QJour);
  DecodeTime(QD, QHeure, QMinute, QSeconde, QMilliseconde);
  Result := Format(SQL_DATE_HEURE_FORMAT,
                   [QAnnee, QMois, QJour,
                    QHeure, QMinute, QSeconde
                   ]);
end;

Bonjour.

Merci JP CASSOU pour les fonctions que je regarderai plus tard.

Pour mon 'ami calmant', la différence c'est effectivement que dans DB Browser, la valeur qui apparaît pour la date est son "julianday" et pas pour moi !

Pour m'ôter un doute et voir si ma base est "louche" (!), j'ai refait un programme très limité à partir de mon programme global (plus complexe car plusieurs tables reliées entre elles ..). En mettant DateOp comme nom du champ date.

Voilà ce qu'il y a dans la fiche :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 TForm1 = class(TForm) DBDateEdit1: TDBDateEdit; DBEdit1: TDBEdit; OpDatasource: TDataSource; DBNavigator1: TDBNavigator; OperationsDs: TSqlite3Dataset; procedure FormCreate(Sender: TObject); private public end;
et voilà comment je crée ma base :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 procedure TForm1.FormCreate(Sender: TObject); begin with OperationsDs do begin FileName:= 'testdate.db'; TableName:= 'Operations'; if not TableExists then // Table operations begin FieldDefs.Clear; FieldDefs.Add('id', ftAutoInc); FieldDefs.Add('Montant', ftFloat); FieldDefs.Add('DateOp', ftDate); CreateTable; end; Open; end; end;
La table est bien créée, DB Browser indique pour l'instruction Create :
Code:

1 2 CREATE TABLE Operations (id AUTOINC_INT , Montant FLOAT , DateOp DATE)
J'ai juste relié les contrôles à la base et mis SaveOnClose à True.

J'ai saisi deux opérations en cliquant sur le + du DbNavigator, saisissant des données dans les 2 contrôles et validant.

Je retrouve bien mes deux opérations dans le DB Browser qui affiche 44176 pour aujourd'hui 11/12/2020 et 44177 pour demain ...

Donc toujours le même comportement. Pourquoi c'est différent pour toi, cela m'échappe ... peut-être la façon dont tu as créé ta table ?

Christian

Avec :

Code:

1
2
3
4
 
  maDate:= DateEdit1.Date;
  origine:= StrToDate('30/12/1899');
  IntToStr(daysbetween(madate,origine)); donne bien 44176 pour aujourd'hui 11/12/2020

11/12/2020, 19h20
thewolf

Vu sur Wikipédia :

L'epoch (de l'anglais époque ou ère) représente la date initiale à partir de laquelle est mesuré le temps par les systèmes d'exploitation. Certains logiciels utilisent une epoch différente de leur système d'exploitation, ce qui peut conduire à des bogues.

D'après le même article, différentes dates sont utilisées comme "origine" dont le 30/12/1899 pour :
Microsoft COM DATE, Object Pascal, LibreOffice Calc, Google Sheets15
11/12/2020, 23h47
jurassic pork

hello,

Citation:

Envoyé par thewolf

julianday(date('now')) renvoie 2459192,5 (9/12/20 vers 12h) mais juliandate(date) renvoie 44173 pour le 8/12/20

moi j'ai donc des julianday et toi des juliandate.

J'utilise Lazarus 2.0.10 fpc 3.2 sous windows 10 avec sqlite3 3.23.1.0. Composants Base de données utilisés : TSQLite3Connection, TSQLTransaction, TSQLQuery, TDataSource, TDBNavigator, TDBDateEdit, TDBGrid.
DB Browser for Sqlite 3.12

et toi ?

Ami calmant, J.P
13/12/2020, 10h41
thewolf

Bonjour.

Citation:

Envoyé par jurassic pork

hello,

moi j'ai donc des julianday et toi des juliandate.

faute de frappe, lire julianday, fonction proposée par sqlite :

Code:

The julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).

Et, dans DB Browser, on n'utilise aucune fonction, cela s'affiche automatiquement.

J'utilise Lazarus 2.0.8 fpc 3.0.4 sous windows 10 avec sqlite3 3.19.2. Composants Base de données utilisés : TSqlite3Dataset, TDataSource, TDBNavigator, TDBDateEdit, TDBEdit, TDBGrid.
DB Browser for Sqlite 3.12.0

Christian
13/12/2020, 15h47
jurassic pork

2 pièce(s) jointe(s)

hello,
je pense que c'est ton composant TSqlite3Dataset qui a des soucis avec les dates :
Voici ce que j'obtiens en remplaçant mes composants TSQLite3Connection, TSQLTransaction, TSQLQuery par le composant TSqlite3Dataset avec la même base sqlite3 montrée précédemment :

Pièce jointe 586513

[EDIT] Avec TSQLite3Connection, TSQLTransaction, TSQLQuery on a bien toutes les formes de stockage de la date dans sqlite3 qui sont gérées :

Pièce jointe 586514

ami calmant, J.P
13/12/2020, 17h16
thewolf

oui, c'est effectivement bizarre ... j'utilise toujours TSqlite3Dataset pour mes développements, cela me paraît plus simple que la "trilogie".

Comment as-tu créé la base sqlite3 que tu utilises pour tes tests ?

En fait, je ne pense pas que TSqlite3Dataset ait un problème particulier avec les dates. Il semble qu'en "collaboration" avec TDBDateEdit, il stocke les dates comme le nombre de jours écoulés depuis le 30/12/1899, ce qui n'a rien de spécialement "anormal" vu que plusieurs logiciels dont Object Pascal utilisent le 30/12/1899 comme origine des dates.

En revanche, le couple "création de la table/mode de saisie" que tu as utilisé semble stocker les dates grâce à leur "julianday".

Et quand on mélange les 2 méthodes, pas étonnant que cela crée une incohérence. Dans l'exemple que tu donnes, ta date du 17/05/1987 a été stockée sous son julianday : 2446932,5

Et quand tu cherches à l'afficher dans un TDbGrid avec TSqlite3Dataset, il l'interprète comme le nombre de jours écoulés depuis le 30/12/1899 d'où l'année 8599 !
ordre de grandeur : 8599 - 1899 =6700 * 365 = 2445500

Ce qu'il faut, c'est toujours utiliser la même méthode ... Quand je relis ma base avec un programme Lazarus, la date s'affiche correctement dans un TDBDateEdit ou un TDBGrid.

Christian
13/12/2020, 17h22
jurassic pork
Citation:
SQLite3 et les dates

SQLite 3 ne stocke pas les dates comme des valeurs spéciales. Il peut les stocker comme des String, de Double or Integer -
D'après ce que je constate TSqlite3Dataset ne gère que le type date Integer.

J'ai créé ma base avec les commandes du message #8
14/12/2020, 12h27
thewolf

Bonjour.

Citation:

J'ai créé ma base avec les commandes du message #8

je suppose donc que tu as créé ta base avec ces commandes sql à partir de l'utilitaire en ligne de commande sqlite3.exe. Où peux-tu le faire par programmation (Lazarus) à partir de l'ensemble TSQLite3Connection, TSQLTransaction, TSQLQuery ?

Avec TSqlite3Dataset, on peut le faire par programmation (comme je l'ai indiqué plus haut). On peut aussi le faire à la conception dans Lazarus en faisant un clic droit sur le composant (commande Create/Edit Table) : pas testé.

J'ai recréé ta base avec l'utilitaire en ligne de commande. Elle s'affiche bien dans un programme Lazarus sauf le champ hire_date qui est vide. Il semble a contrario qu'avec TSQLite3Connection, TSQLTransaction, TSQLQuery, tu affiches correctement la date quel que soit son format de stockage (2446872.5, 44176, 1987-06-20).

Question subsidiaire : dans un programme Lazarus, dois-tu utiliser le format "1987-06-20" dans une requête sql ? Ou "select ... where hire_date = 11/12/2020" renvoie-t-il l'enregistrement 102 (Lex de Haan) ?

Bonne journée.
Christian

hello,

Citation:

Envoyé par thewolf

je suppose donc que tu as créé ta base avec ces commandes sql à partir de l'utilitaire en ligne de commande sqlite3.exe. Où peux-tu le faire par programmation (Lazarus) à partir de l'ensemble TSQLite3Connection, TSQLTransaction, TSQLQuery ?

On peut le faire avec ExecuteDirect. Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 SQLite3Connection1.ExecuteDirect( 'CREATE TABLE DeadlyMushrooms '+LineEnding+ '-- This table created by Jurassic Pork '+LineEnding+ '-- for Free Pascal Lazarus '+LineEnding+ '-- Create date:2013-08-05 23:55:09 '+LineEnding+ '( '+LineEnding+ ' ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '+LineEnding+ ' Scientific_Name VARCHAR NOT NULL, '+LineEnding+ ' Common_Name VARCHAR, '+LineEnding+ ' `Order` VARCHAR, `Genus` VARCHAR, `Notes` TEXT, `Picture` BLOB, `Image_Link` VARCHAR)' );
Citation:

Question subsidiaire : dans un programme Lazarus, dois-tu utiliser le format "1987-06-20" dans une requête sql ? Ou "select ... where hire_date = 11/12/2020" renvoie-t-il l'enregistrement 102 (Lex de Haan) ?

Cela dépend comment a été stocké la date dans la base de données mais il faut certainement que le format soit le même pour toutes les dates présentes dans la base de données.
Avec TSQLite3Connection , il semblerait que les dates rentrées dans Lazarus soit en format double.

[EDIT] ce genre de requête :
Code:

1 2 select * from employees where strftime('%Y-%m-%d', hire_date) = date('2020-05-18')
fonctionne pour des dates stockées en double ou en chaîne mais pas en entier.

Ami calmant, J.P

16/12/2020, 20h58
ovni76

Bonjour,
J'arrive un peu comme un cheveu sur la soupe, je suis une petit utilisateur de lazarus sous linux et j'utilise les tables sqlite avec les composants natifs depuis plusieurs années et comme jon Shannow j'utilise que des champs au format chaîne, pour les affichage ou les contrôles j'utlise les conversions et pour créer les tables j'utilise sqlitebrowser. DE cette façon je n'ai pas de problème avec les champs des bases sqlite. Mais c'est mon humble avis.
A+
21/12/2020, 10h05
thewolf

Citation:

Envoyé par ovni76

Bonjour,
J'arrive un peu comme un cheveu sur la soupe, je suis une petit utilisateur de lazarus sous linux et j'utilise les tables sqlite avec les composants natifs depuis plusieurs années et comme jon Shannow j'utilise que des champs au format chaîne, pour les affichage ou les contrôles j'utlise les conversions et pour créer les tables j'utilise sqlitebrowser. DE cette façon je n'ai pas de problème avec les champs des bases sqlite. Mais c'est mon humble avis.
A+

Bonjour.
Merci pour ce témoignage.

Je suis aussi un petit utilisateur ... je développe quelques programmes pour mes besoins personnels et pour mon plaisir.
Historiquement, j'utilisais Delphi (v1 puis v2) avec le BDE et des bases Paradox ou DBase ...

Puis j'ai découvert Lazarus qui, couplé à sqlite, est un outil très performant.

Pour mes applis base de données, je me sers de TSqlite3Dataset qui est très pratique et fonctionne parfaitement. Ce fil aura permis de diagnostiquer qu'il encode les dates comme le nombre de jours écoulés depuis le 30/12/1899, pas scandaleux (!) puisque plusieurs logiciels dont Object Pascal utilisent le 30/12/1899 comme origine des dates. Du coup, tout se fait automatiquement : on récupère des variables de type TDateTime, l'interface avec un TDBGrid ou un TDBDateEdit affiche les dates au format français ...

Le seul "inconvénient" que j'ai donc découvert récemment est qu'il n'est pas possible de mettre une date en clair dans une requête sql comme "select * from operations where date='08/12/2020'" mais qu'il faut écrire "select * from operations WHERE date=44173".

Il ne semble pas exister de fonction sql retournant ce nombre de jours à partir de la date, style "numjours('08/12/2020')", bien dommage.
Comme je l'ai indiqué, on peut écrire : "julianday('2020-12-08')-julianday('1899-12-30')" mais c'est un peu lourd ...
Pour mes besoins personnels, j'ai opté pour une fonction en pascal me retournant ce nombre.

Il est peut-être possible de créer sa propre fonction en sql, sujet à creuser.

Je vais donc marquer ce sujet comme résolu malgré ce manque.

Merci à tous.

Christian

Fuseau horaire GMT +2. Il est actuellement 06h36.