Recent

Author Topic: Filtering SQLite database by date  (Read 657 times)

heebiejeebies

  • Full Member
  • ***
  • Posts: 102
Filtering SQLite database by date
« on: June 13, 2022, 03:20:09 am »
Hi all,

I need to filter an SQLite database by date.  Database currently has a date column with the date recorded in the format DD/MM/YYYY, although this is user selectable to be MM/DD/YYYY.

I set up a TDateTimePicker to act as the filter control, but it has a couple of problems.  Firstly, its date value omits the leading 20 from the year, which means the format doesn't precisely match the date column, so the SQL query fails to find any entries.  Secondly, if one user has their system set to DD/MM/YYYY and another has theirs set to MM/DD/YYYY, we'll have a similar problem if they share database files.  Any idea how to approach this?  I'm thinking of perhaps adding a hidden column that stores the Julian date, although I can't even seem to figure out how to do that.  Any ideas?

Query for my DBGrid is:

Code: Pascal  [Select][+][-]
  1. SQLQuery1.Close;
  2. SQLQuery1.SQL.Text :=('SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM');
  3. SQLQuery1.Params.ParamByName('DateParam).Value := DateToStr(DateTimePicker1.Date);
  4. SQLQuery1.Open;      
  5.  

This works fine if I change the value of the DateParam to a different source in the format of DD/MM/YYYY.
Ubuntu 18.04/Lazarus 2.0.10 FPC 3.2.0

Thausand

  • Sr. Member
  • ****
  • Posts: 291
Re: Filtering SQLite database by date
« Reply #1 on: June 13, 2022, 03:54:37 am »
documentation https://www.freepascal.org/docs-html/rtl/sysutils/datetostr.html write:
Quote
Converts a TDateTime value to a date string with a predefined format.
You not want that.

better:
- https://www.freepascal.org/docs-html/rtl/sysutils/formatdatetime.html
- https://www.freepascal.org/docs-html/rtl/sysutils/formatchars.html

Code: Pascal  [Select][+][-]
  1. program test;
  2.  
  3. uses
  4.   sysutils;
  5.  
  6. type
  7.   TDBDateType=(dbdtDDMMYYYY, dbdtDDYYYYMM);
  8. var
  9.   today:TDateTime;
  10.   value:string;
  11.   whichDBDateType:TDBDateType;
  12. begin
  13.   today:=Date;
  14.  
  15.   // how you know ? is store in DB ?
  16.   whichDBDateType:=dbdtDDMMYYYY;
  17.  
  18.   case whichDbDateType of
  19.    dbdtDDMMYYYY:value:=FormatDateTime('DD"/"MM"/"YYYY',today);
  20.    dbdtDDYYYYMM:value:=FormatDateTime('DD"/"YYYY"/"MM',today);
  21.    else value:='not know DB date type'
  22.   end;
  23.  
  24.   writeln('today = ',value);
  25. end.
  26.  

But how know DB store date type ? is bad to have different date type.

dseligo

  • Hero Member
  • *****
  • Posts: 725
Re: Filtering SQLite database by date
« Reply #2 on: June 13, 2022, 07:28:18 am »
Do it like this:

Code: Pascal  [Select][+][-]
  1. SQLQuery1.Close;
  2. SQLQuery1.SQL.Text := 'SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM';
  3. SQLQuery1.ParamByName('DATEPARAM').AsDate := DateTimePicker1.Date;
  4. SQLQuery1.Open;      
  5.  

If dates in database are stored as dates, you don't format date when searching. You provide your query with TDateTime type value.
« Last Edit: June 13, 2022, 07:33:01 am by dseligo »

af0815

  • Hero Member
  • *****
  • Posts: 1024
Re: Filtering SQLite database by date
« Reply #3 on: June 13, 2022, 08:06:31 am »
User selectable in the DB is a can of worms. The user only should select the reprentation of the Datetime in the GUI. In the DB only dates as deseigo says. In other Server DBs you can only design a precision of the datetime value. Never the internal format.
Never deal with strings if you work with dates. Only use the Datetime parameter.
 
regards
Andreas

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1089
Re: Filtering SQLite database by date
« Reply #4 on: June 13, 2022, 08:42:10 am »
Do all dates share the same timezone? Do they all omit the time?
SQLite has no date fields. You can store your dates as doubles (TDateTime), integers (Unix time) or strings (ISO). To find the day, you truncate the double, check the integer for a range (00:00:00 .. 23:59:59) or the string for a pattern (0000-00-00).

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: Filtering SQLite database by date
« Reply #5 on: June 13, 2022, 08:48:26 am »
What Frank said.
If you use "Text"-Affinity for the Database-Field, use ISO-Format.
Period!
If you want to evaluate any other "Text"-Representation of Dates it must be done by the Frontend.
Using any other Text-Representation than ISO-Format looses you any and all functionality of SQL within SQLite itself (ORDER BY for example)

EDIT: And don't use "Date" as a column-name
« Last Edit: June 13, 2022, 10:27:33 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

heebiejeebies

  • Full Member
  • ***
  • Posts: 102
Re: Filtering SQLite database by date
« Reply #6 on: June 15, 2022, 09:16:51 pm »
Thanks guys.  I just created another invisible date field in the database that stores the date in a set format, and a visible one that stores the date in the user's preferred format.  Surprisingly, even if you change the displayed date order of the TDateTimePicker, it doesn't seem to change the format of the actual date value of the picker, so it still matches.

I feel like I'm going to get growled at for something in this, but so far it works...
Ubuntu 18.04/Lazarus 2.0.10 FPC 3.2.0

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1089
Re: Filtering SQLite database by date
« Reply #7 on: June 17, 2022, 11:09:12 am »
Code: Pascal  [Select][+][-]
  1. function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;
  2. begin
  3.   if AddT then
  4.     Result := FormatDateTime('yyyy-mm-dd"T"hh:mm:ss', ADate)
  5.   else
  6.     Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);
  7. end;
  8.  
  9. function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):
  10.   TDateTime;
  11. begin
  12.   TryISO8601ToDate(DateString, Result, ReturnUTC);
  13. end;
  14.  
  15. function ISODate(const ADate: TDateTime): string;
  16. begin
  17.   Result := FormatDateTime('yyyy-mm-dd', ADate);
  18. end;
  19.  
  20. function ISODate(const DateString: string; ReturnUTC: Boolean): TDateTime;
  21. begin
  22.   Result := DateOf(ISODateTime(DateString, ReturnUTC));
  23. end;
  24.  
  25. function ISOTime(const ADate: TDateTime): string;
  26. begin
  27.   Result := FormatDateTime('hh:mm:ss', ADate);
  28. end;
  29.  
  30. function ISOTime(const DateString: string; ReturnUTC: Boolean): TDateTime;
  31. begin
  32.   Result := TimeOf(ISODateTime(DateString, ReturnUTC));
  33. end;

heebiejeebies

  • Full Member
  • ***
  • Posts: 102
Re: Filtering SQLite database by date
« Reply #8 on: June 17, 2022, 09:35:06 pm »
Thanks, that's handy! :D
Ubuntu 18.04/Lazarus 2.0.10 FPC 3.2.0

 

TinyPortal © 2005-2018