SQLite3 Date Problem lazarus / 16-Databases ---------------------------------------------------------------------- pat03uk · 2024-11-18 14:06 I want to write a date to an SQLite3 database table, then retrieve it to a DBGrid column. I enter the date eg 05/12/2022 as the string 2022-12-05 into Text Field Using DBBrowser If I enter:    SELECT  strftime('%d/%m/%Y', xDate), xNumber from transactions, I get the right result ie 05/12/2022. If I run the query:    SQLQuery.SQL.Text(SELECT  strftime(''%d/%m/%Y'', xDate), xNumber from transactions)    DataSource1.DataSet := SQLQuery1;    DBGrid1.DataSource := DataSource1;    SQLQuery1.Open; The date column of the grid contains all blanks. Why should this be? Am I doing everything correctly? Pat ---------------------------------------------------------------------- Zvoni · 2024-11-18 14:30 If DD/MM/YYYY is your local format, then don't use strftime, but use Date-Function Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT  DATE(xDate) AS xDate, xNumber FROM transactions"strftime" returns a String/Text, "Date(x)" returns a Date ---------------------------------------------------------------------- pat03uk · 2024-11-18 14:55 Thanks, tried:     SELECT  DATE(xDate) AS xDate, xNumber FROM transactions this results in dates as 2022-12-05. (I want 05/12/2022) If I try:     SELECT  xDate, xNumber FROM transactions it results in the same 2022-12-05 ---------------------------------------------------------------------- paweld · 2024-11-18 15:58 You need to define what format you want the column to display directly on the DBGrid Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses   db;  var   i: Integer; begin   SQLQuery.SQL.Text := 'SELECT xDate, xNumber from transactions';   DataSource1.DataSet := SQLQuery1;   DBGrid1.DataSource := DataSource1;   SQLQuery1.Open;   for i := 0 to DBGrid1.Columns.Count - 1 do   begin     if DBGrid1.Columns[i].Field.DataType = ftDate then       DBGrid1.Columns[i].DisplayFormat := 'dd/mm/yyyy';   end;   //... ---------------------------------------------------------------------- pat03uk · 2024-11-18 16:44 Thanks, tried that, no difference. Bit puzzled, how do I set Field.DataType to ftDate. DisplayFormat is already set to dd/mm/yyyy in the Object Inspector Pat ---------------------------------------------------------------------- Handoko · 2024-11-18 17:43 I am not sure it will help or not. I just want to share how I handle date time data in SQLite. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i const  DateTimeFormat = 'dd-mm-yy hh:nn:ss'; function TimeFormatted(T: TDateTime): string;begin  Result := FormatDateTime(DateTimeFormat, T);end; The data time data will be stored as REAL data type in SQLite. And each time when showing it to user, I always call TimeFormatted. Because I consistently use DateTimeFormat, my code works. And I prefer to use TStringGrid, because I can format and show the data in whatever way I like. Note: I guess you already know, TDateTime actually is Double in Pascal. ---------------------------------------------------------------------- paweld · 2024-11-18 17:46 Actually, I forgot that “/” is a replacement for the date separator. You need to take the “/” in quotation marks. Sample in attachment. ---------------------------------------------------------------------- Thaddy · 2024-11-18 18:04 Handoko provided a better example: store it as TDatetime and convert as necessary. Because the conversion code has the correct TFormatsettings for the language. ---------------------------------------------------------------------- pat03uk · 2024-11-18 18:44 I’m storing the date as a string. My understanding of SQLite is that you can store as string, integer or real - how do you store as TDate or TDatetime? Pat ---------------------------------------------------------------------- Handoko · 2024-11-18 18:46 Didn't I already say, TDateTime actually is Double in Pascal? Storing date data as string (in SQLite) may work too, but you need to extra carefully consistently format the data correctly. Extra work, not worth in my opinion. ---------------------------------------------------------------------- rvk · 2024-11-18 18:47 Quote from: pat03uk on November 18, 2024, 07:44:35 pmI’m storing the date as a string. My understanding of SQLite is that you can store as string, integer or real - how do you store as TDate or TDatetime? Pat Yes, but how did you store it? What real value (string) holds that field? If it's not recognized as a date, it can't translate it. ---------------------------------------------------------------------- rvk · 2024-11-18 18:49 For example QuoteSQLite does not have a dedicated date/time datatype. Instead, date and time values can stored as any of the following: ISO-8601   A text string that is an ISO 8601 date/time value. Example: '2025-05-29 14:16:00' Julian day number   The number of days including fractional days since -4713-11-24 12:00:00 Example: 2460825.09444444 Unix timestamp   The number of seconds including fractional seconds since 1970-01-01 00:00:00 Example: 1748528160https://www.sqlite.org/lang_datefunc.html So if you saved it as 05/12/2024 then it's not a valid date. You need to save it as 2024-12-05 as string. ---------------------------------------------------------------------- Handoko · 2024-11-18 18:52 rvk showed the example, why I said you need to extra carefully consistently format the data correctly. ---------------------------------------------------------------------- pat03uk · 2024-11-18 18:59 I’m saving the date as a string as 2022-12-05 (see first post) Pat ---------------------------------------------------------------------- rvk · 2024-11-18 19:06 Quote from: pat03uk on November 18, 2024, 05:44:48 pmBit puzzled, how do I set Field.DataType to ftDate. DisplayFormat is already set to dd/mm/yyyy in the Object Inspector In object inspector? Did you precreated the fields? That often goes horribly wrong. It's best you let fpc create those fields automatically. Not sure if that's the problem here. Otherwise you need to create a small example which reproduces the problem. ---------------------------------------------------------------------- Handoko · 2024-11-18 19:13 Yes, we need an example. Bug can hide in anywhere in the code. Preferable a downloadable compile-able source code with sample data. ---------------------------------------------------------------------- paweld · 2024-11-18 19:45 Quote from: pat03ukI’m saving the date as a string as 2022-12-05 (see first post) in that case, use the OnGetText event - modified sample in attachment ---------------------------------------------------------------------- Sieben · 2024-11-18 21:43 That's what i would also suggest, given the fact that ISO8601 text is indeed the recommended date format in SQLite. And by converting the ISO string first into a TDateTime and then back to a string you have all the 'freedom of formatting' you'd have formatting an incoming TDateTime - which can be as straightforward as this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i const  cISODate = 'yyyy-mm-dd';  // might also be local to the procedure procedure TdmMain.qHauptDatumGetText(Sender: TField; var aText: string; DisplayText: Boolean);begin  if DisplayText and not Sender.IsNull then  begin    aText := Sender.AsString;    aText := DateToStr(StrToDate(aText,cISODate,cISODate[5]));  end;end; DateToStr of course being a function using DefaultFormatSettings. But you can use a variety of other functions and overloads to customize your formatting. ---------------------------------------------------------------------- rvk · 2024-11-19 08:36 Using GetText is a poor solution for a problem that does not exist. We don't even know anything about the table structure. How is that date field defined? What if you want to use that field to do some data calculations (like number of days since) inside a SELECT statement. For me the code below works fine in FPC so there is no problem with strftime, even if it deals with a TEXT defined field. But it might be better to just let SQLite3 determine the affinity and use DATE (in which case it will pick NUMERIC as underlying field). https://www.sqlite.org/datatype3.html Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program test; {$MODE OBJFPC}{$H+} uses  Classes, SysUtils, dateutils, DB, sqldb, sqlite3conn; var  Conn: TSQLite3Connection;  Trans: TSQLTransaction;  Query: TSQLQuery;  n: Integer;begin  DeleteFile('test.db');  Conn := TSQLite3Connection.Create(nil);  Conn.DatabaseName := 'test.db';  try     Trans := TSQLTransaction.Create(Conn);    Trans.Database := Conn;    Query := TSQLQuery.Create(Conn);    Query.Database := Conn;    Query.Transaction := Trans;     Conn.ExecuteDirect('CREATE TABLE data (id INTEGER, xdate text);');    // but xdate DATE might be better here     Query.SQL.Text := 'INSERT INTO data (id, xdate) values (:id, :xdate)';    for n := 1 to 10 do    begin      Query.Params.ParamByName('id').AsInteger := n;      Query.Params.ParamByName('xdate').AsDate := Date;      // Query.Params.ParamByName('xdate').AsString := '2023-12-05'; // this works too      Query.ExecSQL;    end;    Trans.Commit;     Query.SQL.Text := 'SELECT id, strftime(''%d/%m/%Y'', xDate) as xdate FROM DATA';    Query.Open;    while not Query.EOF do    begin      writeln(format('id = %d  xdate = %s', [Query.FieldByName('id').AsInteger, Query.FieldByName('xdate').AsString]));      Query.Next;    end;   finally    Conn.Free;  end;   readln; end. Quoteid = 1  xdate = 19/11/2024 id = 2  xdate = 19/11/2024 id = 3  xdate = 19/11/2024 id = 4  xdate = 19/11/2024 id = 5  xdate = 19/11/2024 id = 6  xdate = 19/11/2024 id = 7  xdate = 19/11/2024 id = 8  xdate = 19/11/2024 id = 9  xdate = 19/11/2024 id = 10  xdate = 19/11/2024 The problem is probably the predefined fields in the IDE object inspector (which causes the blank fields). ---------------------------------------------------------------------- Thaddy · 2024-11-19 09:59 Correct, with one extra remark: dates in a database should always be stored as UTC time so the date becomes independent of location and time zone. ---------------------------------------------------------------------- Sieben · 2024-11-19 11:36 QuoteWe don't even know anything about the table structure. How is that date field defined? We have some clues from TS' first post here: QuoteI enter the date eg 05/12/2022 as the string 2022-12-05 into Text Field which is of course a ISO8601 text representation of a date, stored to a SQLite TEXT data type field. QuoteWhat if you want to use that field to do some data calculations (like number of days since) inside a SELECT statement. SQLite is perfectly happy to perform calculations on an ISO8601 text representation of date and time values: https://www.sqlite.org/lang_datefunc.html And DateUtils provides functions that take UTC into consideration: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function DateToISO8601(const ADate: TDateTime; AInputIsUTC: Boolean = True): string;Function ISO8601ToDate(const DateString: string; ReturnUTC : Boolean = True): TDateTime;Function ISO8601ToDateDef(const DateString: string; ReturnUTC : Boolean; aDefault : TDateTime ): TDateTime; deprecated;Function ISO8601ToDateDef(const DateString: string; aDefault : TDateTime; ReturnUTC : Boolean = True ): TDateTime;Function TryISO8601ToDate(const DateString: string; out ADateTime: TDateTime; ReturnUTC : Boolean = True) : Boolean; I would even go so far as suggesting that ISO8601 will over time become a if not the standard of database date and time storage. ---------------------------------------------------------------------- pat03uk · 2024-11-19 11:47 I have a minimal project that shows the problem I need to have the dbgrid show the date as dd/mm/yyyy The zipped file is 4.9MB which appears to be too big to attach Any way around this? ---------------------------------------------------------------------- rvk · 2024-11-19 12:01 Quote from: pat03uk on November 19, 2024, 12:47:04 pmThe zipped file is 4.9MB which appears to be too big to attach Any way around this? Remove any .exe and .res from the zip. Only .pas .pp .rc .lfm .lpi .lpr .lps files remain. Should be very small then. ---------------------------------------------------------------------- TRon · 2024-11-19 12:21 Or Lazarus IDE, project, publish project. Tick the compress checkbox and set the directory. Then upload the created archive. ---------------------------------------------------------------------- rvk · 2024-11-19 12:44 Quote from: TRon on November 19, 2024, 01:21:17 pmOr Lazarus IDE, project, publish project. Tick the compress checkbox and set the directory. Then upload the created archive. That will also include the .res which sometimes can be large. Never understood why it was standard included there. .res should be automatically generated when compiling, normally. ---------------------------------------------------------------------- TRon · 2024-11-19 12:59 You got a point there rvk. I always try to remember to remove any resources when uploading (but in practice 9 out of 10 times I forget as well). ---------------------------------------------------------------------- pat03uk · 2024-11-19 13:47 The published project. Pat ---------------------------------------------------------------------- Handoko · 2024-11-19 13:51 Please see my previous post: Quote from: Handoko on November 18, 2024, 08:13:04 pmYes, we need an example. Bug can hide in anywhere in the code. Preferable a downloadable compile-able source code with sample data. ---------------------------------------------------------------------- pat03uk · 2024-11-19 13:54 Sorry the database ---------------------------------------------------------------------- Handoko · 2024-11-19 14:41 Because I used Linux, I needed to modify the path to the data location. Then, it seemed to work without problem: ---------------------------------------------------------------------- pat03uk · 2024-11-19 15:17 Yes, that's what I got, but I need the date as dd/mm/yyyy Pat ---------------------------------------------------------------------- rvk · 2024-11-19 15:27 Quote from: pat03uk on November 19, 2024, 04:17:09 pmYes, that's what I got, but I need the date as dd/mm/yyyy So... Do this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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 · 2024-11-19 15:42 Update the SQLite create table statement to : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "transactions" (        "xId"   INTEGER NOT NULL,        "xdate" Date,        "xnumber"       TEXT,        PRIMARY KEY("xId" AUTOINCREMENT))Note the Date specifier for xDate. Then : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);var  i: integer;begin  SQLite3Connection1.Connected := True;  SQLTransaction1.Database := SQLite3Connection1;  SQLQuery1.Transaction := SQLTransaction1;  SQLTransaction1.Active := True;  SQLQuery1.SQL.Text := 'SELECT xDate, xNumber FROM Transactions';  DataSource1.DataSet := SQLQuery1;  DBGrid1.DataSource := DataSource1;  SQLQuery1.Open;end; Remove display Format from xDate column on the DBGrid. Should display the column with ShortDateFormat. ---------------------------------------------------------------------- pat03uk · 2024-11-19 16:22 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 · 2024-11-19 16:26 Quote from: pat03uk on November 19, 2024, 05:22:00 pmSQLQuery1.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 · 2024-11-19 16:44 Same as rvk. It works fine too on my computer. ---------------------------------------------------------------------- pat03uk · 2024-11-19 16:56 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 · 2024-11-19 17:13 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 · 2024-11-19 17:14 Quote from: pat03uk on November 19, 2024, 05:56:22 pmWhy 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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 · 2024-11-19 17:22 Quote from: pat03uk on November 19, 2024, 05:56:22 pmWhy 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 · 2024-11-19 17:47 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 · 2024-11-19 17:51 Quote from: rvk on November 19, 2024, 06:22:49 pmUsing 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 · 2024-11-30 12:06 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.DisplayDateTimeValue(Sender: TField;  var aText: string; DisplayText: Boolean);begin  if Sender.IsNull then    aText := ''  else    aText := FormatDateTime('MM/DD/YYYY', Sender.AsDateTime);end; procedure TForm1.UpdateGrid;begin  SQLQuery1.SQL.Text := 'select * from "data"';  SQLQuery1.Open;  SQLQuery1.FieldByName('Current_Time').OnGetText := @DisplayDateTimeValue;end; ---------------------------------------------------------------------- Wesbat · 2024-12-06 04:48 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i DROP TABLE IF EXISTS datatypes; CREATE TABLE "datatypes" (        "a"     INTEGER,        "b"     TEXT,        "c"     DATE); INSERT INTO datatypes VALUES (1, 'hello', '2024-12-31');INSERT INTO datatypes VALUES ('hello', 1, '31/12/2024'); SELECT * FROM datatypes  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery.Params.ParamByName('created').AsInteger := DateTimeToUnix(Now);...Result := UnixToDateTime(SQLQuery.FieldByName('completed').AsInteger) ---------------------------------------------------------------------- cdbc · 2024-12-06 06:58 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 ---------------------------------------------------------------------- Thaddy · 2024-12-06 08:05 The way I handle dates in any database is: - I always use simply TDateTime, - So I can store as a double. (TDateTime is a double) - I always store as UTC - And convert to local time for display only. - Using FormatSettings The reason is that this way the database table is always pure and works in any time zone. SQLite3 can store doubles. The above is actually what you would learn in any SQL database course regarding date and time. That way I always have the correct local time in Amsterdam, London and Vilnius - where I happen to be quite often - or whatever time zone with the same code. That is what UTC is meant for. The above is the same if you prefer to store unix timestamps. Store them as UTC. ---------------------------------------------------------------------- Zvoni · 2024-12-06 10:43 My Opinion... Quote from: Thaddy on December 06, 2024, 09:05:58 amThe way I handle dates in any database is: - I always use simply TDateTime,  --> Agreed. If it's available, use it - So I can store as a double. (TDateTime is a double) --> If no DateTime-type is available, i store it as Text in extended ISO-Format - I always store as UTC  --> Disagree. To store as UTC only makes sense, if there are multiple Clients connecting from different timezones. If it's a local Database (SQLite!), it's local. Period! i use localtime, so the Data inside the DB is in sync with the Computer it resides on. - And convert to local time for display only.  --> only if stored as UTC - Using FormatSettings --> that goes without saying ---------------------------------------------------------------------- Thaddy · 2024-12-06 10:53 Even for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers) ---------------------------------------------------------------------- Zvoni · 2024-12-06 10:54 Quote from: Thaddy on December 06, 2024, 11:53:07 amEven for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers) Since when is SQLite NOT a local Database? ---------------------------------------------------------------------- egsuh · 2024-12-10 05:29 I think poweld's solution is right. but, Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i       SQLQuery.SQL.Text := 'SELECT xDate, xNumber from transactions';      DataSource1.DataSet := SQLQuery1;      DBGrid1.DataSource := DataSource1;      SQLQuery1.Open;      for i := 0 to DBGrid1.Columns.Count - 1 do      begin        if DBGrid1.Columns[i].Field.DataType = ftDate then          DBGrid1.Columns[i].DisplayFormat := 'dd/mm/yyyy';      end;      //... Only that ftDateTime instead of ftDate         if DBGrid1.Columns[ i ].Field.DataType = ftDateTime then might work, depending on SQLite's definition of the field type. ---------------------------------------------------------------------- silvercoder70 · 2024-12-10 09:08 Quote from: Zvoni on December 06, 2024, 11:54:12 amQuote from: Thaddy on December 06, 2024, 11:53:07 amEven for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers) Since when is SQLite NOT a local Database? I'm with you Zvoni I regard it as local as well. There are many ways this cat can be skinned (relatively speaking) ... (1) strftime() works, (2) setting up on DisplayText handler at the field level, or (3) setting displayFormat of column? Also I would only though store a date/datetime as a numeric of sometime that easily allows for the date functions to be used on the field in a query, and that is whether I'm working in seconds, hours, days or months. Don't have to make it harder than it needs to be. (I also have a couple of vids on (1) and (2) as well.)