SQLite date question (Yes, I searched first...)
lazarus / 16-Databases

----------------------------------------------------------------------
Derek2020 · 2020-05-07 17:28

Hello

I can see from google that there are many questions about storing time
and dates in SQLite. I had this working before, but I don't know what
I'm doing wrong now.

I am using REAL as the datatype for the date (YYYY-MM-DD) and this is
working fine between the database and the html/php that I've written.
Using a DB viewer, I know it's storing as YYYY-MM-DD as expected.

However, when I compile in Lazarus, all I'm getting is the year. I
have tried changing various settings on the query for the datasource,
without any success. I have seen many references to using SQLite
functions and using functions converting 'Julian' (Surely
Gregorian???)  time to Unix time etc, but none show what to put and,
more importantly, where to put it.

I have looked at the data type for each field in the query (DataType:
ftFloat) and listed in the source editor as Querynam: TFLoatfield;

As I understood it, SQLite would take date entered in any one of
several formats and store it as such. But I'm missing something. I
know this question has been asked many times before and believe me,
I've read lots of replies but either they don't explain what the
problem is or they're different to this.

As ever, I'd be appreciative of any pointers, either solution or
pointing to a similar question that I might have missed.

Thanks
Derek

----------------------------------------------------------------------
egsuh · 2020-05-08 07:24

Why don't you use just TDate or TDateTime format? They are double-
precision float type, AFAIK. Any way how do you extract the date
within Pascal?

I think it should be something like:

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     var d :
TDate;    begin        d := DataSet.FieldByName('date").AsDateTime;  
end;

----------------------------------------------------------------------
thehidden · 2020-05-08 20:45

I store date and time in SQLite as string value. Conversation is done
inside my software.

----------------------------------------------------------------------
JanRoza · 2020-05-09 11:49

The other way around is much easier and that way you let SQLite take
care of the necessary conversions.
Define your date field as Date in SQLite and use
DataSet.FieldByName('date").AsString when you assign the value from
your program to the database. SQLite knows how to store it.

----------------------------------------------------------------------
Thaddy · 2020-05-09 12:08

Quote from: thehidden on May 08, 2020, 10:45:31 pmI store date and
time in SQLite as string value. Conversation is done inside my
software.
That is not a good idea if you want to support different human
languages.
Either store as TDateTime, which is a double, or store as YYYY/MM/DD
(slashes) format and best as UTC time.

The ratio is that your program can support the whole globe and the
value can easily be converted to local time.

----------------------------------------------------------------------
LacaK · 2020-05-11 06:53

If you work with SQLite database using Lazarus/FPC TSQLiteConnection
component, then best way is IMO define column as DATE or DATETIME in
SQLite table definition.
Then all underlaying operation will be handled by TSQLiteConnection,
which uses "REAL as Julian day numbers" as described in 2.2 Date and
Time Datatype https://www.sqlite.org/datatype3.html

This format should/must be supported by all compatible SQLite clients.
Also SQLite datetime functions work with data in this format
https://www.sqlite.org/lang_datefunc.html