SQLite3 Date Problem

[pat03uk]

Yes, that's what I got, but I need the date as dd/mm/yyyy

Pat

[rvk]

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]

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]

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]

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]

Same as rvk. It works fine too on my computer.

[pat03uk]

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]

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.

[BrunoK]

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]

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]

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.

[Thaddy]

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.

[silvercoder70]

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;  

[Wesbat]

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)

[cdbc]

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