Discussion :

[thewolf]

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

[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

[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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

[thewolf]

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 ?

[Jon Shannow]

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... ). 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

[jurassic pork]

hello,

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

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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

voir ici

Ami calmant, J.P

[thewolf]

Bonjour.

Plus simple mais cela ne fonctionne pas !

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

[jurassic pork]

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

et, avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Christian

[jurassic pork]

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 :


Par contre si je fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[JP CASSOU]

A toutes fins utiles

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[thewolf]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[thewolf]

Avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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

[jurassic pork]

hello,

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

[thewolf]

Bonjour.

hello,


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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[jurassic pork]

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 :



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



ami calmant, J.P

[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

[jurassic pork]

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

[thewolf]

Bonjour.

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