SQLite Date and group by
lazarus / 16-Databases

----------------------------------------------------------------------
ron · 2008-01-20 21:47

Hello,

I'm want to run a sql query like this one:

Code: [Select]SELECT name, strftime('%m',Date1) as Month, avg(Scores)
as Scores
from tblScores
where  strftime('%Y',Date1) = 2007
group by name, strftime('%m',Date1)
order by Month asc

This isn't working with lazarus, I think because the sqlite dataset
stores the date as a string.

Does anyone know a way to use the group by in combination with date
fields created by the sqlite dataset?

Ron.

----------------------------------------------------------------------
LuizAmérico · 2008-01-20 23:26

The only way i see is to write a customized strftime function.
I'll put in my todo list

----------------------------------------------------------------------
ron · 2008-01-21 18:12

Thanks,

I'll have to think of a work-around for now.

----------------------------------------------------------------------
LuizAmérico · 2008-01-22 02:10

The good news is that i managed how to create custom functions.

The bad news is that i could not find documentation for strftime
function under sqlite.
Where i can find the specifications for this function?

----------------------------------------------------------------------
LuizAmérico · 2008-01-22 17:57

I managed to create a strftime function that implements what i think
it should be.
To use do the following:

add sqlite3 to your uses clause.
copy that function to your unit:


procedure StrFTime(context: PSqlite3_Context; argc: LongInt; argv:
PPSqlite3_Value); cdecl; 
var
  Year, Month, Day: word;
  FormatStr: String;
begin
  if (sqlite3_value_type(argv[1]) <> SQLITE_INTEGER) and
    (sqlite3_value_type(argv[1]) <> SQLITE_FLOAT) then
  begin
    sqlite3_result_null(context);
    Exit;
  end;
  FormatStr := UpperCase(sqlite3_value_text(argv[0]));
  DecodeDate(sqlite3_value_double(argv[1]), Year, Month, Day);
  if FormatStr = '%Y' then
    sqlite3_result_int(context, Year)
  else   
    if FormatStr = '%M' then
      sqlite3_result_int(context, Month)
    else
      if FormatStr = '%D' then
        sqlite3_result_int(context, Day)
      else
        sqlite3_result_null(context);
end;

After the very first Open call add the following code:


sqlite3_create_function(DataSet.SqliteHandle, 'strftime', 2,
sqlite_any, nil, @strftime, nil, nil);


Test and give me some feedback.

I'll built in in the next release

----------------------------------------------------------------------
ron · 2008-03-26 20:12

Luiz,

I'm sorry for my very late reaction,
I'm just a beginner with pascal programming and I didn't get your
function to work. I'm stuck on the message statistics.pas(133,88)
Error: Incompatible type for arg no. 6: Got "<procedure variable type
of procedure(Pointer, LongInt, PPsqlite3_value) of object;CDecl>",
expected "<procedure variable type of procedure(Pointer, LongInt,
PPsqlite3_value);CDecl>"

Ron.