Recent

Author Topic: SQLite3 Date Problem  (Read 2565 times)

pat03uk

  • New Member
  • *
  • Posts: 20
Re: SQLite3 Date Problem
« Reply #30 on: November 19, 2024, 04:17:09 pm »
Yes, that's what I got, but I need the date as dd/mm/yyyy

Pat

rvk

  • Hero Member
  • *****
  • Posts: 6584
Re: SQLite3 Date Problem
« Reply #31 on: November 19, 2024, 04:27:28 pm »
Yes, that's what I got, but I need the date as dd/mm/yyyy
So... Do this:

Code: Pascal  [Select][+][-]
  1.   SQLQuery1.SQL.Text := 'SELECT strftime(''%d/%m/%Y'', xDate) as xDate, xNumber FROM Transactions';

Note: You do need to add that "as xDate" after the strftime.
That's because you have predifined the xDate column in your DBGrid.
If you would remove those columns it wouldn't be needed because the columns would be added automatically.

But because you specified xDate as field you also need to name that field xDate after doing the function in the SELECT.

BrunoK

  • Hero Member
  • *****
  • Posts: 623
  • Retired programmer
Re: SQLite3 Date Problem
« Reply #32 on: November 19, 2024, 04:42:38 pm »
Update the SQLite create table statement to :
Code: Pascal  [Select][+][-]
  1. CREATE TABLE "transactions" (
  2.         "xId"   INTEGER NOT NULL,
  3.         "xdate" Date,
  4.         "xnumber"       TEXT,
  5.         PRIMARY KEY("xId" AUTOINCREMENT)
  6. )
Note the Date specifier for xDate. Then :

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   i: integer;
  4. begin
  5.   SQLite3Connection1.Connected := True;
  6.   SQLTransaction1.Database := SQLite3Connection1;
  7.   SQLQuery1.Transaction := SQLTransaction1;
  8.   SQLTransaction1.Active := True;
  9.   SQLQuery1.SQL.Text := 'SELECT xDate, xNumber FROM Transactions';
  10.   DataSource1.DataSet := SQLQuery1;
  11.   DBGrid1.DataSource := DataSource1;
  12.   SQLQuery1.Open;
  13. end;

Remove display Format from xDate column on the DBGrid.
Should display the column with ShortDateFormat.

pat03uk

  • New Member
  • *
  • Posts: 20
Re: SQLite3 Date Problem
« Reply #33 on: November 19, 2024, 05:22:00 pm »
rvk


SQLQuery1.SQL.Text := 'SELECT strftime(''%d/%m/%Y'', xDate) as xDate, xNumber FROM Transactions';

I've tried this before (and again now) it results in blank columns (both columns)

Pat

rvk

  • Hero Member
  • *****
  • Posts: 6584
Re: SQLite3 Date Problem
« Reply #34 on: November 19, 2024, 05:26:31 pm »
SQLQuery1.SQL.Text := 'SELECT strftime(''%d/%m/%Y'', xDate) as xDate, xNumber FROM Transactions';

I've tried this before (and again now) it results in blank columns (both columns)
You DID really add the "as xDate"? Because you didn't show that in your opening post.

For me it works fine.


Handoko

  • Hero Member
  • *****
  • Posts: 5376
  • My goal: build my own game engine using Lazarus
Re: SQLite3 Date Problem
« Reply #35 on: November 19, 2024, 05:44:10 pm »
Same as rvk. It works fine too on my computer.

pat03uk

  • New Member
  • *
  • Posts: 20
Re: SQLite3 Date Problem
« Reply #36 on: November 19, 2024, 05:56:22 pm »
rvk, handoko

Yes, I did add the "as xDate"

BrunoK

This has fixed it - it seems the sqlite3 date type for dates should be set to date

Why this should be when date is not an sqlite3 data type is beyond me!

Thanks to everybody for the help

Pat


Handoko

  • Hero Member
  • *****
  • Posts: 5376
  • My goal: build my own game engine using Lazarus
Re: SQLite3 Date Problem
« Reply #37 on: November 19, 2024, 06:13:58 pm »
I personally prefer to store date as REAL data type. Maybe not the best, but at least it works for me.

SQLite has many 'flaws', some database experts here advise not to use SQLite. You can search the forum if you're interested. I read their comments, I know the disadvantages of using SQLite. I still keep using SQLite because I don't think I will build any enterprise level software in near future. But it is a good advice: learn, try and use better database if you have the chance.
« Last Edit: November 19, 2024, 06:15:35 pm by Handoko »

BrunoK

  • Hero Member
  • *****
  • Posts: 623
  • Retired programmer
Re: SQLite3 Date Problem
« Reply #38 on: November 19, 2024, 06:14:21 pm »
Why this should be when date is not an sqlite3 data type is beyond me!
The magic of FPC interpreting the create statement to cast the field to ftDate ...

Apart from that, rvk was right, that
Code: Pascal  [Select][+][-]
  1.   SQLQuery1.SQL.Text := 'SELECT strftime("%d/%m/%Y", xDate) as xDate, xNumber FROM Transactions';
also does the job.

Note that using " double quote instead of 2 successive single (pascal) quotes make it easier for code maintenance.

rvk

  • Hero Member
  • *****
  • Posts: 6584
Re: SQLite3 Date Problem
« Reply #39 on: November 19, 2024, 06:22:49 pm »
Why this should be when date is not an sqlite3 data type is beyond me!
The affinity for DATE is NUMERIC (as I showed with that link).
So it actually uses the NUMERIC field for DATE fields.

Using TEXT fields also work (as we showed, strange it doesn't for you) but DATE (automatically translated to NUMERIC) is internally better for SQLite3.

Sieben

  • Sr. Member
  • ****
  • Posts: 365
Re: SQLite3 Date Problem
« Reply #40 on: November 19, 2024, 06:47:38 pm »
I'd object and set out the reasons on the previous page of this thread. Main thing of course is that it now works for TS.
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

Thaddy

  • Hero Member
  • *****
  • Posts: 16174
  • Censorship about opinions does not belong here.
Re: SQLite3 Date Problem
« Reply #41 on: November 19, 2024, 06:51:36 pm »
Using TEXT fields also work (as we showed, strange it doesn't for you) but DATE (automatically translated to NUMERIC) is internally better for SQLite3.
I forgot that in my last point: the natural representation of a date is yyyymmdd:hhmmss and that fits a TDatetime, which also means can process DATE much faster than a string in the "wrong" format.
So the rules are:
1. store dates as TDatetime/DATE (even if it is a string in SQLite, it will sort faster)
2. store dates as UTC time, not local time
3. then you can represent your dates easily for every time zone and for every representation.
If I smell bad code it usually is bad code and that includes my own code.

silvercoder70

  • Jr. Member
  • **
  • Posts: 92
    • Tim Coates
Re: SQLite3 Date Problem
« Reply #42 on: November 30, 2024, 01:06:37 pm »
Couple of thoughts...

1. Check out the code in this link which shows how you can use date/time fields...

https://github.com/Projects-and-Stuff/Lazarus-SQLite3-Demo/blob/master/unit1.pas

2. As far as the display of a date/time field is concerned, I generally do something like this - using an onGetText event handler for the field and that ensures consistency within my displays...

Code: Pascal  [Select][+][-]
  1. procedure TForm1.DisplayDateTimeValue(Sender: TField;
  2.   var aText: string; DisplayText: Boolean);
  3. begin
  4.   if Sender.IsNull then
  5.     aText := ''
  6.   else
  7.     aText := FormatDateTime('MM/DD/YYYY', Sender.AsDateTime);
  8. end;
  9.  
  10. procedure TForm1.UpdateGrid;
  11. begin
  12.   SQLQuery1.SQL.Text := 'select * from "data"';
  13.   SQLQuery1.Open;
  14.   SQLQuery1.FieldByName('Current_Time').OnGetText := @DisplayDateTimeValue;
  15. end;  
Explore the beauty of modern Pascal programming with Delphi & Free Pascal - https://www.youtube.com/@silvercoder70

Wesbat

  • New Member
  • *
  • Posts: 20
Re: SQLite3 Date Problem
« Reply #43 on: December 06, 2024, 05:48:50 am »
Bear in mind that SQLite uses dynamic typing. In fact defining a data type is completely optional. Unlike traditional RMDBs where the datatype is coerced to match, SQLite allows you to put any value into any column, regardless.

To wit:
Code: SQL  [Select][+][-]
  1. DROP TABLE IF EXISTS datatypes;
  2.  
  3. CREATE TABLE "datatypes" (
  4.         "a"     INTEGER,
  5.         "b"     TEXT,
  6.         "c"     DATE
  7. );
  8.  
  9. INSERT INTO datatypes VALUES (1, 'hello', '2024-12-31');
  10. INSERT INTO datatypes VALUES ('hello', 1, '31/12/2024');
  11.  
  12. SELECT * FROM datatypes
  13.  

yields:
Code: [Select]
a b c
1 hello 2024-12-31
hello 1 31/12/2024

The data type in your db schema is only a hint for the developer and/or library. The Date type is in fact not even a strict SQLite type.

For this reason I prefer to store dates as Unix timestamps. A known quantitive value that can't be misinterpreted.

Code: Pascal  [Select][+][-]
  1. SQLQuery.Params.ParamByName('created').AsInteger := DateTimeToUnix(Now);
  2. ...
  3. Result := UnixToDateTime(SQLQuery.FieldByName('completed').AsInteger)
« Last Edit: December 06, 2024, 05:52:17 am by Wesbat »

cdbc

  • Hero Member
  • *****
  • Posts: 1655
    • http://www.cdbc.dk
Re: SQLite3 Date Problem
« Reply #44 on: December 06, 2024, 07:58:03 am »
Hi
I pack my datetime into a 'ptrint' ~ int64 (8 bytes) and store it in a 'LargeInt' column, in the SQLite3 table.
That works great, mind you - I never use /db-aware controls/
Regards Benny
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

 

TinyPortal © 2005-2018